Solved

Trigger + Dynamic SQL + @@Error variable

Posted on 2004-08-03
10
664 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

747 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

10 Experts available now in Live!

Get 1:1 Help Now