觸發 (Triggers)

1. 觸發(Triggers)

2. Trigger功能 • 一種與資料表緊密結合的預存程序 • 資料表有INSERT, DELETE, UPDATE事件發生時，設定的程序會自動執行 • 可以針對不同的異動事件設定不同的Trigger • 與限制(Constraint)比較 • Constraint可直接設定於資料表內，通常不需另外撰寫程式 • 只能進行較單純動作，包括Default, Primary key, Unique, Check及Foreign key • Trigger • 針對單一資料表所撰寫特殊預存程序 • 資料發生改變時可以處理複雜工作，例如完成成串cascading 工作

3. Trigger功能 • 檢查所做的更改是否允許 • 可同時檢查許多資料表，或使用IF…ELSE來執行更彈性的檢查 • 進行其他相關資料的更改動作 • 如訂單取消時，會自動刪除訂單相關資料，並將業務員奬金扣一半 • 更改原來所要進行的資料操作 • 利用INSTEAD OF可以將原來新增資料，將該資料做處理，而不存入資料表中

4. 建立 Triggers • 利用CREATE TRIGGER指令 • 必須指定資料表 ON • 指定那種異動執行該Trigger Use Northwind GO CREATE TRIGGER Empl_Delete ON Employees 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 insert employees (lastname,firstname) values ('ABCD','EFG') insert employees (lastname,firstname) values ('ABCD','EFG') insert employees (lastname,firstname) values ('ABCD','EFG') Delete employees where lastname =‘ABCD’

5. 修改及刪除 Triggers • 使用ALTER TRIGGER來修改Trigger定義 • 使用DROP TRIGGER來刪除Trigger定義 USE Northwind GO ALTER TRIGGER Empl_Delete ON Employees FOR DELETE, INSERT, UPDATE AS IF (SELECT COUNT(*) FROM Deleted) > 6 BEGIN RAISERROR( 'You cannot delete more than six employees at a time.', 16, 1) ROLLBACK TRANSACTION END

6. 檢視Trigger相關訊息 • sp_helptrigger ‘table_name’ [,’type’], 未指定type則全部列出 • Exec sp_helptrigger Employees, ‘delete’ • sp_help ‘trigger_name’可檢視trigger的擁有者及建立日期 • sp_helptext ‘trigger_name’可列出指定trigger的內容

7. 10523 2 19.00 5 0.2 Products ProductID UnitsInStock … … 1 2 3 4 15 106520 將新資料記錄到inserted資料表中 2 15 inserted 10523 2 19.00 5 0.2 INSERT Trigger 如何運作 INSERT 資料到有設定INSERT Trigger 的資料表 INSERT [Order Details] VALUES (10525, 2, 19.00, 5, 0.2) Order Details OrderID ProductID UnitPrice Quantity Discount UPDATE P SET UnitsInStock = (P.UnitsInStock – I.Quantity) FROM Products AS P INNER JOIN Inserted AS I ON P.ProductID = I.ProductID 執行Trigger動作 10522 10523 10524 10 41 7 31.00 9.65 30.00 79 24 0.20.15 0.0

8. 執行Trigger動作 Categories CategoryID CategoryName Description Picture Products 1 2 3 Beverages Condiments Confections Soft drinks, coffees… Sweet and savory … Desserts, candies, … 0x15…0x15… 0x15… ProductID Discontinued … … 1 2 3 4 0 000 2 1 USE Northwind CREATE TRIGGER Category_Delete ON Categories FOR DELETE AS UPDATE P SET Discontinued = 1 FROM Products AS P INNER JOIN deleted AS d ON P.CategoryID = d.CategoryID 4 Dairy Products Cheeses 0x15… 將刪除資料記錄到deleted資料表中 Deleted 4 Dairy Products Cheeses 0x15… DELETE Trigger 如何運作 DELETE資料到有設定DELETETrigger 的資料表 DELETE Categories WHERE CategoryID = 4 UPDATE P SET Discontinued = 1 FROM Products AS P INNER JOIN deleted AS d ON P.CategoryID = d.CategoryID

9. 執行Trigger動作 USE Northwind GO CREATE TRIGGER Employee_Update ON Employees FOR UPDATE AS IF UPDATE (EmployeeID) BEGIN TRANSACTION RAISERROR ('Transaction cannot be processed.\ ***** Employee ID number cannot be modified.', 10, 1) ROLLBACK TRANSACTION AS IF UPDATE (EmployeeID) BEGIN TRANSACTION RAISERROR ('Transaction cannot be processed.\ ***** Employee ID number cannot be modified.', 10, 1) ROLLBACK TRANSACTION Employees EmployeeID LastName FirstName Title HireDate 1 2 3 4 Davolio Barr Leverling Peacock Nancy Andrew Janet Margaret Sales Rep. R Sales Rep. Sales Rep. ~~~ ~~~ ~~~ ~~~ 2 2 Fuller Fuller Andrew Andrew Vice Pres. Vice Pres. ~~~ ~~~ Employees EmployeeID LastName FirstName Title HireDate 1 2 3 4 Davolio Barr Leverling Peacock Nancy Andrew Janet Margaret Sales Rep. R Sales Rep. Sales Rep. ~~~ ~~~ ~~~ ~~~ UPDATE Trigger 如何運作 UPDATE 資料到有設定UPDATETrigger 的資料表 UPDATE Employees SET EmployeeID = 17 WHERE EmployeeID = 2 UPDATE 指令會將原資料置於deleted資料表中，新資料置於 inserted 資料表中 inserted 17 Fuller Andrew Vice Pres. ~~~ deleted 2 Fuller Andrew Vice Pres. ~~~