Question on Trigger in SQL Server 7.0

[vaidyanathanpc]

I need help on a trigger than I'm writing. The situation is something like this.

I have a table 'circle'. Whenever a change is made to a column say 'cir_hr' on 'circle' table, a trigger is fired to update another table 'user_role'.
Say the column 'cir_hr' has an initial value of 2247 and I update it to 9999. The trigger is fired and the table user_role is appropriately updated. But before the trigger is fired, i want to perform some action on user_role table with the value 2247. How do I do this.
Say I have two tables 'circle' and 'user_role'.
The data in these tables is as follows"
circle
cir_id : 1
cir_hr : 2247

user_role
id : 1
hr : 2247

Now I want the trigger to function such that when I update the cir_hr in circle table to 9999, in the user_role table, the row with hr having value 2247 should be deleted and a new row should be inserted with hr having value 9999.

Thanks
P.C. Vaidyanathan

 

[rajeevnandanmishra]

circle
cir_id : 1
cir_hr : 2247

user_role
id : 1
hr : 2247

In the given scenario, the following trigger may help:

/* Trigger updcircle on circle table */
create trigger updcircle on circle as
delete user_role
from deleted where user_role.hr=deleted.cir_hr
insert user_role
select cir_id, cir_hr from inserted
go
/* End Trigger defination */

Let us know if you need further help.