marrowyung
asked on
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?
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?
Vitor Montalvão
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thank you ste5an.
Always thought that was impossible but you just proved it isn't.
Cheers.
Always thought that was impossible but you just proved it isn't.
Cheers.
ASKER
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 !
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 !
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.
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.
ASKER
"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 ?
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 ?
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
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.
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.
ASKER
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 ?
"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 ?