DDL Triggers in sql server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 92 of sequel server
tutorial in this video we'll discuss DDL
triggers in sequel server in sequel
server there are four types of triggers
first we have DML triggers DML stands
for data manipulation language we
discuss these triggers in parts 43 to 47
of sequel server tutorial and then we
have DDL triggers DDL stands for data
definition language in this video we'll
discuss DDL triggers and then we have
clr triggers
CLR stands for common language runtime
and finally log-on triggers so what are
DDL triggers DDL triggers fire in
response to DDL events so the immediate
obvious question that comes to our mind
is what are DDL events and when are
these events raised now whenever you
create alter or drop a database object
then a corresponding D real event is
raised for example when you create a
table using Create table DDL statement
you know the associated event is create
underscore table so that event is raised
and if you have a trigger associated
with that event then when you create a
table automatically that associated
trigger will be fired similarly when you
drop a stored procedure drop underscore
procedure event is raised when you
create a function create underscore
function D real event is raised so for
the full list of D real events please
visit this MSDN length i'll have this
link available on my blog in case you
need it so on this page you can find the
complete list of TTL events so for
example when you create a function look
at that create underscore function event
is raised similarly when you alter a
function alter underscore function event
is raised when you drop a function drop
underscore function similarly for table
we have you know similar events create
and score table albums put table drop
and sport table and for a stored
procedure we have similar events create
an scoped procedure alternative
procedure drop underscore procedure so
whenever you execute DML statements
associated DDL events are raised and if
you have triggers associated with those
events they are fired automatically not
only the DM LST
Eggman's are going to fire DDL traders
we also have certain system stored
procedures that perform DDL like
operations and these system stored
procedure can also fire DDL triggers one
such system stored procedure is SP
underscore rename we use this system
stored procedure to rename a database
object for example we can use it to
rename a table or a column in a table so
whenever we do that using the system
stored procedure it's going to raise
rename event and if you have a trigger
associated with that event it will be
fired automatically when you rename an
object in fact we look at an example of
doing that in this video so what are the
uses of DDL triggers there are several
now if you want to execute some code in
response to a specific DDL event you can
do that because DDL triggers are fired
in response to TTL events so you can put
whatever code you want to execute within
the body of that trigger and whenever
you create a table create underscore
table DDL event is raised and the
associated trigger is going to execute
that code similarly if you want to
prevent certain changes to your database
schema you can use a DDL trigger for
example let's say you want to prevent
users from creating altering or dropping
tables you can do that using a DDL
trigger in fact we look at an example of
how to do that in this video and then
you can also use a DDL trigger towards
the changes that the users are making to
the database structure now let's say
whatever changes people are making to
the database schema I want to capture
all those changes you know I want to
audit those changes and maybe store them
in a table we can very easily achieve
that using a TTL trigger for example if
somebody modifies a table I want to
capture information like what is the
login name of the user who modified that
what is the name of the table what is
the name of the database in which the
table is present what is the date and
time when he did that modification what
is the exact DML statement that they
have executed to do that modification
all that information can be captured
using a DDL trigger in fact we'll look
at an example of how to do that in a
later video session and here we have the
syntax for creating a DDL trigger so
here you know we
that with create trigger and then we
have the trigger name and then we use
the on keyword and then we specify the
scope of the trigger we can create DDL
triggers in a specific database or a
silver wide trigger if you want to
create a know a trigger whose cope is
database then you use the database
keyword otherwise server
well disk is creating server wide DDL
triggers in a later video session and
then we use the for keyword and then
specify the DDL events list here so if
you want this trigger to be fired for
three events create underscore table all
turns code table drop on its code table
you simply separate those events using a
comma and then you use the ask keyword
begin end within begin and end you will
have your trigger body let's look at an
example now so here we have a very
simple example create trigger the name
of the trigger and then we use the on
keyword and database so we are creating
a trigger who scope is database and then
for create underscore table so the name
of the event is create and this code
table so whenever you execute create
table DML statement this event is raised
and we have a trigger associated with
that event so whenever you create a
table this procedure will be
automatically be fired and it's going to
print this message new table created
let's look at that in action so I have
this exact same code already typed here
so I'm going to execute this and it says
command completed successfully now to
see the trigger that we have just
created so we've created this code
within the context of sample DB database
and we are creating a trigger whose
group is the current database so it
should be created in sample DB database
so expand that and then go to
programmability folder and within that
expand database triggers if you can't
find the trigger that you have just
created right click on that and select
refresh from the context menu and you
should find the trigger so that's our
trigger now here we have create table
statement which is going to create a
test table with one column so when we
execute this it should automatically
print this message because this trigger
will be fired look at that the table
should have been
gated and the table is created here and
whatever message and now we have here
that's printed and now which means this
trigger is fired so if you look at this
trigger this is fired in response to a
single event now let's say I want this
trigger to be fired for you know altar
and drop table events as well if that's
the case you simply need to separate the
event names using a comma so I want this
to be fired for all the table and drop
table events as well so let's go ahead
and alter this and let's change the
message here and let's change the
message to you have just created altered
or dropped a table so let's execute this
and now if we try to drop a table so
drop table and the name of the table is
test so this trigger should be fired
notice that it's fine and we get the
message alright so the examples that we
have looked at so far these triggers are
executing some code in response to those
DDL events now another use of triggers
is that you can prevent certain changes
to your database schema now the changes
I want to prevent are let's say whenever
somebody tries to create or alter or
drop a table you know I won't I don't
want that to happen
I want to prevent those changes I can do
that using a DDL trigger so within the
trigger I am simply going to say
rollback
so let's alter this and then let's
change the message - you cannot create
alter or drop a table so let's execute
this alter statement now when we try to
create a table look at that we get the
message you cannot create alter drop a
table and look at the message here the
transaction ended in the trigger so this
trigger basically rolled back the change
so we are not allowed to create a table
and if I actually
fresh this look at that we don't have
that test table there okay so now the
only way to create alter drop a table is
by either disabling this trigger or
deleting the trigger and to disable the
trigger you can use the simple command
disable trigger and the name of the
trigger so the name of the trigger is tr
my first trigger and then this is a
database scope trigger so we have to use
on database keyword and then we execute
this so now the trigger is disabled so
if we execute this create table
statement now it should be completed
successfully because we have disabled a
trigger and if we refresh this we should
have the test table there okay now to
enable the trigger you simply have to
use enable instead of disabled and if
you want to delete the trigger you have
got two options you can either
right-click on that and select delete
from the context menu
or you can use drop trigger trigger name
on database and when we execute this the
trigger should have been deleted so the
trigger is gone now all right now we
also discuss that certain system stored
procedures that perform DDL like
operations can also fire DDL triggers
and one such system stored procedure is
SP underscore rename we use that stored
procedure to change the name of a
database object now let's actually
create you know if you look at SP
underscore rename system stored
procedure
it raises rename event so I'm going to
create a trigger for rename event and
what I want to do is print a message
saying you just renamed something so you
just renamed something and let's alter
this so it says you know invalid because
we don't have the trigger anymore
because we are using the alter statement
there so let's use create
so now the trigger is created now
whenever we rename an object you know it
could be a table or it could be a column
so if you want to rename a table you use
the system stored procedure SP
underscore rename and then specify your
current table name and the new table
name and you know if you want to rename
a column you specify the table name dot
column name and then your new column
name and you have to use this column you
know the diameter here so let's quickly
look at that so when we rename a table
at the moment we have a table with named
test so I'm going to use SB underscore
rename and we want to change the name of
this table from maybe test to new test
and look at this when we execute this
system strip C is going to raise this
free name event and we have a trigger
associated with that event so it should
execute this trigger and print that
message so let's look at that look at
that it's prints that message you just
renamed something and along the same
lines if you want to rename a column in
that table you can use you know this
system stored procedure to do that and
even in that case you know this trigger
will be fired thank you for listening
and have a great day