DDL trigger (urgent ) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

DDL trigger (urgent )

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2007-03-06 : 10:01:45

Posted - 03/05/2007 : 01:58:00
--------------------------------------------------------------------------------

Hi pals,

I need one trigger for Auditing purpose.Currently we are using SQL Server 2005.
I have gone thru some articles on DDL trigggers but iam not able to get good info.

Frns, Need a trigger which can track all ddl's on all tables in the current database in an
audit table. It should include dropping tables,altering tables ... I should'nt allow
both these activities. The action performed must be tracked in the following table.

create table audit_ddl
(
login_user varchar(20),
login_time datetime,
table_name varchar(50),
action_performed varchar(200) -- Entire ALTER/DROP SQL stmt the user issued issued must be tracked here.
)

Can any senior DBA can help me out.

Thanks & Regards,
franky

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-06 : 10:59:15

Here's an example from Books Online adapted for your audit table. I changed your table a little because the definition you have will not hold everything you want correctly. Also, note that DDL can be issued against more than just tables, so you may want to change the name of your column to object_name. This will record everything in the audit table.

create table audit_ddl
(
login_user varchar(20),
login_time datetime,
table_name nvarchar(100),
action_performed nvarchar(2000) 
);
GO
CREATE TRIGGER log 
ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT audit_ddl 
   (login_user, login_time, table_name, action_performed) 
   VALUES 
   (CONVERT(nvarchar(100), CURRENT_USER), 
    GETDATE(), 
    @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'), 
    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;

Here's how you can prevent certain operations and log them too:

CREATE TRIGGER safety 
ON DATABASE 
FOR ALTER_TABLE, DROP_TABLE
AS 
    PRINT 'ALTER or DROP TABLE Issued.'
    DECLARE @data XML
SET @data = EVENTDATA()
INSERT audit_ddl 
   (login_user, login_time, table_name, action_performed) 
   VALUES 
   (CONVERT(nvarchar(100), CURRENT_USER), 
    GETDATE(), 
    @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'), 
    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') )
   RAISERROR ('Tables cannot be altered or dropped in this database.', 16, 1) 
   ROLLBACK
;