??(Triggers)

1
??(Triggers)
2
Trigger??

• ???????????????
• ????INSERT, DELETE, UPDATE?????,??????????
• ????????????????Trigger
• ???(Constraint)??
• Constraint??????????,??????????
• ?????????,??Default, Primary key, Unique,
  Check?Foreign key
• Trigger
• ????????????????
• ???????????????,??????cascading ??

3
Trigger??

• ???????????
• ??????????,???IFELSE?????????
• ?????????????
• ??????,???????????,??????????
• ?????????????
• ??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) gt 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) gt 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
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
DELETE Trigger ????
DELETE ??????DELETE Trigger ????
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
UPDATE Trigger ????
UPDATE ??????UPDATE Trigger ????
UPDATE Employees SET EmployeeID 17 WHERE
EmployeeID 2
UPDATE ?????????deleted????,????? inserted ????
inserted
17
Fuller
Andrew
Vice Pres.

deleted
2
Fuller
Andrew
Vice Pres.