| Author |
Topic |
|
RobC1511
Starting Member
3 Posts |
 Posted - 2006-09-11 : 13:18:47
|
| Hi,I do not really like doing this. I know it goes against the grain.... but I am told that they want to commit the insert regardless of whether the sp runs ok...Some more info...The insert into the table is an isolated insert (not part of a larger transaction). Also, only 1 row is added at a time (no bulk inserts).The question I have, and I want to be sure on this is....Does the "commit transaction" in the code below apply soley to committing the Insert into the "Orders" table. Can it cause other issues ?CREATE TRIGGER testtrigger ON dbo.Orders FOR INSERT ASDECLARE @OrderID VARCHAR(20) SELECT @OrderID = OrderNumber FROM Inserted commit transaction BEGIN EXEC prProcessOrder @OrderID END |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-09-11 : 13:49:10
|
| The best way to insure that the transaction completes is to not use a trigger to launch another process. Hopefully you can find another way.However, the "COMMIT" will force the original user transaction to complete before the exec call is made. Depending on the type and severity of any errors that occur in the called SP as well as the order in which you place the COMMIT statement, you could get inconsistant output in regards to the error reporting.Be One with the OptimizerTG |
 |
|
|
cvraghu
Posting Yak Master
187 Posts |
Posted - 2006-09-11 : 18:40:56
|
| If you want only one particular insert NOT to be part of the main transaction, and have it always committed, use dynamic sql. Build a string variable with insert statement and execute it with EXEC or sp_executesql |
|
|
|
cvraghu
Posting Yak Master
187 Posts |
Posted - 2006-09-11 : 20:24:42
|
| If that does not work, try using xp_cmdshell and execute the insert. This will be out of scope of parent transaction. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-12 : 05:44:07
|
| An alternative way would be to store the just-inserted data into an @Table, perform the ROLLBACK, and then re-insert the data from the @Table.@Tables are not subjected to ROLLBACKsKristen |
|
|
|
RobC1511
Starting Member
3 Posts |
Posted - 2006-09-12 : 14:06:11
|
| Thanks Kristen,Regarding...>An alternative way would be to store the just-inserted data into an >@Table, perform the ROLLBACK, and then re-insert the data from the >@Table.>@Tables are not subjected to ROLLBACKsMaybe I am missing something..Wouldn't I be right back where I started then... in a loop ?When I go to re-insert it wouldn't it fail again ? Do you have a sample ?Rob |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-12 : 14:21:10
|
| Wouldn't it be better to just fix whatever problem is causing the initial insert to fail?CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-12 : 14:49:18
|
| "When I go to re-insert it wouldn't it fail again ?"Sure, if the first one failed! I probably mis-read the question.We have a scenario like this:SPROC1:BEGIN TRANSACTIONEXEC SPROC2SPROC2:INSERT some stuff to LOG tableProcess some dataReturnSPROC1 resumes:A bit later SPROC1 reaches a situation where it needs to rollback, but it wants to preserve the data entered by SPROC2 into the Log Table - we SELECT it into an @Table, ROLLBACK, and then re-Insert from the @Table back into the Log Table.Kristen |
|
|
|
RobC1511
Starting Member
3 Posts |
Posted - 2006-09-12 : 14:49:19
|
| Maybe you are correct... How could I monitor the following...There are 2 processes involved... so there is some fingerpointing going on... a row Insert and its subsequent trigger...Either a row of data is not being posted to the table properly due to something such as a datatype issue...OrThe row is being posted to the table correctly and the trigger is undoing the original action due to a failure in the code related to the trigger... It appears to be an random problem, we want to eliminate the trigger as being the culprit, hence the earlier discussion...Thanks,Rob |
|
|
|
|
Trigger help