Transaction in trigger - 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 2005 Forums

Transact-SQL (2005)

Transaction in trigger

Author

Topic

deepakugale
Starting Member

33 Posts

Posted - 2010-03-02 : 01:50:36

Can i use transaction in trigger ?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-02 : 01:58:00

Why you want to do that ?

Refer the below link which may be useful for you..
http://weblogs.sqlteam.com/derrickl/archive/2004/03/30/1150.aspx

Regards,
Bohra

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 08:50:31

you can. what do you want to do inside transaction?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

deepakugale
Starting Member

33 Posts

Posted - 2010-03-03 : 04:29:35

More elongated,If i have trigger "T1" on insert operation for table "A" ,i have used transacion in trigger,In trigger there is insert operation on table "B",Table "B" also has trigger "T2" on insert operation,this trigger also has transaction in it.If insert operation happens on table "A",trigger "T1" and "T2" will also fire,but runtion error occures in "T2" will it rollback all the operation from "T1" . or it will only rollback operation from "T2" ?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-03 : 08:27:44

quote:
Originally posted by deepakugale

More elongated,If i have trigger "T1" on insert operation for table "A" ,i have used transacion in trigger,In trigger there is insert operation on table "B",Table "B" also has trigger "T2" on insert operation,this trigger also has transaction in it.If insert operation happens on table "A",trigger "T1" and "T2" will also fire,but runtion error occures in "T2" will it rollback all the operation from "T1" . or it will only rollback operation from "T2" ?

Why you are going for such nested level trigger. It not only hit the performance but also cost a lot when debugging an issue..

To answer your question:
When you start the transaction value of @@TranCount is incremented by 1 and when you say Commit transaction the value of @@Trancount is decreased by 1. When the value of @@TranCount comes to 0 (last commit and no error during middle process) the changes are committed.

If in middle level transaction you are calling rollback transaction then all the changes done are lost and value of @@TranCount is set to 0.

You have a feature of naming the transaction and saving it and rollbacking it to a particular level.. I just read abt it long back and never tried it..

Regards,
Bohra

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 09:18:32

quote:
Originally posted by deepakugale

More elongated,If i have trigger "T1" on insert operation for table "A" ,i have used transacion in trigger,In trigger there is insert operation on table "B",Table "B" also has trigger "T2" on insert operation,this trigger also has transaction in it.If insert operation happens on table "A",trigger "T1" and "T2" will also fire,but runtion error occures in "T2" will it rollback all the operation from "T1" . or it will only rollback operation from "T2" ?

i think it would be best for you to wrap all these inserts in a single trigger or even in single procedure within a transaction

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

deepakugale
Starting Member

33 Posts

Posted - 2010-03-04 : 01:58:48

OK ,Thanks a lot ,this means there is no use of nested transaction, as nested transaction only increment @@TranCount ,All changes get saved to database permanently only when @@TranCount = 0 ,right ?

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-04 : 02:04:14

Could i ask here,
why OUTPUT clause can't be used?.

deepakugale
Starting Member

33 Posts

Posted - 2010-03-04 : 03:14:05

I don't want any output from the query ,i only want assurance that changes happened or not ........

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 09:07:10

quote:
Originally posted by deepakugale

I don't want any output from the query ,i only want assurance that changes happened or not ........

haroon was refering to OUTPUT clause not OUTPUT parameters. you could use that to captured contents of INSERTED & DELETED outside trigger

see

http://visakhm.blogspot.com/2010/02/capturing-audit-table-values-inline-aka.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-04 : 09:11:16

quote:
Originally posted by visakh16

quote:
Originally posted by deepakugale

I don't want any output from the query ,i only want assurance that changes happened or not ........

haroon was refering to OUTPUT clause not OUTPUT parameters. you could use that to captured contents of INSERTED & DELETED outside trigger

see

http://visakhm.blogspot.com/2010/02/capturing-audit-table-values-inline-aka.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Good Visakh.can i ask,First Trigger has to drop on the table which op
was created then output clause can be used to do so?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 09:18:16

sorry did get that. drop what?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-04 : 09:22:12

quote:
Originally posted by visakh16

sorry did get that. drop what?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nope.Sorry for not clear..
as per op

quote:

More elongated,If i have trigger "T1" on insert operation for table "A" ,i have used transacion in trigger,In trigger there is insert operation on table "B",Table "B" also has trigger "T2" on insert operation,this trigger also has transaction in it.If insert operation happens on table "A",trigger "T1" and "T2" will also fire,but runtion error occures in "T2" will it rollback all the operation from "T1" . or it will only rollback operation from "T2" ?

