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
