Solved

Trigger + Dynamic SQL + @@Error variable

Posted on 2004-08-03
10
665 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
 

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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:ScottPletcher
ScottPletcher 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

912 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now