execution of trigger on SQL server table

Dear all,

from trigger point of view, e.g. a insert trigger, if there are more than one trigger defined on a table, there should be no way to make the trigger execute one after the other, they should be fired all at the same time?

anyway can control the order of execution of trigger?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can have only one trigger for each kind of operation for table, i.e. you can only have one INSERT trigger, one UPDATE trigger and one DELETE trigger.
NalinkumarbalajiCommented:
Hi,

My suggestion is, please don't use "Triggers", Instead it is better to design the Architecture (using Stored Procedures) like that, so that we can use "Stored Procedures", we can call "Stored Procedure" in which order we like.

And using trigger is situational needs and the usage. That we can't avoid in some situations.

-SNKBalaji
ste5anSenior DeveloperCommented:
@Vitor: You can have multiple triggers of the same kind..

USE Test;
GO

SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

CREATE TABLE dbo.Test
    (
      ID INT NULL ,
      Payload INT NULL
    );
GO

CREATE TABLE dbo.TestHistory
    (
      ID INT NULL ,
      Payload INT NULL ,
      ts TIMESTAMP NOT NULL ,
      dt DATETIME NULL
    );
GO

CREATE TRIGGER tr_Test_I1 ON Test
    AFTER INSERT
AS
    SET NOCOUNT ON;

    INSERT  INTO TestHistory
            ( ID ,
              Payload ,
              dt
            )
            SELECT  I.ID ,
                    I.Payload ,
                    GETDATE()
            FROM    Inserted I;
GO

CREATE TRIGGER tr_Test_I2 ON Test
    AFTER INSERT
AS
    SET NOCOUNT ON;

    INSERT  INTO TestHistory
            ( ID ,
              Payload ,
              dt
            )
            SELECT  I.ID ,
                    I.Payload ,
                    GETDATE() + 1
            FROM    Inserted I;
GO

Open in new window


@marrowyung: When you have multiple triggers, there is imho no way to serialize the execution in a given way. The only exception is when you have less than 4 triggers.

You can define the first and the last trigger in the execution chain. But others are executed in random order. Say you have five insert triggers I1 to I5. Then you can define that I1 is executed first and I5 is executed as last trigger. But the others may have any order I2,I3,I4 or I2,I4,I3 or I3,I4,I2 or I3,I2,I4 or I4,I2,I3 or I4,I3,I2.

See the remarks section of CREATE TRIGGER and sp_settriggerorder (Transact-SQL).

SNKBalaji is right, it's better to avoid triggers when possible. But this is an architectural question. Also it is better to consolidate multiple triggers into one.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vitor MontalvãoMSSQL Senior EngineerCommented:
Thank you ste5an.

Always thought that was impossible but you just proved it isn't.

Cheers.
marrowyungSenior Technical architecture (Data)Author Commented:
Victor,

In MySQL we can't, this is true! this make me don't want to use MySQL. e.g. can't do 2 x after insert trigger but can do before and after insert trigger.

 tks for ste5an to show me and this show me MSSQL is good for a lot of platform.

Nalinkumarbalaji,

"My suggestion is, please don't use "Triggers", Instead it is better to design the Architecture (using Stored Procedures) like that, so that we can use "Stored Procedures", we can call "Stored Procedure" in which order we like.
"

tks. I knew that a lot of application design in this way and usually all those application related SP will be inside SQL jobs and those SQL job is disabled all the time.

as in this way application can still call it and the SQL job is not going to run by itself anyway.

ste5an,

"Say you have five insert triggers I1 to I5. Then you can define that I1 is executed first and I5 is executed as last trigger. But the others may have any order I2,I3,I4 or I2,I4,I3 or I3,I4,I2 or I3,I2,I4 or I4,I2,I3 or I4,I3,I2. "

ok, but how can we define I1 is first and the I5 the last?

"sp_settriggerorder (Transact-SQL)."

for this, it seems only applicable to AFTER triggers?

https://msdn.microsoft.com/en-us/library/ms186762(v=sql.100).aspx

from my point of view, no way to define the order the trigger start but they all fired out AT THE SAME TIME, so this means no, right?

so sp_settriggerorder  is a global SP, just a system configuration ? but will the system give error if that trigger later on disabled/deleted due to the change in business requirement.

"Also it is better to consolidate multiple triggers into one. "

yes, agree !
ste5anSenior DeveloperCommented:
They are all AFTER triggers. The alternative are INSTEAD OF triggers. The AFTER denominates that it is executed after the SQL statement. An INSTEAD OF means, that SQL statement is NOT executed, and only the the trigger is called with the pseudo-tables INSERTED and DELETED filled.

A disabled trigger will not be executed. sp_settriggerorder does only set which one is executed first/last. If one of those is disabled, then you can only no longer determine which trigger will be first/last.
marrowyungSenior Technical architecture (Data)Author Commented:
"Also it is better to consolidate multiple triggers into one. "

one question on this, why combining triggers to one trigger is good ?

"They are all AFTER triggers. "

So you are talking about AFTER trigger all fired at the same time ?
 
"An INSTEAD OF means, that SQL statement is NOT executed, and only the the trigger is called with the pseudo-tables INSERTED and DELETED filled. "

sorry, what is the meaning of "pseudo-tables INSERTED and DELETED filled. "?

"sp_settriggerorder does only set which one is executed first/last. If one of those is disabled, then you can only no longer determine which trigger will be first/last. "

so this means the first and last definition is lost and nothing will be define, so it just execute randomly and this also DON'T mean that all trigger execute on the SAME time ?
ste5anSenior DeveloperCommented:
1) Cause you can control the execution order. And avoid for example hitting multiple tables rows in your logic.

2) No. I just wasn't sure about your post. But the key point is: They are not "fired" the same time. See your link

Specifies the AFTER triggers that are fired first or last. The AFTER triggers that are fired between the first and last triggers are executed in undefined order.

Triggers are executed per statement. To work with the affected rows you need a mechanism to handle those. These are the pseudo or virtual tables INSERTED and DELETED. A INSERT trigger fills the INSERTED table, DELETED is empty. An UPDATE trigger fills both tables. And a DELETE trigger fills only the DELETED table.

See Use the inserted and deleted Tables.
marrowyungSenior Technical architecture (Data)Author Commented:
2)
"sp_settriggerorder (Transact-SQL)."


 for this, it seems only applicable to AFTER triggers?

so it the trigger can have first and last fire order control is just for AFTER trigger? or all Trigger is not going to fired at the same time and what you mean is it depends on it  AFTER/BEFORE, insert/delete/update? this is the control you mean?

but what is 1) you are referring to ?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.