Server scoped ddl triggers Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 93 of sequel server
tutorial in this video well discuss
creating service coped Tyrael triggers
this is continuation to part 92 so
please watch part 92 before proceeding
here we have a database scoped trigger
notice next to the on keyword we have
specified the scope as database and look
at what this trigger is doing it's
preventing users from creating altering
or dropping a table so I have this exact
same code already typed here I'm going
to execute this create trigger statement
within the context of sample DB database
so this trigger will be now created in
that database and if we try to create a
table within the sample DB database that
trigger should prevent us from doing
that notice we get the error message
now this trigger is present only within
sample DB database because this is a
trigger that is scoped to that database
I have another database here HR DB now
if I try to create a table within that
database will I be allowed to do that
let's see so when I execute this create
table statement in the context of HR DB
notice that we are able to create the
table without any problem now let's say
for some reason even in this database we
want to prevent users from creating
altering or dropping tables now one way
to achieve this is by creating this same
trigger even in this HR DB database this
approach is okay if we have just one or
two databases now imagine if we have
hundred databases on this instance of
sequel server and in all those hundred
different databases we want to prevent
users from creating altering or dropping
tables now in this case definitely
creating you know the same trigger in
all those hundred different databases is
not a right approach and it's not right
for two reasons one it is tedious and
error-prone to maintainability is going
to be a nightmare because if we have to
change the logic in the trigger then we
will have to do the change in all the
hundred different databases which again
is going to be tedious and error-prone
so this is when service cope triggers
are going to come in handy
and creating service coke triggers is
very similar to creating database scoped
triggers all you have to do is change
the scope from database to all server so
let's create a service scope trigger
first let's delete this database scope
trigger that we already have in our
sample DB database so database scope
triggers can be found in that database
and the programmability folder you will
have database triggers folder so let's
go ahead and delete that
you know database scope trigger and I'm
going to give this trigger a meaningful
name let's call this server scope
trigger and we wanted hope to be server
so we have to specify the scope as all
server now let's go ahead and execute
this create trigger statement notice we
are executing this within the context of
sample DB database but this trigger will
not be created in that sample DB
database so we are within the sample DB
database so if i refresh this database
triggers folder and expand it notice
that we can't find this trigger there so
our obvious next question is where is
this trigger created then this actually
created the server level so here we have
the server objects folder if we expand
that we have got triggers folder and
when we expand that we can find our
server scope trigger there all right now
let's try to create a table within
sample DB database we should still be
prevented from doing that look at that
it happens and if you look at the HR DB
database we already have created a test
table inside that so let's see if that
table is there so the table is still
there now if you look at what the
trigger is doing it's also preventing
users from dropping the table so if we
try to drop the table so let's write a
command for that drop table the table
name is test we should be prevented from
doing that notice we are in the context
of HR DB database look at that they
still get the error now here we are
using a sequel command to do that now if
I try to delete this table using the
graphical user interface of sequel
server manager
studio will I be allowed to do that
let's say so when I try to delete it by
right clicking on that and selecting
delete and when I click OK notice that
we still get an error message and if you
look at what the error message is it
says drop failed for table test and here
is the message the transaction ended in
the trigger we get the same error
message it expect your very use a
graphical user interface or a sequel
command you will still not be able to do
that because the trigger is preventing
it all right now to create a trigger you
know a service cope trigger we use
create trigger statement and we just
specify the scope as all server now if
you want to alter this trigger all you
do is instead of create you use Alta and
if you want to disable the trigger then
we use disable command so disable
trigger trigger name and we have to
specify the scope since this is service
scoped trigger we have to specify on all
server so when we execute this notice
that command is completed successfully
that means the trigger is disabled and
we should be able to create a table look
at that when I try to execute this
within the sample DB I am allowed and if
you want to enable it back simply use
enable and if you want to drop it simply
use drop
thank you for listening and have a great
day