jerry的成長之路

使用Trigger要考慮到什麼

1. Trigger是一個被動回應的機制，Constraints則為較主動的機制，當兩者同時瘥在的時候，Constraints會先被檢查
2. 一個table可以有許多個Triggers回應不同的動作
3. Trigger中指定的動作需要有足夠的權限
4. 在view和Temporary Tables裡不可以建立AFTER Trigger

建立Trigger

1. 必須要有足夠的權限
2. 不可以包含特定的敘述句
   ex.   create database   ,   reconfigure之類的命令

   USE Northwind
   GO
   
   --例1 建立 Trigger
   --產生測試資料表
   select * into employees2 from Employees
   
   --建立 Trigger 當 刪除 employees 員工超過一筆以上時, 產生錯誤訊息
   Use Northwind
   GO
   CREATE TRIGGER Empl_Delete ON Employees2
   FOR DELETE 
   AS
   IF (SELECT COUNT(*) FROM Deleted) > 1
   BEGIN
      RAISERROR('You cannot delete more than one employee at a time.', 16, 1)
      ROLLBACK TRANSACTION
   END
   
   --測試
   delete from employees2 where employeeid >= 1      -- not OK
   delete from employees2 where employeeid  = 1      -- OK

   
    

修改和刪除Triggers

修改Trigger

1. 改編Trigger的內容不用先刪除原本的
2. 可以用來將Trigger暫時disable或enable
   ALTER TABLE tablename ENABLE TRIGGER{ALL|triggername}

3. USE Northwind
   
   ALTER   TRIGGER   Empl_Delete   ON   Employees2
   FOR  DELETE 
   AS
   IF (SELECT COUNT(*) FROM Deleted) > 6
   BEGIN
      RAISERROR('You cannot delete more than six employees at a time.', 16, 1)
      ROLLBACK 

   
    

刪除Trigger

1. 使用DROP TRIGGER triggername

Tigger的運作方式

• INSERT TRIGGER
• DELETE TRIGGER
• UPDATE TRIGGER
• INSTEAD OF TRIGGER
• (NESTED) TRIGGERS
• (RECURSIVE) TRIGGERS