Prev: Query and Extract
Next: Invalid object name when I try to create a scaler udf in a stored procedure.
From: Erland Sommarskog on 16 Jun 2010 17:49
Simon Eng (SimonEng(a)discussions.microsoft.com) writes:
> With regard to the use of a CURSOR, you're right and I would prefer a
> set based solution, but I also want a way to bundle the transactions on
> an account by account basis so that if one account fails to post it
> doesn't blow up or delay the overall process. Each transaction set (by
> account) is balanced, so missing a set will annoy one guest, but won't
> throw the system out of balance or annoy all the guests in the hotel.

Keep in mind that when you have nested BEGIN TRANSACTION, all that happens
is that @@trancount is incremented, and a COMMIT only decrements. A
ROLLBACK TRANSACTION roll backs the lot. So if you run this from a
trigger, there will be a single transaction for all accounts.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Eric Isaacs on 16 Jun 2010 19:36

> Keep in mind that when you have nested BEGIN TRANSACTION, all that happens
> is that @@trancount is incremented, and a COMMIT only decrements. A
> ROLLBACK TRANSACTION roll backs the lot. So if you run this from a
> trigger, there will be a single transaction for all accounts.

Erland, are you suggesting that a trigger has it's own transaction, so
the @@trancount is 1 by default? If the @@TranCount is 1, and a
transaction is committed, it's committed, even if it's in a trigger,
right?

-Eric Isaacs
From: Erland Sommarskog on 17 Jun 2010 02:44
Eric Isaacs (eisaacs(a)gmail.com) writes:
> Erland, are you suggesting that a trigger has it's own transaction, so
> the @@trancount is 1 by default? If the @@TranCount is 1, and a
> transaction is committed, it's committed, even if it's in a trigger,
> right?

A trigger always executes in the context of the transaction defined by
the statement that fired the trigger.

If you only issue COMMIT TRANSACTION in a trigger, you will commit the
transaction. Then you will get an error when you exit the trigger,
because that is not supposed to happen.

If you issue both BEGIN TRANSACTION and COMMIT TRANSACTION in the
trigger (or a procedure called by the trigger), all that happens is
that @@trancount is first incremented and then decremented.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Eric Isaacs on 17 Jun 2010 03:18
Erland,

I see your point. Good call.

-Eric Isaacs
First  |  Prev  | 
Pages: 1 2
Prev: Query and Extract
Next: Invalid object name when I try to create a scaler udf in a stored procedure.