---------------------------------------------------------------------
-- DDL Triggers
---------------------------------------------------------------------

---------------------------------------------------------------------
-- Database Level Triggers
---------------------------------------------------------------------

-- Create testdb database
USE master;
GO
IF DB_ID('testdb') IS NOT NULL
  DROP DATABASE testdb;
GO
CREATE DATABASE testdb;
GO
USE testdb;
GO

-- Listing 8-10: Creation Script for trg_create_table_with_pk Trigger
CREATE TRIGGER trg_create_table_with_pk ON DATABASE FOR CREATE_TABLE
AS

DECLARE @eventdata AS XML, @objectname AS NVARCHAR(257),
  @msg AS NVARCHAR(500);

SET @eventdata = eventdata();
SET @objectname = 
  + QUOTENAME(@eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'))
  + N'.' + 
  QUOTENAME(@eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'));

IF COALESCE(
     OBJECTPROPERTY(OBJECT_ID(@objectname), 'TableHasPrimaryKey'),
     0) = 0
BEGIN
  SET @msg = N'Table ' + @objectname + ' does not contain a primary key.'
    + CHAR(10) + N'Table creation rolled back.';
  RAISERROR(@msg, 16, 1);
  ROLLBACK;
  RETURN;
END
GO

-- Test trigger trg_create_table_with_pk
CREATE TABLE dbo.T(col1 INT NOT NULL);
GO
CREATE TABLE dbo.T(col1 INT NOT NULL PRIMARY KEY);
GO

-- Listing 8-11: Creation Script for AuditDDLEvents Table and trg_audit_ddl_events Trigger
IF OBJECT_ID('dbo.AuditDDLEvents') IS NOT NULL
  DROP TABLE dbo.AuditDDLEvents;
GO

CREATE TABLE dbo.AuditDDLEvents
(
  lsn              INT      NOT NULL IDENTITY,
  posttime         DATETIME NOT NULL,
  eventtype        sysname  NOT NULL,
  loginname        sysname  NOT NULL,
  schemaname       sysname  NOT NULL,
  objectname       sysname  NOT NULL,
  targetobjectname sysname  NULL,
  eventdata        XML      NOT NULL,
  CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(lsn)
);
GO

CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS

DECLARE @eventdata AS XML;
SET @eventdata = eventdata();

INSERT INTO dbo.AuditDDLEvents(
  posttime, eventtype, loginname, schemaname, 
  objectname, targetobjectname, eventdata)
  VALUES(
    @eventdata.value('(/EVENT_INSTANCE/PostTime)[1]',         'VARCHAR(23)'),
    @eventdata.value('(/EVENT_INSTANCE/EventType)[1]',        'sysname'),
    @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]',        'sysname'),
    @eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]',       'sysname'),
    @eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]',       'sysname'),
    @eventdata.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'sysname'),
    @eventdata);
GO

-- Test trigger trg_audit_ddl_events
CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY);
ALTER TABLE dbo.T1 ADD col2 INT NULL;
ALTER TABLE dbo.T1 ALTER COLUMN col2 INT NOT NULL;
CREATE NONCLUSTERED INDEX idx1 ON dbo.T1(col2);
GO

SELECT * FROM dbo.AuditDDLEvents;
GO

-- Who changed T1 in the last 24 hours and how?
SELECT posttime, eventtype, loginname,
  eventdata.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)')
  AS tsqlcommand
FROM dbo.AuditDDLEvents
WHERE schemaname = N'dbo' AND N'T1' IN(objectname, targetobjectname)
  AND posttime > GETDATE() - 1
ORDER BY posttime;
GO

-- Cleanup
USE master;
GO
IF DB_ID('testdb') IS NOT NULL
  DROP DATABASE testdb;
GO

---------------------------------------------------------------------
-- Server Level Triggers
---------------------------------------------------------------------

-- Listing 8-12: Creation Script for AuditDDLLogins Table and trg_audit_ddl_logins Trigger
USE master;
GO
IF OBJECT_ID('dbo.AuditDDLLogins') IS NOT NULL
  DROP TABLE dbo.AuditDDLLogins;
GO

CREATE TABLE dbo.AuditDDLLogins
(
  lsn              INT      NOT NULL IDENTITY,
  posttime         DATETIME NOT NULL,
  eventtype        sysname  NOT NULL,
  loginname        sysname  NOT NULL,
  objectname       sysname  NOT NULL,
  logintype        sysname  NOT NULL,
  eventdata        XML      NOT NULL,
  CONSTRAINT PK_AuditDDLLogins PRIMARY KEY(lsn)
);
GO

CREATE TRIGGER trg_audit_ddl_logins ON ALL SERVER
  FOR DDL_LOGIN_EVENTS
AS
DECLARE @eventdata AS XML;
SET @eventdata = eventdata();

INSERT INTO master.dbo.AuditDDLLogins(
  posttime, eventtype, loginname,
  objectname, logintype, eventdata)
  VALUES(
    @eventdata.value('(/EVENT_INSTANCE/PostTime)[1]',   'VARCHAR(23)'),
    @eventdata.value('(/EVENT_INSTANCE/EventType)[1]',  'sysname'),
    @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]',  'sysname'),
    @eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),
    @eventdata.value('(/EVENT_INSTANCE/LoginType)[1]',  'sysname'),
    @eventdata);
GO

-- Test trigger audit_ddl_logins
CREATE LOGIN login1 WITH PASSWORD = '123';
ALTER LOGIN login1 WITH PASSWORD = 'xyz';
DROP LOGIN login1;
GO

SELECT * FROM master.dbo.AuditDDLLogins;
GO

-- Cleanup
DROP TRIGGER trg_audit_ddl_logins ON ALL SERVER;
GO
IF OBJECT_ID('dbo.AuditDDLLogins') IS NOT NULL
  DROP TABLE dbo.AuditDDLLogins;
GO