CREATE TRIGGER trig_InsteadOfEmployee
ON 員工
INSTEAD OF INSERT
AS
PRINT 'INSTEAD OF trig_InsteadOfEmployee 觸發程序已啟動'
PRINT '--- 您所加入的資料已被取消 ---'
GO

INSERT 員工 (姓名) VALUES ('陳阿輝')

DROP TRIGGER trig_InsteadOfEmployee

CREATE TRIGGER trig_InsteadOfEmployee
ON 員工
INSTEAD OF INSERT
AS
PRINT 'INSTEAD OF trig_InsteadOfEmployee 觸發程序已啟動'
DECLARE @maxEmplNo INT

SELECT @maxEmplNo = MAX(員工編號) + 1 FROM 員工

INSERT 員工 (員工編號, 姓名) VALUES (@maxEmplNo, '陳阿如')
GO

INSERT 員工 (姓名) VALUES ('陳阿輝')

CREATE TRIGGER trig_AfterInsertUpdateDelete
ON 訂單
AFTER INSERT, UPDATE, DELETE
AS
--判斷 INSERT，條件為：只存在 inserted，不存在 deleted 資料表
IF EXISTS( SELECT 員工編號 FROM inserted 
          WHERE 員工編號 NOT IN (SELECT 員工編號 FROM deleted) )
   BEGIN
PRINT '--- 已有訂單被 【新增】 ---'
      SELECT * 
FROM inserted 
WHERE 員工編號 NOT IN (SELECT 員工編號 FROM deleted)  
END

--判斷 UPDATE，條件為：同時存在 inserted 和 deleted 兩個資料表
IF EXISTS( SELECT 員工編號 FROM inserted 
          WHERE 員工編號 IN (SELECT 員工編號 FROM deleted) )
   BEGIN
PRINT '--- 已有訂單被 【更新】 ---'
SELECT inserted.*, deleted.* 
FROM inserted, deleted 
WHERE inserted.員工編號 = deleted.員工編號
END

--判斷 DELETE，條件為：只存在 deleted，不存在 inserted 資料表
IF EXISTS( SELECT 員工編號 FROM deleted 
          WHERE 員工編號 NOT IN (SELECT 員工編號 FROM inserted) )
   BEGIN
PRINT '--- 已有訂單被 【刪除】 ---'
SELECT * 
FROM deleted 
WHERE 員工編號 NOT IN ( SELECT 員工編號 FROM inserted )
END
GO

INSERT 訂單 (訂單編號, 員工編號, 客戶編號)
VALUES ('99010103', 7, 'C0016')

UPDATE 訂單
SET 出貨日期='2010-03-03', 預計到貨日期='2010-03-15'
WHERE 訂單編號 = '99010103'

DELETE 訂單
WHERE 訂單編號 = '99010103'

CREATE TRIGGER trig_InsteadOfDelete
ON 員工
INSTEAD OF DELETE
AS
PRINT '--- 啟動【INSTEAD OF DELETE】 TRIGGER ---'
PRINT '--- 僅將被刪除的人員狀態UPDATE為[已刪除] ---'
UPDATE 員工   -- 此處的UPDATE 會再觸發『AFTER UPDATE』觸發程序
SET 狀態 = '已刪除'
WHERE 員工編號 IN ( SELECT 員工編號 FROM deleted )
GO

CREATE TRIGGER trig_AfterUpdate
ON 員工
AFTER UPDATE
AS
PRINT '--- 啟動【AFTER UPDATE】 TRIGGER ---'
GO

DELETE 員工 WHERE 姓名='林美滿'

CREATE TRIGGER trig_InsteadOfProducts
ON 產品資料
INSTEAD OF UPDATE
AS
IF (not UPDATE(類別編號)) AND ( not UPDATE(安全存量))
   UPDATE 產品資料
   SET 供應商編號 = inserted.供應商編號 , 
       產品名稱 = inserted.產品名稱 , 
       建議單價 = inserted.建議單價 ,
       平均成本 = inserted.平均成本 ,
       庫存量 = inserted.庫存量
   FROM inserted
   WHERE 產品資料.產品編號 = inserted.產品編號

PRINT CASE
         WHEN UPDATE(類別編號) AND UPDATE(安全存量) 
               THEN '--- [類別編號]與[安全存量] 皆不可更改---'
         WHEN UPDATE(類別編號) 
               THEN '--- [類別編號] 不可更改---'
         WHEN UPDATE(安全存量) 
               THEN '--- [安全存量] 不可更改---'
         ELSE '--- 更新成功---'
      END   
GO

UPDATE 產品資料
SET 庫存量=庫存量 * 10
WHERE 產品編號 = 1

UPDATE 產品資料
SET 庫存量=庫存量 * 10, 類別編號 = 2, 安全存量=安全存量*10
WHERE 產品編號 = 1

