Dunk

-- 資料來源:德瑞克老師的上課筆記+自行修改
-- DDL Triggers in SQL Server 2005
-- http://www.sql-server-performance.com/2007/ddl-triggers/
-- CREATE TRIGGER (Transact-SQL)
-- http://technet.microsoft.com/zh-tw/library/ms189799.aspx
-- 使用 sp_executesql
-- http://msdn.microsoft.com/zh-tw/library/ms175170(v=sql.105).aspx

-- 針對DDL觸發程序被觸發時的 XML 資料進行記錄
-- 先建立要儲存的表格
USE master
GO

IF EXISTS
(SELECT name
 FROM
	 sys.tables
 WHERE
	 name = 'EvenTrack') DROP TABLE dbo.EvenTrack
GO
	   
CREATE TABLE EvenTrack
(
	[dbname] sysname,
	[LoginName] sysname,
	[TSQLCommand]nvarchar(max),
	[PostTime] datetime,
)

-- 查詢log表格是否建立
SELECT *
FROM
	master.dbo.EvenTrack;
	
-- 針對log table建立trigger防止修改及刪除
IF EXISTS
(SELECT name
 FROM
	 sys.triggers
 WHERE
	 name = 'TR_EvenTrack_Modify') DROP TRIGGER TR_EvenTrack_Modify;
GO
	
CREATE TRIGGER TR_EvenTrack_Modify
on dbo.EvenTrack
after delete,update
as
BEGIN
SELECT N'此表格禁止修改'
ROLLBACK TRANSACTION
END

-- 檢查 Trigger是否建立
SELECT *
FROM
	sys.triggers
WHERE
	name = 'TR_EvenTrack_Modify';


-- step 2 針對要監控的資料庫設定DDL Trigger
-- 抓取所有使用者資料庫
-- DECLARE @SqlString nvarchar(200);
-- SET @SqlString = 'use ? if( DB_NAME() not in (''master'', ''model'', ''msdb'', ''tempdb'')) print''?''';
-- EXEC sp_msforeachdb @SqlString;
USE adstatus
GO

IF  EXISTS
(SELECT *
 FROM
	 sys.triggers
 WHERE
	 parent_class_desc = 'DATABASE'
	 AND name = N'TR_DATABASE_DDL_TRACKING') DROP TRIGGER [TR_DATABASE_DDL_TRACKING] ON DATABASE
GO	
CREATE TRIGGER TR_DATABASE_DDL_TRACKING
	ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
 AS
  DECLARE @PostTime datetime = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime');
  DECLARE @LoginName sysname = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','sysname');
  DECLARE @TSQLCommand nvarchar(max) = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)');
INSERT master.dbo.EvenTrack
SELECT db_name()
	 , @LoginName
	 , @TSQLCommand
	 , @PostTime;	
GO

-- 檢查trigger是否建立
SELECT *
FROM
	sys.triggers
WHERE
	parent_class_desc = 'DATABASE'
	AND name = N'TR_DATABASE_DDL_TRACKING'

-- 執行 DDL 命令
CREATE TABLE TestTable (TestTableID int);
GO
ALTER TABLE TestTable add  [TestName]nvarchar(10) not null
GO
DROP TABLE TestTable;
GO

-- 檢查DDL是否有寫入table
SELECT *
FROM
	master.dbo.EvenTrack

-- 試著修改或者刪除log資料都會發生錯誤
DELETE
FROM
	master.dbo.EvenTrack
UPDATE master.dbo.EvenTrack
SET
	PostTime = getdate()
GO

-- 如果之後要建立的資料庫都要套用DDL TRIGGER
-- 可以將TRIGGER建在model資料庫
-- 以下共有三個TRIGGER
-- TR_DATABASE_DDL_TRACKING:針對DDL觸發程序被觸發時的 XML 資料進行記錄
-- TR_DDL_ProcNamingConvention:禁止建立以 sp_ 為前置詞的預存程序
-- TR_DDL_CREATE_TABLE_PK:資料表必須要有主索引鍵
USE model
GO

