参考:https://blog.csdn.net/weixin_41649106/article/details/86981325
https://blog.csdn.net/htj10/article/details/114629358
触发器的应用场景
- 复杂的安全性检查
- 数据的确认
- 数据库审计
- 数据的备份和审计
触发器例子1:update 之前进行安全检查
准备工作:
create table t_salary(id number(8), sal number(10,2)); insert into t_salary(id, sal) values(1, 8000); select * from t_salary;
创建触发器:
/** 涨后的薪水不能低于涨前的薪水 1 :old 和 :new 代表同一条记录 2 :old 代表操作该行之前,这一行的值 :new 代表操作该行之后,这一行的值 */ create or replace trigger checkSalary before update on t_salary for each row declare -- local variables here 没有变量声明的话,declare可以省略 begin -- if 涨后的薪水 < 涨前的薪水 then 如何表示呢 ? if :new.sal < :old.sal then raise_application_error(-20002,'涨后的薪水:'|| :new.sal ||'小于涨前的薪水:'||:old.sal); end if; end checkSalary;
测试:
SQL> update t_salary set sal=7000 where id=1; update t_salary set sal=7000 where id=1 * 第 1 行出现错误: ORA-20002: 涨后的薪水:7000小于涨前的薪水:9000 ORA-06512: 在 "SCOTT.CHECKSALARY", line 6 ORA-04088: 触发器 'SCOTT.CHECKSALARY' 执行过程中出错
删除触发器
--删除 触发器 drop trigger checkSalary;
例子2:备份数据
/* 删除前,将数据备份 */ create or replace trigger tri_sal_delete_bk before delete on t_salary for each row begin insert into t_salary_del(id,sal) values(:old.id, :old.sal); -- commit; -- 注意不可以有提交,会报错ora-04092 end tri_sal_delete_bk;
注意:DML(delete/update/insert)触发器中不能使用DDL(CREATE,DROP,ALTER)语句,也不能使用事务控制语句(ROLLBACK, COMMIT,SAVEPOINT)。特别注意的是,在触发器的主体中引用的函数(function)/过程(procedure)中也不能有事物控制语句。
例子3:非工作时间禁止插入数据
/** 非工作时间(星期六 星期日, 非9点~18点的区间)禁止写入数据 首先要搞清楚: 触发器的类型--语句级触发器。 不管插入了多少条数据,没有必要对每一行数据都进行校验,只要不在这个时间段内,都不让插入。 */ create or replace trigger tri_addSalaryCheck before insert on t_salary declare -- local variables here begin if to_char(sysdate, 'day') in ('星期六', '星期日') or to_number(to_char(sysdate, 'hh24')) not between 9 and 18 then -- 禁止insert raise_application_error(-20001,'非工作时间禁止插入数据'); end if; end tri_addSalaryCheck;
例子4:Oracle实现像Mysql的自动增长auto_increment
-- 建表: CREATE TABLE t_user(U_ID NUMBER(8), UNAME VARCHAR2(20), UPASSWORD VARCHAR2(20)); -- 建立序列: CREATE SEQUENCE SEQ_TUSER INCREMENT BY 1 MINVALUE 1 --最小值 MAXVALUE 99999999 --最大值由NUMBER(8) NOCYCLE --不打环 NOCACHE --不缓存 ORDER; COMMIT; -- 然后建立before的触发器: CREATE OR REPLACE TRIGGER TRG_ADDTUSER BEFORE INSERT ON t_user FOR EACH ROW BEGIN SELECT SEQ_TUSER.NEXTVAL INTO :NEW.U_ID FROM DUAL; END TRG_ADDTUSER; -- 测试: INSERT INTO t_user (UNAME,UPASSWORD) VALUES ('libai','589avf'); INSERT INTO t_user (UNAME,UPASSWORD) VALUES ('zhangsan','ko098'); COMMIT; select * from t_user;