oracle触发器详解

1.使用触发器需要满足那些需求

• 不需要显式调用来执行，而是有一个事件来启动
• 可以通过其他oracle事件触发调用的程序，因为触发器不能接受参数

2.oracle事件包括哪几种条件

• insert、update及delete操作或及对视图类似的操作

• 执行DDL操作

• 数据库的启动和关闭

3.触发器类型

• 模式（DDL）触发器
• 数据库级触发器
• DML触发器（行级触发器、语句级触发器、instead of触发器）
• 详解

--DDL触发器是在模式中执行DDL语句时执行
--数据库级触发器是在发生打开。关闭、登录和退出数据库等系统事件时执行
--DML触发器是对表和视图执行DML语句时执行（主流）
--语句级触发器是无论受影响的行数是多少，都只执行一次（主流）
--行级触发器是指对DML语句修改的每一行执行一次
--instead of触发器用于用户不能直接使用DML语句修改的视图

4.开始代码

• –创建一个与emp表相同结构的emp2表
• emp表

CREATE TABLE EMP
   (	"EMPNO" NUMBER(4,0), 
	"ENAME" VARCHAR2(10 BYTE), 
	"JOB" VARCHAR2(9 BYTE), 
	"MGR" NUMBER(4,0), 
	"HIREDATE" DATE, 
	"SAL" NUMBER(7,2), 
	"COMM" NUMBER(7,2), 
	"DEPTNO" NUMBER(2,0), 
	 CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

create table emp2 as select * from emp where 1=2

1.插入触发器

• 实现 当向emp表插入数据时，复制一份信息到emp2

CREATE OR REPLACE TRIGGER TRI_INSERT_EMP 
BEFORE INSERT ON emp
--行级触发器
for each row
BEGIN
  insert into emp2(empno, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values(
    :new.empno,
    :new.ename,
    :new.job,
    :new.mgr,
    :new.hiredate,
    :new.sal,
    :new.comm,
    :new.deptno
  );
END;

• 实现 当向emp表插入sal为0的数据时，emp2表报错

CREATE OR REPLACE TRIGGER TRI_INSERT_EMP 
BEFORE INSERT ON emp
--行级触发器
for each row
BEGIN
    if :new.sal<=0 then
    	--报错
        RAISE_APPLICATION_ERROR(-20009, '薪水不能为负');
    else
  insert into emp2(empno, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values(
    :new.empno,
    :new.ename,
    :new.job,
    :new.mgr,
    :new.hiredate,
    :new.sal,
    :new.comm,
    :new.deptno
  );
  end if;
END;

• 实现 当插入新行时，empid自增
• 创建一个序列

create sequence seq_emp_id minvalue 8000 maxvalue 9999 start with 8000 increment by 1;

• 创建触发器

create or replace TRIGGER TRI_insert_EMPautoid 
BEFORE insert ON emp
for each row
BEGIN
  :new.empno:=seq_emp_id.nextval;

END;

• 创建一个主键自增且具备基础的时间信息的触发器
• 创建一个userinfo表

create table userinfo(
    id varchar2(20),
    name varchar2(20)
)

• 创建触发器

create or replace TRIGGER TRI_insert_userinfoautoid 
BEFORE insert ON userinfo
for each row
declare
    v_id varchar2(20);
BEGIN
    v_id:=to_char(sysdate,'yyyyMMddhhmmss')||SEQ_EMP_ID.nextval;
  :new.id:=v_id;

END;

• 插入一条数据测试

2.修改触发器

• 实现当修改emp表中的信息时，同样修改emp2表的信息

create or replace TRIGGER TRI_update_EMP 
BEFORE update ON emp
--行级触发器
for each row
BEGIN
        
    update emp2 set sal=:new.sal where empno=:old.empno;

END;

• 测试执行修改

update emp set sal=8888 where empno='7869';

3.删除触发器

• 实现删除的emp表的数据添加到emp3表中
• 创建一个emp3表

create table emp3 as select * from emp where 1=2;

• 创建触发器

CREATE OR REPLACE TRIGGER TRI_DELETE_EMP 
BEFORE DELETE ON emp
for each row
BEGIN
  insert into emp3(empno, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(
  :old.empno, :old.ENAME, :old.JOB, :old.MGR, :old.HIREDATE, :old.SAL, :old.COMM, :old.DEPTNO
  );

  
  
END;

• 实现 删除emp表的数据添加到emp3表中，并有一列deleteTime用于存储删除时间
• 修改emp3表结构
• 首先在emp3表中加上一列deleteTime
• 创建触发器

create or replace TRIGGER TRI_DELETE_EMP 
BEFORE DELETE ON emp
for each row
BEGIN
  insert into emp3(empno, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO,deleteTime) values(
  :old.empno, :old.ENAME, :old.JOB, :old.MGR, :old.HIREDATE, :old.SAL, :old.COMM, :old.DEPTNO,sysdate
  );



END;

• 测试
• 删除emp表的数据为empno为7855的数据

delete from emp where empno='7855';