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 |
|
carloarango
Starting Member
15 Posts |
 Posted - 2010-04-16 : 12:22:45
|
| I created an INSERT TRIGGER that would perform some other process with the INSERTED data, affecting other tables in a different DB; when testing I noticed that if the process in the trigger fails the record that fired the trigger doesn't get inserted (Atomocity property I guess); is there a way I can bypass that? I want to insert the record no matter what, even if the process within the trigger fails.Any ideas?Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-16 : 13:00:43
|
| Nope, if the transaction that the trigger is in gets rolled back all actions performed by the Trigger itself will also be rolled back.You could probably hack a solution using OPENROWSET or some other ghastly hack, but that wouldn't be wise.What are you trying to do, in a trigger, that must be retained - even if the transaction and its data manipulations are rolled back?(As an example: we have a routine that logs Sproc execution and we specifically want the SProcs actions, which are subsequently rolled back, to remain logged [but flagged as "failed"] to help with diagnosis; we have a means of doing this, from the SProc level, but I can't think of a way of doing it from the Trigger level - short of outputting data to a CSV and importing as a batch job - which is in my classification as "a daft hack"!!) |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-04-16 : 13:24:44
|
| You could try doing the insert into the other DB inside a try catch block and ignore the fail on insert, if that is what you want, as long it doesn't leave your transaction uncommitable.Or you might do it with a CLR trigger and do the insert on a separate connection to the database. Don't ask me how to do it.Or send the data to be inserted into the other DB to a service broker queue. Don't ask me for details on how to do it.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Triggers Atomicity property