Trigger + Dynamic SQL + @@Error variable
Posted on 2004-08-03
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)
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..