Solved

Trigger + Dynamic SQL + @@Error variable

Posted on 2004-08-03
10
668 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
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 125 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 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 125 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

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.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

685 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