Trigger question
-
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 AS
DECLARE @OrderID VARCHAR(20)
SELECT @OrderID = OrderNumber
FROM Inserted
commit transaction
BEGIN
EXEC prProcessOrder @OrderID
END
Thanks,
Rob
-
Rob - the "commit transaction" is not required - the trigger gets fired only after the row is already inserted into the table....& since "they want to commit....regardless of whether the sp runs ok"...all's well...
ps:Hopefully I've understood the question right...
**ASCII stupid question, get a stupid ANSI !!!** -
As I understand it..., without the explicit "Commit Transaction" within the trigger, the row would not get inserted if prProcessOrder fails because if any action within the trigger fails, then the original action (insert into the table) fails as well. This is by design.
Thanks,
Rob
-
The only way to keep from rolling back the record inserted by the trigger, is to do it with another connection.
SP_OA could be used to create such a connection but it's slow and, according to rumor, does have some memory leaks associated with it. Also, if you forget to close the connection, you will quickly eat up all connections available and the server dies.
One method that I've used is to make the call to insert the record using xp_CmdShell to insert the record. It effectively makes a new connection to the server and automatically closes the connection when the cmd is done. Of course, the xp_CmdShell call should be to OSQL using a trusted connection.
There sre some warnings about using xp_CmdShell so far as possible attacks on your database and you might want to Google for those things just to make sure your code doesn't fit into any of the "attackable" categories.
Also, like SP_OA, xp_CmdShell requires "SA" privs, another possible detriment.
Other than that, I can't think of a way of doing this.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff,
When I tested this it did appear to "work"... The sp that the trigger call fails, yet the original insert sticks... because of the Commit Transaction within the trigger appears too make it stick...
If you insert an OrderNumber < 6 characters and a valid date, the
transactions sticks, even if the sp fails... sample below...
insert into Orders select '1111','1/1/2006'
Thanks,
Rob
CREATE TABLE [dbo].[Orders] (
[OrderNumber] [char] (6) COLLATE Latin1_General_BIN NOT NULL ,
[Shipped] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrdersLog] (
[OrderNumber] [char] (5) COLLATE Latin1_General_BIN NULL ,
[Shipped] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrdersRaw] (
[Ord] [char] (6) COLLATE Latin1_General_BIN NULL ,
[DatShp] [varchar] (50) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrdersSuccessFail] (
[OrderId] [char] (6) COLLATE Latin1_General_BIN NULL ,
[SuccessFail] [varchar] (50) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Orders] ADD
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderNumber]
 
