SQL Server 2005 Extended Triggers


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

 


Comments (4)

  1. Scott Allen says:

    This is a great feature. Especially for those production databases where you can prevent those accidental table drops!