May 7, 2008 at 1:26 pm
The trigger I'm working on is a "FOR UPDATE,INSERT"
This trigger checks for any changes to a specific column. If a change is made or a new row is inserted I check for the Length and Uniquness of the data for that specific column. If the length is not correct or the data is not unique then I want to either not allow the insert or not allow the update to take place.
If an Update occurs and the data is deemed "bad" I can just update the table with the values from the "Deleted" Table.
If an Insert occurs I'm not sure how to stop that transaction. How would you do that?
I'm also unaware of how to process each because I don't know if it's an Update or Insert taking place. Of course I could break this into two triggers. Any opinions?
May 7, 2008 at 1:30 pm
AVB (5/7/2008)
If an Update occurs and the data is deemed "bad" I can just update the table with the values from the "Deleted" Table.If an Insert occurs I'm not sure how to stop that transaction. How would you do that?
For both, Rollback Transaction
I'm also unaware of how to process each because I don't know if it's an Update or Insert taking place. Of course I could break this into two triggers. Any opinions?
If a trigger is for insert, update, then the inserted table will always be populated, with the new records in the case of an insert and the new values in the case of an update.
If an insert occurs, the deleted table will be empty. For an update, the deleted table will have the old values of the rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2008 at 1:35 pm
Gila,
I was wondering about that. I almost tried it for the heck of it but I figured you'd have to supply a BEGIN TRAN somewhere which I wasn't so I didn't think it'd work. That's too easy! I've been doing it the other way in my other triggers - updating the table with the old values!!!!
Thanks!
May 7, 2008 at 2:00 pm
No need for begin tran. The trigger runs within the transaction started by the insert/update
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2008 at 4:48 pm
No need to manage transactions or update with deleted values.
Just raise an error.
Error raised inside of trigger will roll back everything, including UPDATE/INSERT itself.
_____________
Code for TallyGenerator
May 8, 2008 at 2:33 pm
Why not use an INSTEAD OF TRIGGER? That way, you can validate the data, and if it PASSES, then you actually insert the data. If it doesn't then skip the insert/update. Or if you prefer - only do the insert for the data that qualifies as "valid", and toss the rest.
Waiting for the error to happen, and then undoing is usually the wrong way (it's referred to as "coding by exception"). Catching the problem up front BEFORE the error happens might be better, no?
Just a thought.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 9, 2008 at 6:24 am
Matt,
I didn't think you could use an Instead Of Trigger like that. I thought that if I chose an "Instead Of" trigger that the action supplied by that triggger would take place instead of the update/insert. So if the data is good wouldn't I have to supply an Insert/Update statement within the trigger? or is that wrong?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply