Audit table changes in sql server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 95 of sequel server
tutorial in this video we'll discuss how
to order table changes in sequel server
using a DDL trigger so here is what we
want to do we want to keep track of all
table changes that are happening across
all our databases in a given sequel
server instance for example when
somebody creates a new table we want to
capture all this information we want to
capture the name of the database in
which that table is created the table
name itself and what's the event type
whether it is create table alter table
or drop table the login name associated
with the person who made that change the
exact transact sequel command that they
have executed and what is the date and
time when that change happen in order to
achieve this we're going to make use of
this event data function so what is this
event data function now to understand
this let's take an example when we
create a table we know that a DDL event
is triggered and that event is create
underscore table now this event data
function is going to return as the data
associated with that DDL event and it's
going to return that in an XML format
and this is how the data is going to
look like let's actually look at this in
action so here I've got a trigger the
name of the trigger is TR unscored it
people changes and this is a server
scoped trigger and this will fire in
response to create a table alter table
and drop table events and if you look at
what this trigger is doing it's simply
selecting whatever this event data
function returns so let's go ahead and
create this trigger so now if we create
a table this trigger will fire and it's
going to return the data associated with
this create table event in an XML format
let's look at that in action so here we
have create table statement so when I
execute that notice we get the event
data and an XML format and when I click
on that notice that we get the event
data so first we have the event type so
create table is the event type post time
so whenever this change happened the
server process ID the server name login
name user name
database name schema name object name
that is our table name object type and
the exact transact sequel command that
we have executed so now we can use this
XML data retrieve whatever pieces of
information that we want and store them
in this audit table so the first thing
to do now is create this audit table and
here I have called the sequel script to
create that table so I'm going to
execute this script and notice we are
creating this table in sample DB
database and look at this when I created
this table changes table and again we
have the XML data associated with that
create table event okay and now we have
to write the trigger to read that XML
information and then insert that XML
data into our audit table and here I
have that trigger already so T our audit
table changes since the name of the
trigger
it's server scope and this trigger is
going to respond to these three events
and look at what we are doing within the
body of the trigger we are creating a
variable of type XML and the name of the
variable is event data and in this
variable we are storing whatever event
data this function is going to return
and then we are inserting data into
table changes table now it's very
important that we give the fully
qualified name here
because this is a server scoped trigger
now if you just specify the name of the
table you know it is not going to find
that table you have to give it the fully
qualified name the database name the
schema name and the actual table name
and here are the columns for the table
for which we want to insert the values
and notice to retrieve the values from
the XML we are using the value function
and we are specifying the XPath here so
if you look at this XML data all this
event data is present in this event
instance root element right so here we
are saying in a go to event instance and
within that we have database name
retrieve that so within event instance
we have
database name and the name of the
database is sample DB so it's going to
retrieve that and if you're wondering
what is this numerical number one you
can think of this one as select top one
and transact sequel so this is going to
return the database name and we want
that to be converted to where pair of
250 that's the data type and then we are
going to you know insert that into this
table similarly we are retrieving the
object name that is the table name in
our case and then the event type login
name transact sequel command and the
date and time when that change happened
okay so let's go ahead and execute this
one now we already have that my table
right so I'm going to alter that at the
moment if you look at the name column
length it is 50 I'm going to change it
to 100 okay so we are changing the
length and then finally let's drop that
table okay so now within our table
changes audit table we should have two
entries let's see if that's what we have
there so select star from table changes
when we execute that notice that we have
two entries and the first entry is all
the table so we have the name of the
database the name of the table the event
type the login name associated and the
exact transact sequel the audit date and
time now you know this table is created
in sample DB database now let's go ahead
and create this table in a different
database and see if it's going to be
audited so we have this HR DB database
so I am going to create a table in that
database so let's execute that and now
if we come back and select the data we
should have another entry and look at
the database name here HR DB create
table and this is the exact transact
sequel date our command and here is the
date and time
so here we have that audit table changes
trigger thank you for listening and have
a great day