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 |
|
holistic
Starting Member
4 Posts |
 Posted - 2009-02-20 : 13:31:03
|
| Experts,A programmer is INSERTing records in a table 'A'.I have created a trigger in table 'A'. I need that if an ERROR in my trigger occurs then the INSERT in table 'A' is done but my code (the tigger code) is rolled back.Is it possible / how?!thanksEvan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-02-20 : 13:46:17
|
| Your trigger code should be very simple code. If you are anticipating errors then I suspect you may be doing stuff in your trigger that is better suited for processing outside the context of the user transaction.Be One with the OptimizerTG |
 |
|
|
holistic
Starting Member
4 Posts |
Posted - 2009-02-20 : 14:10:20
|
| errors happen! |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-02-20 : 14:56:43
|
quote:
Originally posted by holistic
errors happen!
Is that the DBA motto in your company?  Thanks, I'll try using that here next time the [error] hits the fan...There are two ways to not loose the user transaction. One is to successfully anticipate/catch the error prior to it actually being raised then issue a COMMIT transaction. However, this is not recommended: (from books online - COMMIT topic)quote:
In SQL Server 2000 and later, if a COMMIT TRANSACTION or COMMIT WORK statement is executed in a trigger and there is no corresponding explicit or implicit BEGIN TRANSACTION statement at the start of the trigger, users may see different behavior than in SQL Server version 7.0. Placing COMMIT TRANSACTION or COMMIT WORK statements in a trigger is not recommended.
The better way is to remove the code (in the trigger) from the context of the user transaction. ie: don't use the trigger to do whatever is erroring out. How that is accomplished depends on your requirements.Care to post your trigger code?Be One with the OptimizerTG |
|
|
|
holistic
Starting Member
4 Posts |
Posted - 2009-02-23 : 07:46:56
|
| no that's not our motto! the first guy who proposed seat belts on a car was accused that his car was not secure. in the end the idea was so correct that all cars must have it! so maybe MS will someday allow us same issue on triggers.my issue is about data transfer. one program is putting data, the trigger importing it.i guess my solution is to remove the trigger and then have a stored procedure running every x minutes checking for new data.... instantly thank to a trigger was cooler |
|
|
|
|
|
|
Triggers And Rollbacks