ON [PRIMARY]GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER testtrigger ON dbo.Orders
FOR INSERT
AS
DECLARE @OrderID VARCHAR(20)
SELECT @OrderID = OrderNumber
FROM Inserted
COMMIT TRANSACTION
BEGIN TRANSACTION
insert into OrdersSuccessFail select @OrderID, 'Fail'
COMMIT TRANSACTION
BEGIN
EXEC prProcessOrder @OrderID
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE prProcessOrder @OrderId as char(6) AS
declare @ErrorCode int
Select @ErrorCode = @@Error
-- The following code causes failure due to incorrect datatype
If @ErrorCode = 0
Begin
Insert into OrdersLog select @OrderId, 'mmm'
Select @ErrorCode = @@Error
End
If @ErrorCode = 0
begin
update OrdersSuccessFail set SuccessFail = 'Success' where OrderId =
@OrderId
end
GO
-
okay Jeff - use your infinite reserves of patience and explain to me where Rob says that the row needs to be "rolled back"...I've read this post several times over to see where this is spelled out and I still "don't get it"...
As I understand it, the row needs to be inserted regardless of the success/failure of the proc - the proc is called from the trigger - all this will happen ONLY IF the trigger is fired and the trigger will be fired ONLY IF the t-sql responsible for the insertion succeeds...what am I missing ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
Ok... first things first...
Rob... I'm confused... I thought you were saying that some sproc OUTSIDE of the trigger was being rolled back and that you need what was happening inside the trigger to "stick" even if the OUTSIDE sproc failed... is that true? Please explain one more time
Sushila... as of this very moment, I'm as confused as you are... I'm not sure what Rob means, anymore
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Sorry for the confusion folks....
In the example above, I want the original insert statement to work...
insert into Orders select '1111','1/1/2006'
Even if the procedure called by the trigger fails....
EXEC prProcessOrder @OrderID
That is why I placed the "COMMIT TRANSACTION" after obtaining the "Inserted" value within the trigger.
The reason I placed the "COMMIT TRANSACTION" within the trigger is to Commit the implicit transaction that the trigger itself begins.
From what I understand, if the code attached to a trigger fails (in this case prProcessOrder), then the underlying action (the original Insert in this case) fails as well. Well, they do not want the original insert to fail.
Assume that the original insert is simply some ADO insert statement.
Thanks !
-
And that's why I said that the trigger is fired only after the insert is successful - at the point that it executes you already have a row inserted in your "Orders" table...
"The trigger executes after the statement that triggered it completes. If the statement fails with an error, such as a constraint violation or syntax error, the trigger is not executed." (from BOL)
**ASCII stupid question, get a stupid ANSI !!!** -
Rob - a million apologies - I was so focusing on the fact that the trigger does not ever get fired if the insert fails that I didn't pay attention to the second part - of transactions within the trigger failing...a "commit" then should work though I've never had cause to use one since usually most business rules go for an "all or nothing" basis..I'll test it as soon as I get a break..or maybe Jeff will be back to confirm/negate..
**ASCII stupid question, get a stupid ANSI !!!** -
In the example I presented, if you comment out the first COMMIT TRANSACTION (in the trigger), then the stored procedure that is called by the trigger (EXEC prProcessOrder @OrderID) will fail (I placed a statement in the stored proc to make it fail on purpose). then you will notice that the original insert (insert into Orders select '1111','1/1/2006') fails as well.
I believe this is because SQL treats the whole process as 1 transaction that gets rolled back if there are any errors at all within the trigger.
Thanks
-
Rob - a couple of things - I was going to tell you to take the "exec proc" out of the trigger and make it a separate command when I came acoss this other post with an identical requirement....erland sommarskog (no less) has this to say:
"If I understand this correctly, you have a mission-critical application, and now you want to tap data from a table, but without any risk of disturbing the normal flow. In such case a trigger is probably not the way to go, because it is part of trigger fundamentals that a trigger is whole together with the statement that it is attached to. If the trigger fails, then the statement has failed, and there is no way around that, nor should there be. You should probably look into replication. In this case, the committed data is read from the transaction log at some later point in time it reaches its destination."
On the other hand I did run some quick tests - and you're right...adding a "commit" at that point does seem to make the insert "stick" despite a failing sproc...you may have just found a really simple way to avoid going through some convoluted hoops...
**ASCII stupid question, get a stupid ANSI !!!** -
I'll say... as for me, I can't find the examples I had made for something else (might not even be the same problem
but dunno, anymore) and I can't make what I said work (shame on me for not testing prior to opening my mouth ). Either it was some really bad coffee or Alzheimers is finally settling in 
I'm going to my room to suck my thumb and twiddle my hair, now... --Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Yes, I posted this in the public forums as well... and yes erland sommarskog has responded to me as well...
He does not like the "quick fix" (and rightly so for the reasons he states), and quite frankly I never liked it either, but I need some ammo to prevent it being employed as a temporary solution. I also need to find a new solution... You know how it goes..., if you do not want to do that, then what "DO" you want to do .
Problem is that there are also problems with any of the other suggestions I can think of... these are 2 processes that must be very closely coupled (scheduled jobs won't cut it). Also, the program that is inserting the row is another application altogether, so it cannot be modified.
Thanks again for your help and taking the time to look at it...
-
You can always start a job in the trigger (in another process with an alert or raiserror... don't remember which). That way there would be no delay besides the execution of the code...
BTW you can also schedule a job to run every 0.001 second if you want to... But I would suggest every second if that's acceptable to you
.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply