Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Trigger + Dynamic SQL + @@Error variable

Posted on 2004-08-03
10
Medium Priority
?
672 Views
Last Modified: 2008-02-01
I'm iusing a AfterInsert Trigger in SQL Server 2000..and I'm using dymic sql in it and using sp_executesql to execute it.
so, my problem is that, when ever this dynamic query fails, I'm unable to see the corresponding row in the original table on which trigger is set. I'm using Begin Transaction in the dynamic SQl and tring to catch the error by @@error variable and I'm unable to catch it and take rollback action.

Trigger looks like this(forget the syntax --- it is set in table A):

'
Insert Into table1(x,y)
if(@@error<>0)
Begin
Rollback transaction
End
Else
Begin
Commit Transaction
End'

and the weird thing happening is...when this trigger (dynamic SQL )fails there is no corresponding row in Table A

Can some one explain this..
thanx

0
Comment
Question by:girishlimaye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 6

Expert Comment

by:billy21
ID: 11706290
Can you please post the entire trigger.  Also why are you using dynamic sql?
0
 
LVL 12

Expert Comment

by:ill
ID: 11706988
i think you are already in transaction when trigger is fired. so when you rollback you trans, you are also rollbacking most upper transaction, which mean  insert into tableA
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11707155
Don't use "if(@@error<>0)"

ALWAYS use

@DECLARE @Err int

INSERT....
SELECT @Err = @@ERROR
IF @Err <> 0


0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

Author Comment

by:girishlimaye
ID: 11708886
Okay, my question is this....I want to know is there any way in SQL Server 2000 triggers, to use Transaction RollBack and then Not to rollback everything(The Insert in beacuase of Original SQLStatement)  but to rollback up to certain point in trigger only....

Say, the trigger is looks like this

Triggered b'coz of insert in to TableA: Insert into TableA(a,b)Values(c,d)

Trigger:

Begin Transaction
Insert into Tbl1(x,y)(1,2)
if(@@error<>0)
Begin
Rollback Transaction
Commit Transaction
End
Else
Commit Transaction

but the above one will roll back everything including insert in to the tableA ...so I want to avoid it...and only insert inside the trigger(insert in to table1) has to be rolled back..how to do this...thinking of using save transaction points..is this the right way...?
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11709135
Why have you got a COMMIT straight after a ROLLBACK?
0
 
LVL 9

Accepted Solution

by:
crescendo earned 500 total points
ID: 11709182
You can nest transactions. A rollback will only rollback to the last "begin".

0
 

Author Comment

by:girishlimaye
ID: 11709360
I have to commit after a rollback b'coz it still uses the resources it has been using...and they have to be released...I've read in msdn that trigger it self is more like a nested transaction and executing rollback Transaction rolls back to the outer most transaction which is nothing but the original transaction...so,nested transactions, I don't think are  going to work...
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 11710188
Try SAVE TRANSACTION, for example:


FOR ...
AS
...
SAVE TRANSACTION yourSaveNameHere  --sets a "save" point  you can rollback to
...
IF <error>
BEGIN
    ROLLBACK TRANSACTION yourSaveNameHere
END --IF


Eventually a COMMIT TRAN must be done on the whole transaction, but SQL should do that as part of the SQL statement itself (implicit trans) or you should as part of your code (explicit trans).
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question