-- TR_DATABASE_DDL_TRACKING:針對DDL觸發程序被觸發時的 XML 資料進行記錄
IF  EXISTS
(SELECT *
 FROM
	 sys.triggers
 WHERE
	 parent_class_desc = 'DATABASE'
	 AND name = N'TR_DATABASE_DDL_TRACKING') DROP TRIGGER [TR_DATABASE_DDL_TRACKING] ON DATABASE
GO	
CREATE TRIGGER TR_DATABASE_DDL_TRACKING
	ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
 AS
  DECLARE @PostTime datetime = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime');
  DECLARE @LoginName sysname = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','sysname');
  DECLARE @TSQLCommand nvarchar(max) = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)');
INSERT master.dbo.EvenTrack
SELECT db_name()
	 , @LoginName
	 , @TSQLCommand
	 , @PostTime;	
GO

-- TR_DDL_ProcNamingConvention:禁止建立以 sp_ 為前置詞的預存程序
IF  EXISTS
(SELECT *
 FROM
	 sys.triggers
 WHERE
	 parent_class_desc = 'DATABASE'
	 AND name = N'TR_DDL_ProcNamingConvention') DROP TRIGGER [TR_DDL_ProcNamingConvention] ON DATABASE
GO
CREATE TRIGGER TR_DDL_ProcNamingConvention
ON DATABASE
	FOR CREATE_PROCEDURE
AS 
BEGIN
SET NOCOUNT ON;

  DECLARE @EventData xml;
  DECLARE @ObjectName sysname;
SET @EventData = eventdata();
SET @ObjectName = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');
	
  IF @ObjectName LIKE 'sp[_]%'
  BEGIN
    PRINT N'預存程序的前置詞，不得以 sp_ 開頭';
    ROLLBACK TRAN;
  END;
END;
GO

-- TR_DDL_CREATE_TABLE_PK:資料表必須要有主索引鍵
IF  EXISTS
(SELECT *
 FROM
	 sys.triggers
 WHERE
	 parent_class_desc = 'DATABASE'
	 AND name = N'TR_DDL_CREATE_TABLE_PK') DROP TRIGGER [TR_DDL_CREATE_TABLE_PK] ON DATABASE
GO
CREATE TRIGGER TR_DDL_CREATE_TABLE_PK
ON DATABASE
	FOR CREATE_TABLE, ALTER_TABLE 
AS BEGIN
SET NOCOUNT ON;

  DECLARE @EventData xml;
  DECLARE @SchemaName sysname;
  DECLARE @ObjectName sysname;
  DECLARE @FullName nvarchar(max);
SET @EventData = eventdata();
SET @SchemaName = @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @ObjectName = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') ;
SET @FullName = quotename(@SchemaName) + '.' + quotename(@ObjectName);

  IF OBJECTPROPERTY(OBJECT_ID(@FullName),'TableHasPrimaryKey') <> 1
  BEGIN
    PRINT N'資料表必須要有：主索引鍵';
    ROLLBACK TRAN;
  END;
END;
GO

-- 查詢TRIGGER是否建立
SELECT *
FROM
	sys.triggers
WHERE
	parent_class_desc = 'DATABASE'

-- 建立新的資料庫
USE master
GO
IF  EXISTS
(SELECT name
 FROM
	 sys.databases
 WHERE
	 name = N'TESTDB1')
BEGIN
	ALTER DATABASE [TESTDB] 
		SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [TESTDB]
END
GO
CREATE DATABASE TESTDB
GO
-- 查詢是否有內建的TRIGGER
SELECT *
FROM
	TESTDB.sys.triggers
WHERE
	parent_class_desc = 'DATABASE'

-- 執行 DDL 命令
USE TESTDB
GO

CREATE TABLE TestTable0 (TestTableID int);
GO
CREATE TABLE TestTablePK (TestTableID int primary key);
GO
CREATE PROCEDURE sp_foo
as
return 'foo test!'
GO
DROP TABLE TestTablePK;
GO

-- 檢查DDL是否有寫入table
SELECT *
FROM
	master.dbo.EvenTrack

-- 刪除範例資料庫
USE master
GO
IF  EXISTS
(SELECT name
 FROM
	 sys.databases
 WHERE
	 name = N'TESTDB')
BEGIN
	ALTER DATABASE [TESTDB] 
		SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [TESTDB]
END
GO