visakh.I was asking like before OUTPUT Clause used,Trigger has to be dropped if trigger has created on that table?

still iam not clear,,please excuse me..ask me agian

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 09:22:13

quote:
Originally posted by visakh16

sorry did get that. drop what?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

haroon2k9 is asking whether you can use OUTPUT clause if there is no trigger on the base table

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 09:25:50

quote:
Originally posted by haroon2k9

quote:
Originally posted by visakh16

sorry did get that. drop what?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nope.Sorry for not clear..
as per op
quote:

More elongated,If i have trigger "T1" on insert operation for table "A" ,i have used transacion in trigger,In trigger there is insert operation on table "B",Table "B" also has trigger "T2" on insert operation,this trigger also has transaction in it.If insert operation happens on table "A",trigger "T1" and "T2" will also fire,but runtion error occures in "T2" will it rollback all the operation from "T1" . or it will only rollback operation from "T2" ?

visakh.I was asking like before OUTPUT Clause used,Trigger has to be dropped if trigger has created on that table?

still iam not clear,,please excuse me..ask me agian

yup. you need to drop that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-04 : 09:26:13

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

sorry did get that. drop what?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

haroon2k9 is asking whether you can use OUTPUT clause if there is no trigger on the base table

Madhivanan

Failing to plan is Planning to fail

Madhi..sorry i was not clear..iam asking like,if trigger has created on a table,can output clause be used on it?...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 09:29:23

quote:
Originally posted by haroon2k9

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

sorry did get that. drop what?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

haroon2k9 is asking whether you can use OUTPUT clause if there is no trigger on the base table

Madhivanan

Failing to plan is Planning to fail

Madhi..sorry i was not clear..iam asking like,if trigger has created on a table,can output clause be used on it?...

you cant have trigger for same event you're using OUTPUT clause without INTO option

from BOL

If the OUTPUT clause is specified without also specifying the INTO keyword, the target of the DML operation cannot have any enabled trigger defined on it for the given DML action. For example, if the OUTPUT clause is defined in an UPDATE statement, the target table cannot have any enabled UPDATE triggers.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-04 : 09:31:01

quote:
Originally posted by visakh16

quote:
Originally posted by haroon2k9

quote:
Originally posted by visakh16

sorry did get that. drop what?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nope.Sorry for not clear..
as per op
quote:

More elongated,If i have trigger "T1" on insert operation for table "A" ,i have used transacion in trigger,In trigger there is insert operation on table "B",Table "B" also has trigger "T2" on insert operation,this trigger also has transaction in it.If insert operation happens on table "A",trigger "T1" and "T2" will also fire,but runtion error occures in "T2" will it rollback all the operation from "T1" . or it will only rollback operation from "T2" ?

visakh.I was asking like before OUTPUT Clause used,Trigger has to be dropped if trigger has created on that table?

still iam not clear,,please excuse me..ask me agian

yup. you need to drop that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks visakh.i saw your link just now for the output clause.I Learnt about Composable DML can be used in sql server 2008 and what does Composable DML mean as well..Your explanation is really great..iam a beginner of sql..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 09:31:09

quote:
Originally posted by haroon2k9

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

sorry did get that. drop what?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

haroon2k9 is asking whether you can use OUTPUT clause if there is no trigger on the base table

Madhivanan

Failing to plan is Planning to fail

Madhi..sorry i was not clear..iam asking like,if trigger has created on a table,can output clause be used on it?...

No

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 09:36:15

quote:
Originally posted by haroon2k9

quote:
Originally posted by visakh16

quote:
Originally posted by haroon2k9

quote:
Originally posted by visakh16

sorry did get that. drop what?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nope.Sorry for not clear..
as per op
quote:

More elongated,If i have trigger "T1" on insert operation for table "A" ,i have used transacion in trigger,In trigger there is insert operation on table "B",Table "B" also has trigger "T2" on insert operation,this trigger also has transaction in it.If insert operation happens on table "A",trigger "T1" and "T2" will also fire,but runtion error occures in "T2" will it rollback all the operation from "T1" . or it will only rollback operation from "T2" ?

visakh.I was asking like before OUTPUT Clause used,Trigger has to be dropped if trigger has created on that table?

still iam not clear,,please excuse me..ask me agian

yup. you need to drop that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks visakh.i saw your link just now for the output clause.I Learnt about Composable DML can be used in sql server 2008 and what does Composable DML mean as well..Your explanation is really great..iam a beginner of sql..

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources