第 22 课 PL/SQL5

1. 第22课PL/SQL5

2. 教学目标 • Trigger • CREATE Trigger • MANAGE Trigger • 使用Trigger

3. Overview of Triggers • 触发器类似于函数和过程，它们都是具有声明部分、执行部分和异常处理部分的命名P L / S Q L 块 • 触发器是一个数据库对象，在数据库中以独立对象的身份存储 • 触发器是在事件发生时隐式地运行的，并且触发器不能接收参数

4. Overview of Triggers • 触发事件可以是对数据库表的D M L（I N S E RT 、U P D AT E 或D E L E T E ）操作或某种视图的操作( Vi e w )。 • Database triggers execute implicitly when an DML statement (triggering statement) is issued against the associated table, no matter which user is connected • A trigger can be either a database trigger or an application trigger.

5. Designing Triggers须注意 • Perform related actions • Use triggers for global operations

6. Database Trigger: Example Application SQL> INSERT INTO EMP 2 . . .; EMP table CHECK_SAL trigger EMPNO 7838 7698 7369 7788 ENAME KING BLAKE SMITH SCOTT JOB PRESIDENT MANAGER CLERK ANALYST SAL 5000 2850 800 3000

7. Creating Triggers语句的组成 CREATE [OR REPLACE ] TRIGGER trigger__name {BEFORE |AFTER | INSTEAD OF}event ON {table_or_view_name} [FOR EACH ROW [WHEN condition ]] trigger_body

8. Creating Triggers语句的组成 • Trigger timing: BEFORE or AFTER • Triggering event: INSERT or UPDATE or DELETE • Table name: On table名 • Trigger type: Row or statement

9. Creating Triggers语句的组成 • When clause: Restricting condition. • 如果在W H E N 子句中指定trigger condition的话，则首先对该条件求值。触发器主体只有在该条件为真值时才运行。 • W H E N 子句只适用于行级触发器。如果使用该子句的话，触发器体将只对满足由W H E N 子句说明的条件的行执行。 • Trigger body: DECLARE • BEGIN • END;

10. 语句的组成 • Trigger Timing: When should the trigger fire? • BEFORE: The code in the trigger body will execute before the triggering DML event. • AFTER: The code in the trigger body will execute after the triggering DML event.

11. 语句的组成 • Trigger Timing: When should the trigger fire? • INSTEAD OF: The code in the trigger body will execute instead of the the triggering statement. 可以定义在VIEW上.

12. 语句的组成 • Triggering Event: • What DML operation will cause the trigger to execute? • INSERT • UPDATE • DELETE • Any combination of the above：之间用or 连接 • INSERT OR UPDATE:在执行INSERT或UPDATE时都会触发此TRIGGER.

13. 语句的组成 • Trigger Type: • How many times should the trigger body execute when the triggering event takes place? • Statement: The trigger body executes once for the triggering event. • This is the default. • Row: The trigger body executes once for each row affected by the triggering event. • 用FOR EACH ROW 子句指定。

14. 语句的组成 • Trigger Body: • What action should the trigger perform? • The trigger body is defined with an anonymous PL/SQL block. • [DECLARE] • BEGIN • [EXCEPTION] • END;

15. Example SQL> UPDATE emp 2 SET sal = sal * 1.1 3 WHERE deptno = 30; Statement and Row Triggers

16. BEFORE statement trigger BEFORE row trigger AFTER row trigger BEFORE row trigger AFTER row trigger BEFORE row trigger AFTER row trigger AFTER statement trigger Firing Sequence of Database Triggers on Multiple Rows EMPNO 7839 7698 7788 ENAME KING BLAKE SMITH DEPTNO 30 30 30

17. 触发器的触发顺序 • 触发器是在D M L 语句运行时激发的。下面是执行D M L 语句的算法步骤： • 1 )如果有语句之前级触发器的话，先运行该触发器。 • 2 )对于受语句影响每一行： • a. 如果有行之前级触发器的话，运行该触发器。 • b. 执行该语句本身。 • c. 如果有行之后级触发器的话，运行该触发器。 • 3 )如果有语句之后级触发器的话，运行该触发器。

18. Before Statement Trigger: Example • EXAMPLE: • trigger_test.txt

19. Creating a Row Trigger CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON table_name [REFERENCING OLD AS old | NEW AS new] FOR EACH ROW [WHEN condition] PL/SQL block;

20. After Row Trigger: Example SQL>CREATE OR REPLACE TRIGGER audit_emp 2 AFTER DELETE OR INSERT OR UPDATE ON emp 3 FOR EACH ROW 4 BEGIN 5 IF DELETING THEN 6 UPDATE audit_table SET del = del + 1 7 WHERE user_name = user AND table_name = 'EMP' 8 AND column_name IS NULL; 9 ELSIF INSERTING THEN 10 UPDATE audit_table SET ins = ins + 1 11 WHERE user_name = user AND table_name = 'EMP' 12 AND column_name IS NULL; 13 ELSIF UPDATING ('SAL') THEN 14 UPDATE audit_table SET upd = upd + 1 15 WHERE user_name = user AND table_name = 'EMP' 16 AND column_name = 'SAL'; 17 ELSE /* The data manipulation operation is a general UPDATE. */ 18 UPDATE audit_table SET upd = upd + 1 19 WHERE user_name = user AND table_name = 'EMP' 20 AND column_name IS NULL; 21 END IF; 22 END; 23 /

