Tmess
asked on
Trigger
I'm having a little trouble with a trigger I'm creating. Basically I have parent and child records in my table. I want to cascade changes on all of my related records above it if one has changed. For example, if the great granchild is changed, the grandchild should change, the child should change and then the parent should change. However, I seem to only be able to change the one record up from updated record. Below is my code. Any thoughts would be appreciated?
Tmess
CREATE TRIGGER tU_Decisions ON [Decisions]
FOR UPDATE AS
if update(Hidden_flag)
begin
if exists (select hidden_flag from decisions where hidden_flag = 0)
begin
update decisions
set hidden_flag = 1
where decisions.decision_key In (Select parent_ID from inserted)
end
end
Tmess
CREATE TRIGGER tU_Decisions ON [Decisions]
FOR UPDATE AS
if update(Hidden_flag)
begin
if exists (select hidden_flag from decisions where hidden_flag = 0)
begin
update decisions
set hidden_flag = 1
where decisions.decision_key In (Select parent_ID from inserted)
end
end
Have you enabled reccursive triggers??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yep, Sankar has said it all! Setting the Recursive Triggers option to TRUE should do the trick.
Abhi.
Abhi.
ASKER
Excellent, thank you!
Tmess
Tmess