CODEWITHSUNDEEP
sql-server
Savanrof
Savanrof

Reputation: 21

Can I create a trigger triggering another trigger?

I have 2 tables, table A and table B. I have a trigger on A have input is column of table B. So what happens when I update table A: will table B also be updated?

What should I do if I want to update table B as well?

Upvotes: 1

Views: 2178

Answers (1)

D Kramer
D Kramer

Reputation: 759

Purely based on your example (if I understand it right) this sounds like a foreign key relation where you specify on UPDATE cascade.

Some examplecode:

ALTER TABLE [dbo].[Foo]  WITH CHECK ADD  CONSTRAINT [FK_FooBar] FOREIGN KEY([BarID])
REFERENCES [dbo].[Bar] ([BarID])
ON UPDATE CASCADE

Which makes an edit to BarID in the dbo.Foo table also iterate to the BarID field it references in dbo.Bar

However if we go with triggers you can't explicitly fire a trigger from within a trigger. You can however do an action that makes the trigger fire by satisfying the trigger conditions. And if the server option is set (which is default true) to allow triggers to activate other triggers, the secondary trigger will execute.

Note the option is only for AFTER triggers, which can be 'chained'/nested up to 32 times. And not INSTEAD OF triggers.

Msdn articles:

Upvotes: 2

Related Questions