DDL Trigger (Know DDL commands on a Database)

DDL Trigger (Know DDL commands on a Database)

-- Table to Log DDL Level Events

CREATE TABLE databaseleveleventlog
(
id INT IDENTITY,
dbuser NVARCHAR(100),
systemuser NVARCHAR(100),
hostname NVARCHAR(100),
hostid NVARCHAR(100),
event NVARCHAR(500),
command NVARCHAR(max),
eventtime DATETIME,
);

go

-- Trigger Definition
CREATE TRIGGER tgr_databaseleveleventlog
ON DATABASE
FOR ddl_database_level_events
AS
INSERT databaseleveleventlog
(dbuser,
systemuser,
hostname,
hostid,
event,
command,
eventtime)
VALUES (CONVERT(NVARCHAR(100), CURRENT_USER),
SYSTEM_USER,
Host_name(),
Host_id(),
Eventdata().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
,
Eventdata().value('(/EVENT_INSTANCE/TSQLCommand)[1]',
'nvarchar(2000)'),
Getdate())

go


-- DROP TRIGGER tgr_DatabaseLevelEventLog ON DATABASE;
-- DROP TABLE DatabaseLevelEventLog;

Note: Once our task/goal is achieved, we need to drop this Trigger and Table, If not required any more.