oracle触发器
oracle触发器
触发器:在执行insert,updatedalete语句时,
触发执行的一段plsql代码
可以在sql语句执行前触发,也可以在sql语句执行后触发,
还可以替换原sql语句只执行触发器代码
触发器管理
让触发器失效
alter trigger 触发器名 disable;
让触发器生效
alter trigger 触发器名 enable;
删除触发器
drop trigger 触发器名;
1表级触发器(语句级触发器)
表级触发器:一个sql语句只会触发一次触发器代码
语法
create trigger 触发器名称
before|alter insert[or delete[or update]] on 表名
begin
end;
给emp创建一个触发器,当给emp表插入数据时,打印'插入数据'
新建t1触发器
create trigger t1
before insert on emp
begin
dbms_output.put_line('插入数据');
end;
插入数据进行测试
insert into emp(empno,ename,deptno) values(7123,'asd',10);
rollback;
会输出
插入数据
尝试插入多条数据
insert into emp(empno,ename,deptno)
select 1234,'a',10 from dual
union all
select 2345,'b',10 from dual
union all
select 3456,'c',10 from dual;
rollback;
输出
插入数据
发现插入多条数据时,也是打印了一次
触发器中的变量
触发器中的三个变量,用来区分insert,update,delete语句的
| 变量名称 | 描述 |
|---|---|
| inserting: | 布尔类型的变量,当insert语句触发触发器时,返回true,否则返回false |
| updating: | 布尔类型变量,当update语句触发触发器时,返回true,否则返回false |
| deleting: | 布尔类型变量,当delete语句触发触发器时,返回true,否则返回false |
新建触发器
create or replace trigger t1
before insert or update or delete on emp
begin
if inserting then
dbms_output.put_line('插入数据');
end if;
if updating then
dbms_output.put_line('修改数据');
end if;
if deleting then
dbms_output.put_line('删除数据');
end if;
end;
输入
update emp set ename='slzd' where empno=7777;
输出
修改数据
输入
delete emp where empno in (9123,7777);
输出
删除数据
2行级触发器
行触发器:当表中一条数据改变时,触发一次触发器代码
语法
语法
create or replace trigger 触发器名
after|before insert [or update [or delete]] on
表名 for each row
declare
begin
end;
创建触发器
create or replace trigger t1
before insert or update or delete on emp for each row
begin
if inserting then
dbms_output.put_line('插入数据');
end if;
if updating then
dbms_output.put_line('插入数据');
end if;
if deleting then
dbms_output.put_line('删除数据');
end if;
end;
插入一条数据
insert into emp(empno,ename,deptno)
values(7123,'a',10);
输出
插入数据
插入多条数据
insert into emp(empno,ename,deptno)
select 7123,'a',10 from dual
union all
select 7124,'b',10 from dual
union all
select 7125,'c',10 from dual;
输出
插入数据
插入数据
插入数据
删除多条数据
delete from emp where empno in (1011,9123);
输出
删除数据
删除数据
行触发器中的两个变量
:old获取到数据当前记录,修改之前的数据:new获取修改后(即将要修改)的数据
create or replace trigger t1
before update on dept for each row
begin
--打印修改前的数据
dbms_output.put_line(:old.deptno||' , '||:old.dname||' , '||:old.loc);
--打印修改后的数据
dbms_output.put_line(:new.deptno||' , '||:new.dname||' , '||:new.loc);
end;
修改一条记录
update dept set dname='aa',loc='bb' where deptno=10;
输出
10 , ACCOUNTING , NEW YORK
10 , aa , bb
before和after区别
before在sql语句执行后触发after在sql语句执行后触发
create or replace trigger t3
before update on dept for each row
begin
--打印修改前的数据
dbms_output.put_line(:old.deptno||' , '||:old.dname||' , '||:old.loc);
--尝试对:new进行赋值
:new.dname:='a';
:new.loc:='b';
--打印修改后的数据
dbms_output.put_line(:new.deptno||' , '||:new.dname||' , '||:new.loc);
end;
修改一条记录
update dept set dname='aa',loc='bb' where deptno=10;
输出
10 , ACCOUNTING , NEW YORK
10 , a , b
发现在上面的例子中
使用before能够修改new的值
使用after时,不能修改new的值
为dept表实现日志记录
新建日志表
create table dept_log(
dt date,--数据的修改时间
opt varchar2(20), --操作(insert,update,delete)
old varchar2(100), --修改之前,源数据库的数据
new varchar2(100) --修改之后,数据库中的数据
);
为dept表创建一个触发器,记录dept表的修改日志
create or replace trigger log1
before insert or update or delete on dept for each row
declare
--声明一个变量保存操作
v_opt varchar2(20);
--声明一个变量保存修改前的数据
v_old varchar2(100);
--声明一个变量保存修改后的数据
v_new varchar2(100);
begin
if inserting then
v_opt:='insert';
v_old:=null;
v_new:=:new.deptno||' , '||:new.dname||' , '||:new.loc;
end if;
if updating then
v_opt:='update';
v_old:=:old.deptno||' , '||:old.dname||' , '||:old.loc;
v_new:=:new.deptno||' , '||:new.dname||' , '||:new.loc;
end if;
if deleting then
v_opt:='delete';
v_old:=:old.deptno||' , '||:old.dname||' , '||:old.loc;
v_new:=null;
end if;
insert into dept_log(dt,opt,old,new)
values(sysdate,v_opt,v_old,v_new);
end;
测试
SQL> insert into dept values(50,'a','b');
1 row inserted
SQL> update dept set dname='aa',loc='bb' where deptno=50;
1 row updated
SQL> delete from dept where deptno=50;
1 row deleted
查看dept_log表
SQL> select * from dept_log;
DT OPT OLD NEW
----------- --------- -------------------- -----------
2019/12/26 insert 50 , a , b
2019/12/26 update 50 , a , b 50 , aa , bb
2019/12/26 delete 50 , aa , bb
自增主键
创建一个序列
create sequence seq111
start with 41
increment by 1;
创建一个触发器给dept表自动添加主键
create or replace trigger t4
before insert on dept for each row
begin
:new.deptno:=seq111.nextval;
end;
测试
insert into dept(dname,loc)
values('aaa','ccc');
insert into dept(dname,loc)
values('aaa','ccc');
insert into dept(dname,loc)
values('aaa','ccc');
insert into dept(dname,loc)
values('aaa','ccc');
commit;
查看dept表
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
41 aaa ccc
42 aaa ccc
43 aaa ccc
44 aaa ccc
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
8 rows selected
注意如果执行插入语句报错,会导致序列空一个数:41,43,44
删除重复数据
delete from dept where deptno between 42 and 44;
如果很多条,重复数据,该如何删除呢
delete from dept t where rowid>
(select min(rowid) from dept where dname=t.dname
and loc=t.loc);
3替换触发器(行级触发器)
替换触发器:
只能使用在视图上,不能使用在表上
替换原有的sql语句,只执行触发器代码
语法
create or replace trigger 触发器名
instead of[insert [or update [or delete]]] on
视图名 for each row
declare
begin
end;
实现:修改多表查询视图,将修改内容在表中更改
创建emp和deptno表的视图
create view dept_e as select
empno,ename,job,mgr,hiredate,sal,comm,
dept.deptno,dname,loc
from dept,emp where dept.deptno=emp.deptno;
新建触发器
当对dept_e视图进行update操作时生效
create or replace trigger t5
instead of update on dept_e for each row
begin
--修改部门信息
update dept set
dname=:new.dname,loc=:new.loc where deptno=:new.deptno;
--修改员工信息
update emp set
ename=:new.ename,job=:new.job,mgr=:new.mgr,hiredate=:new.hiredate,
sal=:new.sal,comm=:new.comm,deptno=:new.deptno where empno=:new.empno;
end;
更改视图
SQL> update dept_e set ename='sun',hiredate=to_date('19990330','YYYY/MM/DD'),loc='qingdao'
2 where empno=1011;
1 row updated
查看更改的数据
SQL> select * from dept_e where empno=1011;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME LOC
----- ---------- --------- ----- ----------- --------- --------- ------ -------------- -------------
1011 sun CLERK 7902 1999/3/30 2384.25 300.00 10 ACCOUNTING qingdao
SQL> select * from emp where empno=1011;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
1011 sun CLERK 7902 1999/3/30 2384.25 300.00 10
SQL> select * from dept where deptno=10;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING qingdao
