Cascading Updates/Deletes and Triggers
-
When I used an update wizard from MSAccess some triggers were created. I am now creating more tables and setting cascading updates/deletes where appropriate. If I have set the cascading updates/deletes are the triggers ambiguous or even necessary to cascade updates.
-
I think that depends on whether you need both or just the deletes. Not sure exactly what you're asking I guess.
Andy
-
If cascading updates are set on related tables aren't triggers that perform the cascading updates duplicating effort and what is the impact to operations or performance as a result of having both? Then, does the system perform the cascading changes before or after triggers are fired?
-
Hi scking,
quote:
If cascading updates are set on related tables aren't triggers that perform the cascading updates duplicating effort and what is the impact to operations or performance as a result of having both? Then, does the system perform the cascading changes before or after triggers are fired?from my understanding it is duplicate effort. You perform two operations with the same aim. But I'm interested, too, whether the cascading changes or the trigger will be first. My best guess is, that the cascading changes comes first.
Cheers,
Frank
From BOL:
quote:
Triggers and Cascading Referential ActionsCascading referential actions fire the AFTER triggers in this sequence:
All of the cascading referential actions directly caused by the original DELETE or UPDATE are performed first.
When the original cascading referential actions have completed, the AFTER triggers on the original table are fired, regardless of whether any rows were updated.
AFTER triggers on tables in the chain of cascaded referential actions are then fired, but only if one or more rows in the table have been updated or deleted.
If any errors are generated by any of the original set of cascading referential actions, an error is raised, no AFTER triggers are fired, and the DELETE or UPDATE is rolled back.
An AFTER trigger can execute a DELETE or UPDATE statement that starts another chain of cascading referential actions. Each secondary chain of referential actions is treated independently. These secondary chains of referential actions behave like the primary chain. All of the secondary referential actions are completed before any secondary triggers are fired. Within each independent unit, there is no defined order in which the cascading referential actions are executed and the affected triggers are fired.
A table that has an INSTEAD OF trigger cannot also have a REFERENCES clause that specifies a cascading action. An AFTER trigger on a table targeted by a cascading action, however, can execute an INSERT, UPDATE, or DELETE statement on another table or view that fires an INSTEAD OF trigger defined on that object.
found at cascading referential integrity constraints -> Cascading Referential Integrity Constraints (maybe BOL SP 3 or not
)Edited by - a5xo3z1 on 08/05/2003 01:45:37 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
a5xo3z1,
Thank you very much. This is exactly what I needed to know. Based on this information I can remove any triggers that duplicate cacading update actions defined using the database diagram. I'll test this out at first opportunity.
-
In my experience also it is always a good idea to use either Cascading or Triggers for the enforcing referemtial integrity and remove orphaned records. But, be careful and make sure that you test it thoroughly. These deletes are hard to recover.
)Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply