Oracle触发器总结与实例

一、什么是触发器？

定义在表上面的pl/sql程序，特定操作（增删改）时触发

Create [or replace] trigger saynewemp

after/before  insert/delete/update[of 列名]

on emp [for each row[when(条件)]]

declare

begin

dbms_output.put_line(‘成功插入新员工’);//打印

end;

类似于Java中的监听器

 

二、触发器的语法

触发器的类型：

①语句级触发器(无 for each row)：在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。针对表。

②行级触发器（有for each row）：触发语句作用的每一条记录都被触发。在行级触发器中使用:old和:new伪记录变量，识别值得状态，代表操作之前或者之后的值。针对行。

 

三、触发器的应用场景：

1. 复杂的安全性检查

eg:禁止在非工作时间插入新员工

周末：to_char(sysdate,’day’) in (‘星期六’,’星期日’)

上班前、下班后：to_number(to_char(sysdate,’hh24’)) not between 9 and 18

create or replace trigger saynewemp

before  insert

on emp

begin

If to_char(sysdate,’day’) in  (‘星期六’,’星期日’) or

to_number(to_char(sysdate,’hh24’)) not between 9 and 18 then

--禁止插入新员工

raise_application_error(-20001,’禁止在非工作时间插入新员工’);

end;

 

1. 数据的确认

eg:涨工资不能越长越少,先确定是语句级还是行级

create or replace trigger saynewemp

before  update

on emp for each row

begin

If :new.sale<:old.sal then

--禁止插入新员工

raise_application_error(-20002,’涨后的薪水不能少于涨前的薪水’);

End if;

end;

 

1. 数据库审计--基于值得审计

eg:当员工涨薪超过6000进行审计员工信息

Create table audit_info

(

Information varchar2(200)

);

create or replace trigger doaudit

after  update

on emp for each row

begin

If :new.sale>6000 then

Insert into audit_info values(:new.empno);

End if;

end;

 

1. 数据的备份和同步:分布式数据库

eg:当给员工涨完工资后，自动备份新的工资到备份表中

Create table emp_bakeup select * from emp;

create or replace trigger sync_salary

after  update

on emp for each row

begin

Update emp_bakeup set sal=:new.sal where empno=:new.empno;

end;