Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JimJudge
Starting Member
7 Posts |
 Posted - 2003-07-23 : 18:11:32
|
| I have an AFTER INSERT on a table. My SQL references say that the trigger is processed AFTER the successful completion of the INSERT. However I am observing something different. If an unhandled error occurs in the trigger the INSERT is not completed.Can anyone shed some light on this for an SQL newbee.Thanks,Jim Judge |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-07-23 : 19:09:20
|
| Jim,The AFTER keyword was introduced in SQL 2K because they introduced INSTEAD OF triggers. The AFTER trigger was the "old" normal trigger in SQL 7. Triggers are considered part of the whole transaction which simply means that it is all or nothing. A failure in any of the triggers fired causes the whole transaction to be rolledback.DavidM"SQL-3 is an abomination.." |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-24 : 09:04:33
|
| Which is a good thing!For example, I keep history on all my tables.If an update or delete occurs, I move the whole row to an (almost) identical history tableBUT, I want to track WHO did the move.So I have a HIST_ADD_BY column, that is not null.All of my sprocs must supply this.There have been many a time that developers have forgotten this "rule", and wonder why their statements fail (when they look perfectly OK).Developer: "SQL is broken, this is impossible"DBA: "Did you update and set the UserId?"Develeoper: "No...ohhhh"Brett8-) |
|
|
|
|
|
|
AFTER INSERT Triggers