db2触发器怎么写 触发器的sql语句解释

触发器概述

触发器是一件事情发生前后自动执行的pl/sql块，不接受参数，无法显式调用

举个栗子，例如著名的蝴蝶效应：哪哪哪的蝴蝶轻轻扇了扇翅膀，哪哪哪就刮起了龙卷风。

语法格式

create [or replace] trigger 触发器名
[before|after|instead of] 触发事件 on 表名
[for each row]
[when 条件]
触发体(标准pl/sql代码块块)

• 无[for each row]且[before|after|instead of]中不选择instead of选项的为语句级触发器
• 带 [for each row]且[before|after|instead of]中不选择instead of选项的为行级触发器
• 带 [for each row]且[before|after|instead of]中选择instead of选项的为instead of触发器
• [or replace]无法更改on 表名选项

触发器分类

• DML触发器——执行DML语句触发（增、删、改）

（1）语句级触发器/表级触发器——只触发一次，不管更改了几行

--往dept表中插入数据时调用触发器打印你好
create or replace trigger t1
before insert on dept
begin
	dbms_output.put_line('你好');
end;
--调用语句
update dept set dname='beijing';

（2）行级触发器——更改了几行触发几次

--定义
create or replace trigger t1
before insert on dept
for each row
begin
	:new.dname:='无';
end;
--调用
insert into dept(deptno) values(90);

对上述触发器中 :new.dname:=‘无’;该剧我做如下理解：对于insert、update而言，触发器中:new中的字段即为其要向基表插入的数据insert into values(deptno,dname,loc);一定程度上等价于：new.deptno：=值；：new.dname：=值；：new.loc：=值；

（3）instead of触发器（用于视图）

将视图中不能执行的增删改操作转换为等价的可以执行的pl/sql程序块

--创建视图
create view v1 as select e.*,d.dname,d.loc
from emp e,dept d where e.deptno=d.deptno;
--查看视图数据
select * from v1;
--定义触发器(根据雇员编号改部门名)
create or replace trigger t1
instead of update on v1
declare
        v_deptno dept.deptno%type;
begin
  dbms_output.put_line('新的值：'||:new.empno||', '||:new.ename||', '||:new.job||', '||:new.mgr||', '||:new.hiredate||
  ', '||:new.sal||', '||:new.comm||', '||:new.deptno||', '||:new.dname||', '||:new.loc );
  dbms_output.put_line('旧的值：'||:old.empno||', '||:old.ename||', '||:old.job||', '||:old.mgr||', '||:old.hiredate
  ||', '||:old.sal||', '||:old.comm||', '||:old.deptno||', '||:old.dname||', '||:old.loc);
  select deptno into v_deptno  from emp where empno=:old.empno;
  update dept set dname=:new.dname where deptno=v_deptno;
end;
--调用
update v1 set dname='孙涵' where empno=7782;

--查看视图数据
select * from v1;

• DDL触发器——执行DDL语句时触发（alter、drop、create、truncate）

常用系统变量如下：
a. Ora_client_ip_address ——返回客户端的ip地址
b. Ora_database_name——返回当前数据库名
c. Ora_login_user——返回登录用户名
d. Ora_dict_obj_name——返回ddl操作所对应的数据库对象名
e. Ora_dict_obj_type——返回ddl操作所对应的数据库对象的类型

• 数据库级触发器——发生系统事件（数据库启动、用户登录等）时执行

注：笔者在此不再过多赘述DDL触发器与数据库级触发器感兴趣的可以上网冲浪搜索一下

启用、禁用和删除触发器

（1）启用触发器：

alter trigger 触发器名 enable;

（2）禁用触发器：

alter trigger 触发器名 disable;

（3）删除触发器：

drop trigger 触发器名;

部分实例详细解释

:new与:old简单解释

当进行dml操作时，系统会产生临时表：new和：old,临时表结构与进行操作的表结构相同且只能在行级触发器中使用

• insert或update操作会产生：new临时表，存储的是插入或更改的新值
• delete或update操作会产生：old临时表，存储的是DML操作删除或更改前的值

--触发器定义（update）
create or replace trigger t1
before update on emp
for each row
begin
  dbms_output.put_line(:old.empno||', '||:old.ename||', '||:old.job||', '||:old.mgr||', '||:old.hiredate||', '||:old.sal||', '||:old.sal
  ||', '||:old.comm||', '||:old.deptno);
end;
--调用(试思考带where的update语句中：old中的值）
update  emp set ename='张';

--触发器定义（insert）
create or replace trigger t1
before insert on emp
for each row
begin
  dbms_output.put_line(:new.empno||', '||:new.ename||', '||:new.job||', '||:new.mgr||', '||:new.hiredate||', '||:new.sal||', '
  ||', '||:new.comm||', '||:new.deptno);
end;


--调用
insert into emp values(7788,'王云峰'，'教师',7369,null,null,null,30);

--触发器定义（delete）
create or replace trigger t1
before delete on emp
for each row
begin
  dbms_output.put_line(:old.empno||', '||:old.ename||', '||:old.job||', '||:old.mgr||', '||:old.hiredate||', '||:old.sal||', '
  ||', '||:old.comm||', '||:old.deptno);
end;


--调用
delete from emp where empno=7369;

• 若将before改为after则无法赋值给：new去更改插入或更新的值，因为更新/插入操作已经完成

--错误示范
create or replace trigger t1
after insert or update  on emp
for each row
begin
  
  if updating then
     :new.ename:='张三';
  elsif inserting then
     :new.ename:='王五';
  end if;  
end;

搭配序列使用

--建表语句
create table a(
       aid number primary key,
       aname varchar2(20)
);
--创建序列
create sequence s1 start with 1 increment by 1;
--设置触发器
create or replace trigger t1
before insert on a
for each row
begin
    :new.aid:=s1.nextval;    
end;
--调用
insert into a(aname) values('刘柳');
select * from a;

备份表

(1)普通方式

--数据与结构兼得
create table 表名 as select * from 备份表名;
--只要结构不要数据
create table 表名 as select * from 备份表名 where 1<>1;

(2)触发器方式

--备份emp表
--创建emp表同结构备份表
create table emp_bak as select * from emp where 1<>1;
--创建触发器（当数据被删除或修改时将原数据插入到emp_bak）、数据过多只展示部分列
create or replace trigger t1
before update or delete on emp
for each row
begin
  insert into emp_bak(ename,empno) values(:old.ename,:old.empno);
end;
--调用
delete from emp;
--查看备份表数据
select * from emp_bak;

inserting、updating、deleting

• 如果是insert语句触发的触发器则inserting返回true
• 如果是updating语句触发的触发器则updating返回true
• 如果是deleting语句触发的触发器则deleting返回true

行级、表级触发器均可使用

--创建触发器
create or replace trigger t1
before insert or delete or update on dept
  begin
    if inserting then
      dbms_output.put_line('新增数据');
    elsif updating then
      dbms_output.put_line('修改数据');
    elsif deleting then
      dbms_output.put_line('删除数据');
    end if;
  end;
--调用
delete dept;

when

“加筹码”，相当于增加了触发该触发器得难度，when不能用于表级触发器

--创建触发器(when处new前不加:,因为要一条一条比较加：会提示变量使用无效)
create or replace trigger t1
before insert on dept
for each row
when (new.deptno=80)
  begin
      dbms_output.put_line('你好');
  end;
--调用（下列两句试比较不同）
insert into dept(deptno) values(90);
insert into dept(deptno) values(80);

该代码效果不再展示，留作思考之用