PL/SQL触发器
程序员文章站
2022-05-08 18:33:38
...
触发器 1、触发器相当于触发按钮发生的事件,它是在事件发生时隐式地运行的,并且触发器不能接收参数。 2、触发的事件可以是对数据库表的DML操作或某个视图操作,也可以是系统事件,如数据库的启动和关闭及一些DDL操作。 3、触发器分三种类型: DML触发器(邮表上执行的INSERT/UPDATE/DELETE操作触发);INSTEAD OF替代触发器(用于视图的操作);系统触发器(用于系统事件触发)。 4、触发器的作用:安全性方面,确定用户的操作是否可继续执行;产生对数据值修改的审计,将修改的信息记录下来,产生数据改动记录;提供更灵活的完整性校验规则,能够根据 更复杂的规则校验数据;提供表数据的同步复制,使多个表的数据达到同步;事件日志记录,记录数据库的重要操作信息。 5、创建语句级DML触发器语法"create [or replace] trigger 触发器名 触发时机 事件 on 表名 when 条件 触发器执行体",如: --创建DML触发器,阻止在非工作时间对EMP表插入记录 SQL>CREATE OR REPLACE TRIGGER secure_emp BEFORE INSERT ON emp /*触发时机可以是AFTER或BEFORE*/ BEGIN IF (TO_CHAR(SYSDATE,'DY') IN ('星期六','星期日')) OR (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00') THEN RAISE_APPLICATION_ERROR(-20500,'你只能在工作时间对EMP表执行插入操作'); /*将应用程序的错误从服务器端传递到客户端应用程序*/ END IF; END; 用户如果在非工作时间给EMP表增加记录,触发器会自动触发并报错。 6、行级触发器:增加了for each row子句,表示每次对表中的每行操作时都会触发这个触发器。如: --创建行级触发器,当降低薪水时引发异常 SQL>CREATE OR REPLACE TRIGGER check_salary BEFORE UPDATE OF sal ON emp /*触发时机可以是AFTER或BEFORE*/ FOR EACH ROW WHEN (NEW.sal<OLD.sal) BEGIN RAISE_APPLICATION_ERROR(-20501,'不允许降低薪水'); /*-20501为错误编号,自己定义的,取值范围从-20000到-20999之间,为避免与oracle冲突*/ END; 7、创建替代触发器:当需要通过视图影响它所对应的基表时,可以通过替代触发器来实现,如: --创建替代触发器,将对视图的删除操作,转换为对emp表的删除操作 SQL>CREATE OR REPLACE TRIGGER emp_view_delete INSTEAD OF delete ON emp_view /*emp_view为视图名*/ FOR EACH ROW BEGIN DELETE FROM empWHERE deptno= :old.deptno; END; 8、系统触发器分为两种:用户触发事件(DDL命令;登录或者退出数据库连接)和系统触发事件(启动、关闭数据库;特殊错误发生)。如: --创建系统事件触发器,记录用户的登录登出 SQL>conn sys/oracle as sysdba /*连接登录数据库*/ SQL>create table event(eventuser varchar2(20), action varchar2(20), logtime date default sysdate); /*默认为系统时间*/ --创建数据表event,记录用户的登录 SQL>CREATE OR REPLACE TRIGGER trig_logon AFTER logon ON DATABASE BEGIN --将事件属性插入到事件日志表中 INSERT INTO sys.event(eventuser,action) VALUES(USER,'log on'); END; --创建触发器,记录用户的登出 SQL>CREATE OR REPLACE TRIGGER trig_logoff AFTER logoff ON DATABASE BEGIN --将事件属性插入到事件日志表中 INSERT INTO sys.event(eventuser,action) VALUES(USER,'log off'); END; 9、在PL/SQL触发器中调用存储过程的访求是通过CALL命令,如: SQL>CREATE OR REPLACE TRIGGER trig_ddl AFTER CREATE OR ALTER OR DROP ON DATABASE CALL 过程名 注意此种方式创建触发器不需BEGIN和END,程序末尾出无需写分号";"。 10、在数据字典中查看触发器"select object_name from user_objects where object_type='TRIGGER'" 11、触发器默认是启用状态,禁用触发器"alter trigger 触发器名 disable",禁用某表上所有的触发器"alter table 表名 disable all triggers"。 12、删除触发器"drop trigger 触发器名"。 13、触发器的执行顺序,表上所有触发事件的触发顺序如下: (1)执行所有的before statement触发器。 (2)根据操作的行循环地执行:所有的before row 触发器;所有after row触发器。 (3)执行DML语句,进行完整性约束条件检测。 (4)执行所有的after statement触发器。 作用完善多表之间的完整性约束校验,当对主表操作时,如果主表被子表引用,则先对子表操作,这样就不会违反完整性约束了,如: --建立触发器之前执行修改操作,此时会报错,违反完整性约束条件 SQL>UPDATE dept SET deptno=70 WHERE deptno=20; --建立触发器 SQL>CREATE OR REPLACE TRIGGER trig_update AFTER UPDATE OF deptno ON dept /**/ FOR EACH ROW BEGIN --先将emp表中所有属于部门20的员工更改到新的部门70下,再修改主表的部门编号 UPDATE emp SET emp.deptno= :NEW.deptno WHERE emp.deptno= :OLD.deptno; END; --建立触发器之后执行修改操作,成功 SQL>UPDATE dept SET deptno=70 WHERE deptno=20; /*把部门编号为20的修改成70*/