Records updated by After Update Trigger #1 do not trigger After Update Trigger #2.

Trigger #1 causes changes to Record A to run an Update statement to update Record B

Trigger #2 creates a replication type update statement to be inserted into a different table.

Expectation is that the replication table should now have 2 records (1 for Record A and 1 for Record B). In reality it has only one.

asked 07 Jun '15, 12:43

Jack's gravatar image

Jack
545333654
accept rate: 14%


From your description I believe this is the expected behavior.

If Trigger #1 is modifying / updating Record B in the same table as Record A then the recursive / nested operation will not fire.

INSTEAD OF and AFTER triggers do not support recursion when called on the base table that they are assigned to. These triggers are allowed to operate on the base table without firing themselves again. However, if these triggers modify some other table that has a trigger, that trigger will be fired.

Nested and Recursive Triggers

link

answered 08 Jun '15, 09:32

Edgar%20Sherman's gravatar image

Edgar Sherman
5.4k13185
accept rate: 25%

Thanks for the clarification.

Knowing this I did not assume the same would apply to different triggers on the same table.

Any workaround to achieve the desired results?

(08 Jun '15, 15:22) Jack

I'm not certain of the best way, but here are some thoughts.

1) Maybe use a before update trigger (in addition to the after update)? I think these will allow to call other triggers

2) Maybe create another table, in your after trigger dump the IDs of the records updated. On this other table have an instead of insert trigger that updates the "replication table"

(09 Jun '15, 10:51) Edgar Sherman
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×133
×18

Asked: 07 Jun '15, 12:43

Seen: 1,653 times

Last updated: 09 Jun '15, 10:51

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.