建立在系统或模式上的触发器,响应系统事件和DDL(CREATE,ALTER,DROP)操作。
CREATE [OR REPLACE] TRIGGER trigger_name BEFORE|AFTER triggering_event [OF column_name] ON table_name] [FOR EACH ROW] [WHEN trigger_condition] DECLARE /*Declarative section is here */ BEGIN /*Exccutable section si here*/ EXCEPTION /*Exception section is here*/ END [trigger_name];•语句级触发器 在默认情况下创建的DML触发器为语句级触发器,即触发事件发生后,触发器只执行一次。
•创建一个触发器,禁止在休息日改变雇员信息
CREATE OR REPLACE TRIGGER trg_emp_weekend
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGINIF TO_CHAR(SYSDATE, 'DY', 'nls_date_language= american') IN('SAT', 'SUN')THENraise_application_error(-20000, 'Can''t operate in weekend. ');END IF;
END trg_emp_weekend;
•如果触发器响应多个DML事件,而且需要根据事件的不同进行不同的操作,则可以在触发器体中使用3个条件谓词。
谓词 | 行为 |
INSERTING | 如果触发语句是INSERT,则为TRUE; 否则为FALSE |
UPDATING | 如果触发语句是UPDATE,则为TRUE; 否则为FALSE |
DELETING | 如果触发语句是DELETE,则为TRUE; 否则为FALSE |
为emp表创建一个触发器,当执行插入操作时,统计操作后员工人数;当执行更新工资操作时,统计更新后员工平均工资;当执行删除操作时,统计删除后各个部门的人数。
CREATE OR REPLACE TRIGGER trg_emp_dml
AFTER INSERT OR UPDATE OR DELETE ON emp
DECLAREv_count NUMBER;v_sal NUMBER(6,2);
BEGINIF INSERTING THEN SELECT count(*) INTO v_count FROM emp;DBMS_OUTPUT.PUT_LINE(v_count);ELSIF UPDATING THENSELECT avg(sal) INTO v_sal FROM emp;DBMS_OUTPUT.PUT_LINE(v_sal);ELSEFOR v_dept IN (SELECT deptno,count(*) num FROM emp GROUP BY deptno) LOOPDBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.num);END LOOP;END IF;
END trg_emp_dml;
触发事件 | :old | :new |
INSERT | 未定义,所有字段都为NULL | 当语句完成时,被插入的记录 |
UPDATE | 更新前原始记录 | 当语句完成时,更新后的记录 |
DELETE | 记录被删除前的原始值 | 未定义,所有字段都为NULL |
:old.field和:new.field (执行部分)
old.field 和new.field (WHEN条件中)
•注意事项: 是伪记录,不能作为整个记录进行赋值或引用 不能传递给带triggering_table%ROWTYPE参数的过程和函数 如果触发器是建立在嵌套表上,;old和;new都执行嵌套表的行,:parent指向父表中的当前行。为emp表创建一个触发器,当插入新员工时显示新员工的员工号、员工名;当更新员工工资时,显示修改前后员工工资;当删除员工时,显示被删除的员工号、员工名。
CREATE OR REPLACE TRIGGER trg_emp_dml_row
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
BEGINIF INSERTING THEN DBMS_OUTPUT.PUT_LINE(:new.empno||' '|| :new.ename);ELSIF UPDATING THENDBMS_OUTPUT.PUT_LINE(:old.sal||' '||:new.sal);ELSEDBMS_OUTPUT.PUT_LINE(:old.empno||' '|| :old.ename);END IF;
END trg_emp_dml_row;
•在行级触发器中,可以使用WHEN子句进一步控制触发器的执行。
例如,修改员工工资时,保证修改后的工资高于修改前的工资。
CREATE OR REPLACE TRIGGER trg_emp_update_row
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN(new.sal<=old.sal)
BEGINRAISE_APPLICATION_ERROR(-20001,'The salary is lower!');
END trg_emp_update_row;
•创建INSTEAD OF触发器的基本语法
CREATE [OR REPLACE] TRIGGER trigger_name INSTEAD OF triggering_event [OF column_name] ON view_name FOR EACH ROW [WHEN trigger_condition] DECLARE /*Declarative section is here */ BEGIN /*Exccutable section si here*/ EXCEPTION /*Exception section is here*/ END [trigger_name];
创建一个包括员工及其所在部门信息的视图empdept,然后向视图中插入一条记录(2345,’TOM’,3000,’SALES’)。
CREATE OR REPLACE VIEW empdept
AS
SELECT empno,ename,sal,dname
FROM emp,dept WHERE emp.deptno=dept.deptno
WITH CHECK OPTION;INSERT INTO empdept
VALUES(2345, 'TOM',3000, 'SALES');
*
ERROR 位于第 1 行:
ORA-01733: 此处不允许虚拟列
CREATE OR REPLACE TRIGGER trig_view
INSTEAD OF INSERT ON empdept
FOR EACH ROW
DECLAREv_deptno dept.deptno%type;
BEGINSELECT deptno INTO v_deptno FROM dept WHERE dname=:new.dname;INSERT INTO emp(empno,ename,sal,deptno) VALUES(:new.empno,:new.ename,v_deptno,:new.sal);
END trig_view;
GRANT,REVOKE,AUDIT,NOAUDIT,
COMMENT,TRUNCATE,ANALYZE,
ASSOCIATE STATISTICS,DISASSOCIATE
STATISTICS等。
触发时间可以是BEFORE,也可以是AFTER。 数据库事件 STARTUP,SHUTDOWN,SERVERERROR,LOGON,LOGOFF等。
触发时间由具体事件决定, 事件 | 允许计时 | 描述 |
STARTUP | AFTER | 当实例开始时激发 |
SHUTDOWN | BEFORE | 当实例关闭时激发 |
SERVERERROR | AFTER | 只要错误发生就激发 |
LOGON | AFTER | 在一个用户成功连接数据库时触发 |
LOGOFF | BEFORE | 在用户注销时开始激发 |
语法 CREATE [OR REPLACE] TRIGGER trigger_name BEFORE|AFTER ddl_event_list|database_event_list ON DATABASE|SCHEMA [WHEN trigger_condition] DECLARE /*Declarative section is here */ BEGIN /*Executable section is here*/ EXCEPTION /*Exception section is here*/ END [trigger_name];•说明 基于数据库(DATABASE)的触发器,只要系统中该触发事件发生,且满足触发条件,则触发器执行; 对于基于模式(SCHEMAN)的触发器,只有当特定模式中的触发事件发生时,触发器才执行。 •注意 STARTUP和SHUTDOWN事件只能激发基于数据库的触发器。
•将每个用户的登录信息写入temp_table表中。
CREATE OR REPLACE TRIGGER log_user_connection
AFTER LOGON ON DATABASE
BEGININSERT INTO scott.temp_table VALUES (user,sysdate);
END log_user_connection;
•在系统触发器内部可以使用一些事件属性函数以获得触发事件的信息。
•由于系统没有为这些事件属性函数指定同义词,因此在调用时必须在其前加上“SYS.”前缀。
•
示例
当数据库中执行CREATE操作时,将创建的对象信息记录到ddl_creations表中。
CREATE TABLE ddl_creations (user_id VARCHAR2(30),object_type VARCHAR2(20),object_name VARCHAR2(30),object_owner VARCHAR2(30),creation_date DATE);CREATE OR REPLACE TRIGGER log_creations
AFTER CREATE ON DATABASE
BEGININSERT INTO ddl_creationsVALUES(ora_login_user, ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner, sysdate);
END log_creations;
KEY,UNIQUE 或FOREIGN KEY关键字的列, 但
可以修改其他列。
注意 如果INSERT…VALUES语句只影响一行,那么该语句的行级前触发器不会把触发表当做变异表对待。 INSERT INTO table SELECT…等语句总是把触发表当做变异表,即使子查询仅仅返回一条记录。 •如果既想更新变异表,同时又需要查询变异表,那么如何处理呢? 将行级触发器与语句级触发器结合起来,在行级触发器中获取要修改的记录的信息,存放到一个软件包的全局变量中,然后在语句级后触发器中利用软件包中全局变量信息对变异表的查询,并根据查询的结果进行业务处理。为了实现在更新员工所在部门或向部门插入新员工时,部门中员工人数不超过8人,可以在emp表上创建两个触发器,同时创建一个共享信息的包。
CREATE OR REPLACE PACKAGE share_pkg
ASv_deptno NUMBER(2);
END;CREATE OR REPLACE TRIGGER rmutate_trigger
BEFORE INSERT OR UPDATE OF deptno ON EMP
FOR EACH ROW
BEGINshare_pkg.v_deptno:=:new.deptno;
END;
CREATE OR REPLACE TRIGGER smutate_trigger
AFTER INSERT OR UPDATE OF deptno ON EMP
DECLAREv_num number(3);
BEGINSELECT count(*) INTO v_num FROM emp WHERE deptno=share_pkg.v_deptno;IF v_num>8 THENRAISE_APPLICATION_ERROR(-20003,'TOO MANY EMPLOYEES IN DEPARTMENT '||share_pkg.v_deptno);END IF;
END;
测试
INSERT INTO emp(empno,ename,sal,deptno) VALUES(2,'WANG',2000,10);
UPDATE emp SET deptno=10 WHERE empno=7844;
UPDATE emp SET deptno=10 WHERE empno=7369;
UPDATE emp SET deptno=10 WHERE empno=7369*
第 1 行出现错误:
ORA-20003: TOO MANY EMPLOYEES IN DEPARTMENT 10
ORA-06512: 在 "SCOTT.SMUTATE_TRIGGER", line 6
ORA-04088: 触发器 'SCOTT.SMUTATE_TRIGGER' 执行过程中出错
触发器的管理
•触发器名称 触发器存在于单独的名字空间中,在一个模式中可以与其他对象同名 •触发器的限制 不能出现任何事务控制语句。因为触发器作为触发语句执行的一部分,处于同一个事务中。 触发器体所调用的过程或函数都不能发出任何事务控制语句(自治事务子程序除外)。 触发器体中不能声明LONG 或LONG RAW变量,而且:new和:old不能引用LONG或LONG RAW类型的列。 触发器体中可以引用LOB和OBJECT列,但不能修改该列的值。 触发器的大小不能超过32K。 •激活或禁用触发器 可以激活或禁用某个触发器。语法为:ALTER TRIGGER triggername ENABLE|DISABLE;
激活或禁用某个表对象上的所有触发器。语法为:ALTER TABLE table_name ENABLE|DISABLE ALL
TRIGGERS;
•修改触发器 CREATE OR REPLACE TRIGGER trigger_name •重新编译触发器 ALTER TRIGGER trigger_name COMPILE; •查看触发器及其源代码 查询数据字典视图USER_TRIGGERS SELECT trigger_name,trigger_type, table_name,trigger_body FROM user_triggers; •删除触发器 当触发器不再需要时,可以使用DROP TRIGGER语句删除触发器。 DROP TRIGGER trigger_name;本人从事软件项目开发20多年,2005年开始从事Java工程师系列课程的教学工作,录制50多门精品视频课程,包含java基础,jspweb开发,SSH,SSM,SpringBoot,SpringCloud,人工智能,在线支付等众多商业项目,每门课程都包含有项目实战,上课PPT,及完整的源代码下载,有兴趣的朋友可以看看我的在线课堂
讲师课堂链接:https://edu.csdn.net/lecturer/893