21. 行级触发器的相关标识 • 行级触发器是按触发语句所处理的行激发的。在触发器内部，我们可以访问正在处理中的行的数据。 • 这种访问是通过两个相关的标识符： • : o l d 和: n e w 实现的。 • ：new.field 只有在该字段位于触发表中时才是合法的。

22. 行级触发器的相关标识 • 触发语句 • I N S E RT • (: o l d):无定义,所有字段为空N U L L • (: n e w):该语句结束时将插入的值 • U P D AT E • (: o l d):更新前行的原始值 • (: n e w):该语句结束时将更新的值 • D E L E T E • (: o l d):行删除前的原始值 • (: n e w):无定义-所有字段为空N U L L

23. 行级触发器的相关标识 • 引用子句 • 我们可以根据需要使用子句R E F E R E N C I N G 来为 :new 和 :old 指定一个不同的名称。 • 该子句可以在触发事件之后，W H E N 子句之前使用， • 其语法如下： • REFERENCING [OLD AS :old_name] [NEW AS :new_name] • 在触发器体内，我们可以使用: old_name 和: new_name 来代替: old 和: new 。

24. 行级触发器的相关标识 • EXAMPLE: • trigger_row.txt

25. Differentiating Between Triggers and Stored Procedures Triggers Use CREATE TRIGGER Data dictionary contains source and p-code Implicitly invoked COMMIT, SAVEPOINT, ROLLBACK not allowed Procedure Use CREATE PROCEDURE Data dictionary contains source and p-code Explicitly invoked COMMIT, SAVEPOINT, ROLLBACK allowed

26. Managing Triggers Disable or Re-enable a database trigger ALTER TRIGGER trigger_nameDISABLE | ENABLE Disable or Re-enable all triggers for a table ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS Recompile a trigger for a table ALTER TRIGGER trigger_name COMPILE

27. Removing Triggers • To remove a trigger from the database, use the DROP TRIGGER syntax: DROP TRIGGER trigger_name

28. Triggers管理的方法 • Rule 1: • Do not change data in the primary key, foreign key, or unique key columns of a constraining table（约束表）. • Rule 2: • Do not read data from a mutating table（变异表）.

29. Triggers管理的方法 • 系统对触发器将要访问的表和列有一些限制。为了定义这些限制，有必要来理解什么是变异表（mutating table ）和约束表(constraining table). • 变异表就是当前被D M L 语句修改的表。对触发器来说，变异表就是触发器在其上进行定义的表 • A table is not considered mutating for STATEMENT triggers.

30. Triggers管理的方法 • 约束表是一种需要实施引用完整性约束而读入的表,即在 • FOREIGN KEY(field) REFERENCES 参照表(field) • 如： • FOREIGN KEY(deptno) REFERENCES dept_new(deptno);

31. Changing Data in a Constraining Table SQL> UPDATE emp 2 SET deptno = 20 3 WHERE deptno = 30; Triggering event Trigger action EMP table Referential integrity DEPT_new table EMPNO 7698 7654 7499 ENAME BLAKEMARTINALLEN DEPTNO 303030 DEPTNO 10 20 3040 DNAME ACCOUNTINGRESEARCHSALESOPERATIONS AFTER UPDATE row Failure Triggered table Constraining table xxxxxxxxxxxxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxxxxxxxxxxxx CASCADE_UPDATES trigger

32. Constraining Table: Example CREATE OR REPLACE TRIGGER cascade_updates AFTER UPDATE OF deptno on emp FOR EACH ROW BEGIN UPDATE dept_new SET dept_new.deptno = :new.deptno WHERE dept_new.deptno = :old.deptno; END;

33. Constraining Table: Example EXAMPLE: constraint_trigger.txt

34. Reading Data from a Mutating Table SQL> UPDATE emp 2 SET sal = 1500 3 WHERE ename = 'SMITH'; CHECK_SALARYtrigger Failure EMP table EMPNO 7369 7698 7788 ENAME SMITHBLAKESCOTT JOB CLERKMANAGERANALYST SAL 150028503000 BEFOREUPDATE row Trigged table/mutating table Trigger event

35. Mutating Table: Example EXAMPLE: • 在insert ,update 员工salary之前，检查新指定的salary。如果新指定的salary高于所有员工中的最高工资,或低于所有员工中的最低工资，则提示错误. mutating_table.txt

36. Trigger的功能 • Security • :trigger_test.txt中的secure_emp • Auditing • :trigger_row txt中的audit_emp_values • Data integrity • Referential integrity • Table replication • Event logging

37. 小结 • Trigger • CREATE Trigger • MANAGE Trigger • 使用Trigger