SQL server trigger execution order Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 94 of sequel server
tutorial in this video we'll discuss how
to set the execution order of triggers
one thing to keep in mind is that the
service cope triggers will always fire
before any of the database cope triggers
this cannot be changed let's actually
prove this with an example
here we've got two triggers the first
trigger is scoped to the database and
this trigger will fire in response to
create table event and look at what this
trigger is doing its simply printing
this message database code trigger the
second trigger right here is scoped to
the server and this trigger will also
fire in response to the same event that
is create table and this trigger is
printing a message saying server scope
trigger so let's create these two
triggers at the moment we've got two
triggers one scoped at the database
level and the other one at the server
level both of them firing responds to
the same event that is create table so
now if we go ahead and create a table
both of the triggers will be fired and
if you look at the order in which they
are fired server scope trigger is fired
first and then the database scope
trigger so service scope triggers will
always fire before any of the database
cope triggers and that cannot be changed
however we can use this system stored
procedure SP underscore set trigger
order to set the execution order of a
the silver scope or database scoped
triggers meaning if you have let's say
five different server scope triggers
responding to the same event then you
can control which trigger is going to
fire last or which trigger is going to
fire first using this system store
procedure and this procedure has got
four parameters the first one is trigger
name which specifies the name of the
trigger the second one is order so the
value for this parameter can be first
last or none if you set the value to
none
then the trigger is fired in a random
order the third parameter is statement
type which specifies the sequel
statement that fires the trigger for DML
triggers it can be insert update delete
etc or any DDL event
and the final parameters namespace which
specifies the scope of a DDL trigger for
a database scoped trigger the value will
be database for service corporate the
value will be server otherwise now and
here we have an example of how to use
this system store procedure so let's
understand this with an example
right here we've got three database
scoped triggers notice all of them are
scoped to database and if you look at
the events for which they are responding
all of them will fire and response to
the same three events create table alter
table and drop table and look at what
those triggers are doing the third
trigger here that is database scope
trigger 3 is printing this message
database scope trigger 3 similarly a
second one is printing database scope
trigger 2 and the first one database
scope trigger 1 and notice the order in
which we are going to create them the
third one first second one second and
first one last ok
the reason this order is important
because by default these triggers will
fire in the order in which they are
created and we can change that default
behavior using this system store
procedure so let's create these three
triggers before we do that let's
actually delete the other triggers that
we already have so I'm going to first
delete the database scoped one and then
the server scoped 1 now let's create
these 3 triggers and now we already have
this test table so I'm going to drop it
and these three triggers should still
fire because you know we want them to
respond to drop table event as well so
let's drop the test table and look at
this so the order in which they are
executed is the order in which they are
created we created the third trigger
first and then the second trigger and
then the first trigger and that's the
order in which they are executed that's
the default behavior now for some reason
if you want to change that execution
order then you can use this system store
procedure SP and
go set trigger order and here we have an
example so if you look at the first
execute statement here we are changing
the execution order of database Koch
trigger 1 we are setting the order for
that one too fast first and the
statement type is create table so that's
the DDL event for which we want this
trigger to be executed first and the
namespace of this one is database and
similarly database scope trigger 3 we
want that to fire last and again for the
same event create table and the scope
based database so let's go ahead and
execute these two statements now let's
create the table so when I execute this
look at that database scope trigger one
first and then database scope trigger 2
and then finally database scope trigger
3 so the order that we have set using
this system stored procedure is
respected however if we drop the table
again those three triggers will fire and
let's see what's the order in which they
are going to fire look at this now the
order is back to what it was three to
one and that's because using this system
stored procedure we have only set the
order for create table event now if you
want that same order for drop table then
you will have to set it again for that
event using this system stored procedure
now if you have a database scoped
and a service cope trigger handling the
same event and if you have said the
execution order at both the levels that
is both at the Silver level and at the
database level then here is the
execution order of the triggers the
service cope trigger marked first will
be fired first and then the other
service cope triggers and then the
service cope trigger that's marked last
and then the database scope triggers
will start so the database scope trigger
that's marked first will fire first and
then the other database scope triggers
and then finally the database scope
trigger that is marked last
thank you for listening and have a great
day