Intercept Update and Change to Insert while Maintaing Audit Triggers
-
Would like to intercept a case when an update of an existing record is being proposed that is NOT a valid business scenario, disregard the update and change to an insert.
AddressTable contains AddressNumber and DateEffective fields which get populated whenever a user changes a customer's address. Unfortunately, in the application they may update a record with a previous DateEffective date rather than inserting a new record with a new DateEffective date. In other words they can overwrite an existing record rather than being forced to create a new record.
Additionally, I am writing changes to addresses to an Audit table via UPDATE and INSERT triggers which intercept previous and proposed new address information.
I need a solution which can accomodate both the intercepting of the incorrect UPDATE (and resubmitting as an INSERT) as well as processing the Audit triggers after the corrected INSERT statement is generated in place of the original UPDATE statement (don't log the UPDATE but log the INSERT)
1. Change Invalid UPDATES to INSERTS via a trigger
2. Run all Audit UPDATE and INSERT triggers.
I looked into INSTEAD OF UPDATE triggers but determined that they happen after any other triggers (First, Normal/Unspecified, Last).
What kinds of triggers can be coded to solve this puzzle (e.g. First, Normal/Unspecified, Last, Nested)?
THANKS for any suggestions!!!
-
An INSTEAD OF trigger should work okay for this. I'm not sure where you got the impression that they happen after AFTER triggers, as that's not correct...
--Jonathan -
SQL Server Books Online Topic "Specifying First and Last Triggers" indicates...
"INSTEAD OF triggers cannot be specified as first or last triggers. INSTEAD OF triggers are fired before updates are made to the underlying tables. However, if updates are made by an INSTEAD OF trigger to underlying tables, the updates occur after triggers defined on the table, including the first trigger. For example, if an INSTEAD OF trigger on a view updates a base table and the base table contains three triggers, the three triggers in the table fire before the data is inserted by the INSTEAD OF trigger."
If my understanding of this information is correct then in my scenario the Audit UPDATE and INSERT triggers would occur before the INSTEAD OF UPDATE trigger.
This is NOT what is desired as I want to change the UPDATE to an INSERT prior to the Audit UPDATE and INSERT triggers performing their actions.
Please advise...
-
That text is referring to tables other than the one with the INSTEAD OF trigger (what they're calling "underlying" and the "base" table, respectively). They're trying to say that an INSTEAD OF trigger on a table does not automagically invoke INSTEAD OF behavior with any other tables being affected by the INSTEAD OF trigger. Not sure if I put it any better.
This is an important point as INSTEAD OF triggers are often used with updateable views, where the underyling tables are all that are actually being changed.
--Jonathan -
Thank you for the clarification!
So, in concluding, will the following will work as described?
On the AddressTable
INSTEAD OF UPDATE <conditionally intercept and change business rule invalid UPDATES to INSERTS>
UPDATE <trigger to write out values before/after UPDATES - but not picking up the UPDATES intercepted and changed to INSERTS by the INSTEAD OF UPDATE>
INSERT <trigger to write out values from a previous record and the new INSERT record - picking up the INSERTS created by UPDATES intercepted and changed to INSERTS by the INSTEAD OF UPDATE>
Again THANKS!!!!
-
Yes, that's how it works.
--Jonathan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply