17PLSQL–触发器 触发器： 一、类型 1、DML触发器：基于表和视图的数据变化。 INSERT UPDATE DELETE 2、DDL触发器：基于对象的创建、修改和删除 CREATE ALTER DROP 3、数据库级触发器：基于数据库服务器操作时触发 STARTUP SHUT DOWN LOGON LOGOFF SERVERERROR 二、DML触发器 1、语法 CREATE OR REPLACE TRIGGER 名称 BEFORE|AFTER ON 表/视图 WHEN 条件 FOR EACH ROW 行级/表级触发器 DECLARE – 声明变量 BEGIN – 主体 EXCEPTION – 异常处理 END; / a、BEFORE/AFTER：表示事件前还是事件后要执行触发器程序。 比如说插入一条记录到表中：如果是BEFORE，表示插入记录之前要先执行触发器。 如果是AFTER表示将该条记录先插入到表中，然后再执行触发器。 b、WHEN条件，表示带条件的执行触发器，并不是都执行。比如说，我要更新工资， 当工资涨幅是正数时才执行，是负数降薪不执行。 c、FOR EACH ROW行级触发器：SQL语句每影响一行都要执行一次触发器。 表级触发器：表示一个SQL语句执行一次触发器。 举例：DELETE FROM emp WHERE deptno=10; — 有5条记录。 行级触发器会执行5次,表级触发器会执行一次。 2、案例：员工表插入记录时，入职日期不能早于出生日期。 CREATE TABLE employees ( id INT PRIMARY KEY, — 员工编号 name NVARCHAR2(50), — 员工姓名 birthday DATE, — 出生日期 hiredate DATE — 入职日期 ); INSERT INTO employees VALUES(1,’张开’,TO_DATE(‘2000-1-1′,’YYYY-MM-DD’),TO_DATE(‘2006-9-1′,’YYYY-MM-DD’)); INSERT INTO employees VALUES(2,’胡力文’,TO_DATE(‘1998-3-15′,’YYYY-MM-DD’),TO_DATE(‘1996-9-1′,’YYYY-MM-DD’)); – 编写触发器，避免无效数据插入。 CREATE OR REPLACE TRIGGER trig_employees_hiredate AFTER INSERT ON employees FOR EACH ROW DECLARE BEGIN – 记录值并验证，插入的记录保存在:new伪行对象里 IF :new.hiredate<:new.birthday THEN RAISE_APPLICATION_ERROR(-20001,’入职日期不能早于出生日期！’); END IF; /* EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE(SQLERRM); */ END; / – 测试验证触发器的有效性 INSERT INTO employees VALUES(3,’张哲铭’,TO_DATE(‘1999-3-15′,’YYYY-MM-DD’),TO_DATE(‘1998-9-1′,’YYYY-MM-DD’)); INSERT INTO employees VALUES(4,’徐俊瑶’,TO_DATE(‘1999-3-15′,’YYYY-MM-DD’),TO_DATE(‘1998-9-1′,’YYYY-MM-DD’)); – 用约束实现 CREATE TABLE employees2 ( id INT PRIMARY KEY, — 员工编号 name NVARCHAR2(50), — 员工姓名 birthday DATE, — 出生日期 hiredate DATE, — 入职日期 CONSTRAINT CK_employees2_hiredate CHECK(hiredate>birthday) ); INSERT INTO employees2 VALUES(1,’张开’,TO_DATE(‘2000-1-1′,’YYYY-MM-DD’),TO_DATE(‘2006-9-1′,’YYYY-MM-DD’)); INSERT INTO employees2 VALUES(2,’胡力文’,TO_DATE(‘1998-3-15′,’YYYY-MM-DD’),TO_DATE(‘1996-9-1′,’YYYY-MM-DD’)); – 原则：触发器可以实现检查约束。但是，能用约束实现的，尽量不要使用触发器。 3、带条件执行的触发器 案例：当薪资涨幅超过1000时，给予提醒不允许。 CREATE OR REPLACE TRIGGER trig_emp_sal BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (ABS(new.sal-old.sal)>=1000) DECLARE BEGIN – DBMS_OUTPUT.PUT_LINE(”); RAISE_APPLICATION_ERROR(-20002,’涨薪幅度不能超过1000！’); END; / – 测试验证 UPDATE emp SET sal=sal+1 WHERE empno=7369; UPDATE emp SET sal=sal+1001 WHERE empno=7369; UPDATE emp SET sal=sal-1 WHERE empno=7369; UPDATE emp SET sal=sal-1001 WHERE empno=7369; 4、伪记录：new/old new：新增的记录或者修改后的记录存放在new伪记录里 old：删除的记录或者修改前的记录存放在old伪记录里 5、基于增删改操作的三个谓词 INSERTING：添加记录时，该谓词为TRUE UPDATING：修改记录时，该谓词为TRUE DELETING：删除记录时，该谓词为TRUE 案例：当增删改员工表记录时，须对其操作进行日志记载。 – 创建日志表 create table logs( log_id number(10) primary key, — 自动编号 log_table varchar2(10) not null, — 表名 log_dml varchar2(10), — 操作命令 log_key_id number(10), — 主键字段值 log_date date, — 操作日期 log_user varchar2(15) — 操作用户 ); – 创建序列，实现自动编号 CREATE SEQUENCE logs_id_squ ; – 创建触发器，实现以上功能 create or replace trigger trig_emp_dml_log before –触发时间为操作前 delete or insert or update — 由三种事件触发 on emp –表名 for each row — 行级触发器 begin if inserting then insert into logs values(logs_id_squ.nextval,’emp’,’insert’,:new.empno, sysdate,user); elsif deleting then insert into logs values(logs_id_squ.nextval,’emp’,’delete’,:old.empno, sysdate,user); else insert into logs values(logs_id_squ.nextval,’emp’,’update’,:new.empno,sysdate,user); end if; end; / – 测试验证触发器 INSERT INTO emp(empno,ename,sal) VALUES(8001,’张飞’,1); UPDATE emp SET sal=1000 WHERE empno=8001; DELETE FROM emp WHERE empno=8001; 三、模式触发器：基于DLL语句的CREATE/ALTER/DROP监控触发器 1、SCHEMA:模式，以用户为单位的一些对象操作。 案例：跟踪对象创建/修改/删除操作的日志记录。 – 创建日志记录表 CREATE TABLE dropped_obj ( obj_name NVARCHAR2(50), obj_type NVARCHAR2(50), opr_user NVARCHAR2(50), opr_date DATE ); – 创建触发器 CREATE OR REPLACE TRIGGER trig_obj_opr_log AFTER CREATE OR ALTER OR DROP ON SCHEMA –创建、修改和删除Schema对象时触发 BEGIN INSERT INTO dropped_obj VALUES( ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, user,SYSDATE); END; / ORA_DICT_OBJ_NAME：数据字典对象名 ORA_DICT_OBJ_TYPE： 数据字典对象类型 –测试触发器 create table emp11 as select * from emp where deptno=20; create table emp12 as select * from emp where deptno>20; select * from emp11; select * from emp12; select * from dropped_obj; drop table emp11; drop table emp11; select * from dropped_obj; create view view_emp01 as select * from emp; – 修改表，添加约束 alter table emp11 add constraint pk_emp11 primary key(empno); drop table emp12; SQL> select * from dropped_obj; OBJ_NAME OBJ_TYPE OPR_USER OPR_DATE ————————————- ———– EMP11 TABLE SCOTT 2021/11/22 EMP12 TABLE SCOTT 2021/11/22 VIEW_EMP01 VIEW SCOTT 2021/11/22 PK_EMP11 INDEX SCOTT 2021/11/22 EMP11 TABLE SCOTT 2021/11/22 EMP12 TABLE SCOTT 2021/11/22 6 rows selected 四、数据库事件触发器：包括如下事件 启动数据库：STARTUP 关闭数据库：SHUTDOWN 数据库服务器出现异常：SERVERERROR 用户登录：LOGON 用户注销：LOGOFF STARTUP 数据库打开时触发 SHUTDOWN 使用NORMAL/IMMEDIATE关闭数据库时触发 SERVERERROR 发生服务器错误时触发 LOGON 当用户连接到数据库，建立会话时触发 LOGOFF 当会话从数据库中断开时触发 注意：要用管理员创建 conn system/ as sysdba create table userlog ( username varchar2(20), event NVARCHAR2(50), logon_time date ); create or replace trigger database_logon after logon or startup or servererror on database –登录数据库后触发 begin insert into userlog values(sys.login_user,ora_sysevent,sysdate); end; / GRANT select ON userlog TO PUBLIC; – 测试验证触发器的使用 conn scott/tiger conn system/ select * from sys.userlog; – 创建退出登录和关闭数据库事件触发器 create or replace trigger database_logoff before logoff or shutdown on database –登录数据库后触发 begin insert into userlog values(sys.login_user,ora_sysevent,sysdate); end; /
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/75122.html
