Dunk

-- 資料來源:德瑞克老師的上課筆記+自行修改
-- 使用 Trigger 紀錄資料表的新增、修改、刪除的行為 
-- http://www.dotblogs.com.tw/jameswu/archive/2009/07/23/9643.aspx
USE ProductDB
GO 

-- 從正式表格隨機選取員工編號後新增測試資料
-- 建立測試表格
IF  EXISTS
(SELECT *
 FROM
	 sys.objects
 WHERE
	 object_id = object_id(N'[dbo].[EMPLOYEE_TEST_DATA]')
	 AND type IN (N'U')) DROP TABLE [dbo].[EMPLOYEE_TEST_DATA]
GO
CREATE TABLE [dbo].[EMPLOYEE_TEST_DATA]
(
[STAFF] [nvarchar](6)PRIMARY KEY,
[NOTE][nvarchar](30)DEFAULT ('這是林大貓的測試資料'),
[CEATETIME][datetime] DEFAULT getdate()
);
GO

IF  EXISTS
(SELECT *
 FROM
	 sys.objects
 WHERE
	 object_id = object_id(N'[dbo].[EMPLOYEE_TEST_DATA_LOG]')
	 AND type IN (N'U')) DROP TABLE [dbo].[EMPLOYEE_TEST_DATA_LOG]
GO
CREATE TABLE [dbo].[EMPLOYEE_TEST_DATA_LOG]
(
	[ACTION][nvarchar](10),
	[STAFF] [nvarchar](6),
	[NOTEBEFORE][nvarchar](30),
	[NOTEAFTER][nvarchar](30),	
	[RECORDDATE][datetime] DEFAULT getdate()
);
GO

-- 檢查測試表格
SELECT *
FROM
	dbo.EMPLOYEE_TEST_DATA
SELECT *
FROM
	dbo.EMPLOYEE_TEST_DATA_LOG

-- 隨機選取十筆資料當作測試資料
INSERT [dbo].[EMPLOYEE_TEST_DATA] (STAFF)
SELECT TOP 10 STAFF
FROM
	dbo.EMPLOYEE
ORDER BY
	newid()
GO

-- 寫入固定資料
INSERT [dbo].[EMPLOYEE_TEST_DATA] (STAFF)
SELECT '123456'

-- 查詢測試表格資料
SELECT *
FROM
	dbo.EMPLOYEE_TEST_DATA

-- trigger(AFTER)
-- 功能：當刪除多筆資料時會發出訊息並且Rollback
IF exists
(SELECT name
 FROM
	 sys.triggers
 WHERE
	 name = 'tri_employee_test_data_del') DROP TRIGGER dbo.tri_employee_test_data_del
GO	 
create trigger tri_employee_test_data_del
on dbo.EMPLOYEE_TEST_DATA
after delete	
as
declare @rowcount int
SELECT @rowcount = count(*)
FROM
	DELETED;
IF(@rowcount >1)
	begin
SELECT '一次只能刪除一筆資料!您的請求將退回!';
		ROLLBACK TRANSACTION
	end
GO

-- 功能：每次僅能更新一筆資料列
IF exists
(SELECT name
 FROM
	 sys.triggers
 WHERE
	 name = 'tri_employee_test_data_update') DROP TRIGGER dbo.tri_employee_test_data_update
GO	
CREATE TRIGGER tri_employee_test_data_update 
	ON dbo.EMPLOYEE_TEST_DATA
	AFTER UPDATE
AS
IF @@ROWCOUNT > 1
BEGIN
   RAISERROR(N'每次僅能更新一筆資料',10, 1)
   ROLLBACK TRANSACTION
END
GO

-- 功能：當修改資料時同時寫到log
IF exists
(SELECT name
 FROM
	 sys.triggers
 WHERE
	 name = 'tri_employee_test_data_log') DROP TRIGGER dbo.tri_employee_test_data_log
GO	 
create trigger tri_employee_test_data_log
on dbo.EMPLOYEE_TEST_DATA
after insert,update,delete
as
declare @type nvarchar(10);
IF EXISTS
(SELECT 1
 FROM
	 inserted) AND NOT EXISTS (SELECT 1
							   FROM
								   deleted) 
