Oracle的触发器Trigger

参考：https://blog.csdn.net/weixin_41649106/article/details/86981325
https://blog.csdn.net/htj10/article/details/114629358
Oracle 触发器详解（trigger）

触发器的应用场景

1. 复杂的安全性检查
2. 数据的确认
3. 数据库审计
4. 数据的备份和审计

触发器例子1：update 之前进行安全检查

准备工作：

?

1 2 3

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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

/** 涨后的薪水不能低于涨前的薪水 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;

 测试：

?

1 2 3 4 5 6 7

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' 执行过程中出错

 删除触发器

?

1 2	--删除 触发器 drop trigger checkSalary;

 例子2：备份数据

?

1 2 3 4 5 6 7 8 9 10 11

/* 删除前，将数据备份 */ 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：非工作时间禁止插入数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

/** 非工作时间（星期六 星期日， 非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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

-- 建表： 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;

 

例子5：插入或更新或删除 后 触发

CREATE OR REPLACE TRIGGER trigger_table01 
AFTER INSERT OR UPDATE OR DELETE ON table01
 FOR EACH ROW
  
DECLARE recordNum INT;
BEGIN
  IF (inserting OR updating) THEN
    SELECT COUNT(1) INTO recordNum FROM table02 WHERE name=:NEW.USER_NAME;
    IF (recordNum =0) THEN
      EXECUTE IMMEDIATE 'insert into table02(name) values ('''||:NEW.USER_NAME||''') ';
    END IF;
  ELSIF DELETING THEN
    EXECUTE IMMEDIATE 'delete from table02 where name = '''||:OLD.USER_NAME||''' ';
  END IF;
END;