Transaction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Transaction

Author

Topic

psfaro
Starting Member

49 Posts

Posted - 2011-02-09 : 05:39:03

Hi ,

I've a trigger that Inserts on a LOG table , the records that are updated.

I notice that in a "Transaction" if have a failure in "Inserting on the LOG table(issued by the trigger" , the Transaction is Not "ROLLBACKED ".

The Transaction is Only ROLLBACK if can´t update the table that are declared between "BEGIN ... END TRANS"

Example:

BEGIN TRANS

UPDATE XPTO SET campo=' ' --XPTo TABLE has a Trigger to a LOG TABLE
where campo='P'

if ok
COMMIT
else
ROLLBACK -- if it can't update XPTO , ROOLBACK works ok, if it can't update the LOG (issued by the trigger) ir returns that I have no "BEGIN TRANSACTION "
end

How can solve this situation ?

Regards
Pedro Faro

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-09 : 07:31:57

Are you looking for this ?

Create trigger ....
Begin
--It is BEGIN TRAN and not BEGIN TRANS
BEGIN TRANS

UPDATE XPTO SET campo=' ' --XPTo TABLE has a Trigger to a LOG TABLE
where campo='P'

if @@rowcount >=1
COMMIT Tran
else
ROLLBACK Tran -- if it can't update XPTO , ROOLBACK works ok, if it can't update the LOG (issued by the trigger) ir returns that I have no "BEGIN TRANSACTION "

End

psfaro
Starting Member

49 Posts

Posted - 2011-02-09 : 10:30:25

Hi Pk_Bohra

Tanks for the answer.

I still not understand why MSSQL lost "BEGIN TRANSACTION" instruction when an error ocurred in the Table when the Trigger Fired.

As i said , if the trigger returns no error , even if the "UPDATE on the main table " returns any error, the TRANSACTION is ROLLBACK.

May Be Trigger Internally creates a Transaction and ignores the previous begin transaction issued.

Regards
Pedro

quote:
Originally posted by pk_bohra

Are you looking for this ?

Create trigger ....
Begin
--It is BEGIN TRAN and not BEGIN TRANS
BEGIN TRANS

UPDATE XPTO SET campo=' ' --XPTo TABLE has a Trigger to a LOG TABLE
where campo='P'

if @@rowcount >=1
COMMIT Tran
else
ROLLBACK Tran -- if it can't update XPTO , ROOLBACK works ok, if it can't update the LOG (issued by the trigger) ir returns that I have no "BEGIN TRANSACTION "

End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources