SQL Server 2005 Extended Triggers

  • Article

Ok, I am impressed with everything in SQL Server 2005, especially extended triggers to capture DDL actions.  I was playing around with these today and I can see how this will be a nice feature, especially for audit trails.  I've included a simple script below.

-- create sample db
create database trigtestdb
go

use trigtestdb
go
create table tblextrig (eventid int identity, eventdata xml)
go

-- create event driven trigger
create trigger trig_ddl
   on database
   for create_table, drop_table, alter_table
as
   insert tblextrig values (eventdata())
go

-- do some ddl
create table ben (id int)
drop table ben
create table ben (id int)
go

-- check if DML events have been logged by trigger
select eventid, eventdata from tblextrig
go

-- clean up
use master
go

drop database trigtestdb