• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 679
  • Last Modified:

Trigger + Dynamic SQL + @@Error variable

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
girishlimaye
Asked:
girishlimaye
2 Solutions
 
billy21Commented:
Can you please post the entire trigger.  Also why are you using dynamic sql?
0
 
illCommented:
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
 
crescendoCommented:
Don't use "if(@@error<>0)"

ALWAYS use

@DECLARE @Err int

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


0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
girishlimayeAuthor Commented:
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
 
crescendoCommented:
Why have you got a COMMIT straight after a ROLLBACK?
0
 
crescendoCommented:
You can nest transactions. A rollback will only rollback to the last "begin".

0
 
girishlimayeAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now