Thursday, September 15, 2011

DDL Trigger To Capture Table Creation Details

We can create Triggers for all database changes like table creation, table updation, drop table, schema change, and etc.

Here follow the steps for creating trigger for all create table statements. 

Step 1: First create one table to store the audit details. 
USE Adventureworks
Go
CREATE TABLE dbo.CREATE_TABLE_LOG (
eventTime datetime
, eventOwner nvarchar(100)
, eventTSQL nvarchar(3000)
)
Go 

Step 2: Now create the DDL Trigger for all create table statements
USE Adventureworks
GO
CREATE TRIGGER DDLTrigger_CreateTable ON DATABASE FOR create_table
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO CREATE_TABLE_LOG
VALUES (GETDATE()
, CURRENT_USER
, @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(1000)'))
Go 

Please see the below picture for output.

Designing DDL Triggers, Using DDL Triggers in SQL Server 2005 to Capture Schema change, SQL Server 2005 DDL Trigger Workbench, DDL Trigger in SQL-Server 2005 for Create table, alter table, drop table, SQL Server DDL Triggers to Track All Database Changes, SQL Server DDL Trigger Tips, Understanding DDL Triggers in SQL Server 2012, Understanding DDL Triggers in SQL Server 2008, DDL Trigger and DDL Auditing, ddl trigger sql server 2005, ddl trigger sql server 2008, create ddl trigger sql server 2012, ddl trigger on create_database does not fire, Server and Database Level DDL Triggers, Using DDL Triggers to Manage SQL Server, Anyway to create a SQL Server DDL trigger, Auditing Schema Changes using DDL Triggers, Understanding DDL Triggers, DISABLE TRIGGER,

0 comments:

Post a Comment