CREATE TRIGGER trig_AfterCategory
ON 產品類別
AFTER INSERT, UPDATE, DELETE
AS
DECLARE @OpCode varchar(10)
--判斷INSERT
IF EXISTS(SELECT 類別編號 FROM inserted WHERE 類別編號 NOT IN (SELECT 類別編號 FROM deleted))
   SET @OpCode = 'INSERT'

--判斷UPDATE   
IF EXISTS(SELECT 類別編號 FROM inserted WHERE 類別編號 IN (SELECT 類別編號 FROM deleted))
   SET @OpCode = 'UPDATE'

--判斷DELETE   
IF EXISTS(SELECT 類別編號 FROM deleted WHERE 類別編號 NOT IN (SELECT 類別編號 FROM inserted))
   SET @OpCode = 'DELETE'

INSERT 產品類別log  ( 操作類型, 操作日期, 新類別編號, 新類別名稱, 
舊類別編號, 舊類別名稱)
  SELECT @OpCode, getdate(), inserted.*, deleted.* 
  FROM inserted full outer join deleted ON inserted.類別編號 = deleted.類別編號
   
GO

INSERT 產品類別 VALUES ( 9, '花茶')

UPDATE 產品類別
SET 類別名稱 = '山茶花'
WHERE 類別編號 = 9

DELETE 產品類別
WHERE 類別編號 = 9

CREATE TRIGGER trig_AfterCategoryLog
ON 產品類別log
AFTER UPDATE, DELETE
AS
PRINT '--- [產品類別log]資料表不可被UPDATE & DELETE ---'
ROLLBACK
GO

INSERT 產品類別log (操作類型, 操作日期)
VALUES ('INSERT', '2010/01/01')

DELETE 產品類別log WHERE 操作日期 = '2010/01/01'

CREATE TRIGGER trig_AfterCustomer
ON 客戶
AFTER INSERT
AS
PRINT '已經順利增加以下資料'
SELECT inserted.*, deleted.* 
FROM inserted full outer join deleted ON inserted.客戶編號 = deleted.客戶編號
GO

ALTER TRIGGER trig_AfterCustomer
ON 客戶
INSTEAD OF INSERT, UPDATE, DELETE
AS
PRINT '已經取消異動以下資料'
SELECT inserted.*, deleted.* 
FROM inserted full outer join deleted ON inserted.客戶編號 = deleted.客戶編號
GO

DROP TRIGGER trig_AfterCustomer

CREATE TRIGGER trig_AfterTrigger01
ON 員工
AFTER INSERT, UPDATE, DELETE
AS
PRINT '--- 啟動【AFTER】TRIGGER no. 1 ---'
GO

CREATE TRIGGER trig_AfterTrigger02
ON 員工
AFTER INSERT, UPDATE, DELETE
AS
PRINT '--- 啟動【AFTER】TRIGGER no. 2 ---'
GO

CREATE TRIGGER trig_AfterTrigger03
ON 員工
AFTER INSERT, UPDATE, DELETE
AS
PRINT '--- 啟動【AFTER】TRIGGER no. 3 ---'
GO

CREATE TRIGGER trig_InsteadOfTriggerAndProc
ON 員工
INSTEAD OF DELETE
AS
PRINT '--- 啟動【INSTEAD OF】TRIGGER ---'
EXEC usp_DontDelete '員工'
GO

CREATE PROC usp_DontDelete
@tablename varchar(30)
AS
PRINT '--- 啟動usp_DontDelete 預存程序---'
PRINT ' 【'+@tablename+'】資料表不可被刪除任何資料'
GO

sp_helptrigger '員工'

sp_helptrigger '員工', 'INSERT'

EXEC sp_help 'trig_InsteadOfTriggerAndProc'

EXEC sp_helptext 'trig_InsteadOfTriggerAndProc'

EXEC sp_depends 'trig_InsteadOfTriggerAndProc'

EXEC sp_settriggerorder 'trig_AfterTrigger01', 'First', 'INSERT'
EXEC sp_settriggerorder 'trig_AfterTrigger03', 'Last', 'INSERT'

EXEC sp_settriggerorder 'trig_AfterTrigger02', 'First', 'UPDATE'
EXEC sp_settriggerorder 'trig_AfterTrigger03', 'Last', 'UPDATE'

EXEC sp_settriggerorder 'trig_AfterTrigger02', 'First', 'DELETE'
EXEC sp_settriggerorder 'trig_AfterTrigger01', 'Last', 'DELETE'

INSERT 員工 (員工編號, 姓名)
VALUES (99,'陳阿輝')

UPDATE 員工
SET 任用日期 = GETDATE()
WHERE 姓名='陳阿輝'

DELETE 員工 WHERE 員工編號 = 99