Freigeben über

SQL Server 2005 Extended Triggers

  • Artikel

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

  • Anonymous
    October 20, 2004
    This is a great feature. Especially for those production databases where you can prevent those accidental table drops!
  • Anonymous
    June 01, 2009
    PingBack from http://paidsurveyshub.info/story.php?id=76613