BEGIN
SELECT @type = N'新增';
INSERT INTO dbo.EMPLOYEE_TEST_DATA_LOG ([ACTION]
									  , [STAFF]
									  , [NOTEBEFORE]
									  , [NOTEAFTER])
SELECT @type
	 , STAFF
	 , NULL
	 , NOTE
FROM
	INSERTED;
END								   
IF EXISTS
(SELECT 1
 FROM
	 inserted) AND EXISTS (SELECT 1
						   FROM
							   deleted) 
BEGIN
SELECT @type = N'修改';
INSERT INTO dbo.EMPLOYEE_TEST_DATA_LOG ([ACTION]
									  , [STAFF]
									  , [NOTEBEFORE]
									  , [NOTEAFTER])
SELECT @type
	 , a.STAFF
	 , b.NOTE
	 , a.NOTE
FROM
	INSERTED a
	INNER JOIN DELETED b
		ON a.STAFF = b.STAFF;
END							   
IF NOT EXISTS
(SELECT 1
 FROM
	 inserted) AND EXISTS (SELECT 1
						   FROM
							   deleted) 
BEGIN
SELECT @type = N'刪除';
INSERT INTO dbo.EMPLOYEE_TEST_DATA_LOG ([ACTION]
									  , [STAFF]
									  , [NOTEBEFORE]
									  , [NOTEAFTER])
SELECT @type
	 , STAFF
	 , NOTE
	 , NULL
FROM
	DELETED 	
END				   
GO

-- trigger(INSTEAD of)
-- 禁止使用者修改刪除log
IF exists
(SELECT name
 FROM
	 sys.triggers
 WHERE
	 name = 'tri_employee_test_data_log_DisableModify') DROP TRIGGER dbo.tri_employee_test_data_log_DisableModify
GO	 
create trigger tri_employee_test_data_log_DisableModify
on dbo.EMPLOYEE_TEST_DATA_LOG
instead of update,delete
as
 RAISERROR(N'不可以對log紀錄進行修改刪除!',10, 1); 
GO


-- 檢查tringger是否建立
SELECT object_name(t.parent_id) N'資料表'
	 , t.name N'觸發程序名稱'
	 , parent_class_desc N'觸發程序父類別的描述'
	 , t.type_desc N'物件類型的描述'
	 , tEV.type_desc '引發觸發程序的每個事件'
	 , is_instead_of_trigger N'是否為 INSTEAD OF 觸發程序'
FROM
	sys.triggers t
	INNER JOIN sys.trigger_events tEV
		ON t.object_id = tEV.object_id
GO

-- 測試trigger(會出現失敗訊息)
DELETE
FROM
	dbo.EMPLOYEE_TEST_DATA

UPDATE dbo.EMPLOYEE_TEST_DATA
SET
	NOTE = N'測試資料'
GO

-- 測試trigger(會出現成功訊息)
INSERT INTO dbo.EMPLOYEE_TEST_DATA (STAFF)
VALUES
	('999999');

UPDATE dbo.EMPLOYEE_TEST_DATA
SET
	NOTE = N'可修改的測試資料'
WHERE
	STAFF = '999999'

DELETE
FROM
	dbo.EMPLOYEE_TEST_DATA
WHERE
	STAFF = '999999'
GO

-- 測試trigger(會出現失敗訊息)(INSTEAD OF)
DELETE
FROM
	dbo.EMPLOYEE_TEST_DATA_LOG

UPDATE dbo.EMPLOYEE_TEST_DATA_LOG
SET
	NOTEAFTER = NULL
GO

-- 查詢測試表格資料
SELECT *
FROM
	dbo.EMPLOYEE_TEST_DATA
SELECT *
FROM
	dbo.EMPLOYEE_TEST_DATA_LOG

-- 刪除測試資料
DROP TABLE dbo.EMPLOYEE_TEST_DATA
DROP TABLE dbo.EMPLOYEE_TEST_DATA_LOG
GO