触发器——PL/SQL（八）

触发器

什么是触发器？

我们生活就有许多触发器，比如：灯的开关，手枪扳机。。。

而数据库的触发器与表相关联的、存储的pl/sql程序。每当一个特定的数据操作语言在指定的表中发出时，Oracle自动执行触发器中定义的语句序列。

-- 触发器可用于
1、数据确认
2、实施复杂的安全性检查
3、做审计，跟踪表上所做的数据操作等
4、数据的备份和同步
-- 触发器分类
1、DML触发器：
	表级触发器（语句级触发器）
	行级触发器
	instead of 触发器
2、模式（DDL）触发器
3、数据库级触发器

创建触发器的语法

create or replace trigger 触发器名
before | after
delete [or] | insert[or] | update [of 列名]
on 	表名
[for each row][where(条件)]
declare
	....
begin
	PLSQL块
end;


for each row 作用时标注此触发器是行级触发器；没有标注就是语句级触发器

在触发器中触发语句与为记录变量的值

触发语句	:old	:new
insert	所有的字段都为空（null）	将要插入的值
update	更新以前该行的值	更新后的值
delete	删除以前该行的值	所有字段都为空（null）

案例

表级触发器

create or replace trigger tri1
before insert on dept --给这个表插入数据之前
begin
	dbms_output.put_line('触发器执行了');
end;

-- 查看触发器
select * from user_objects where object_type='TRIGGER';

-- 如何执行触发器，在特定的事件发生的时候执行
insert into dept values(50,'财务部','北京');--执行
update dept set loc='上海' where deptno=50;--不会执行
delete from dept where deptno=50;-- 不会执行

-- 如果我要实现增删改都要触发就这么做
create or replace trigger tri1
before insert or update or delete on dept --给这个表插入数据之前
begin
	dbms_output.put_line('触发器执行了');
end;


-- 也可以指定列名
create or replace trigger tri1
before insert or update of loc,dname or delete on dept --给这个表插入数据之前
begin
        dbms_output.put_line('触发器执行了');
end;

update dept set deptno=52 where deptno=50;--不会执行


before:表示在sql语句执行前，执行触发器的代码
after:表示在sql语句执行之后，执行触发器的代
以下三个都是boolean类型的
updating:如果触发这个触发器的是一条update语句，它值就是true
deleting:如果触发它是一条delete语句，它值就是true
inserting:如果触发它是一条insert语句，它值就是true

create or replace trigger tri1
before insert or update or delete on dept --给这个表插入数据之前
begin
	if updating then
		dbms_output.put_line('触发器执行了--改');
	end if;
	if deleting then
		dbms_output.put_line('触发器执行了--删');
	end if;
	if inserting then
		dbms_output.put_line('触发器执行了--增');
	end if;
end;
insert into dept values(50,'财务部','北京');
update dept set loc='上海' where deptno=50;
delete from dept where deptno=50;


-- 删除触发器
drop trigger 触发名;

行级触发器

对DML语句修改的每个行执行一次,有for each row语句，在begin代码段中可以使用:new和:old。
:new --是 一个引用最新的列值
:old --是 一个引用以前的列值
-- 为dept表创建行级触发器
create or replace trigger tri2
before update or delete or insert
on dept for each row
begin
	if updating then
		dbms_output.put_line('修改了');
	end if;
	if deleting then
		dbms_output.put_line('删除了');
	end if;
	if inserting then
		dbms_output.put_line('增加了');
	end if;
end;

insert into dept values(88,'技术部','北京');
update dept set loc='销售部' where deptno=56 or deptno=57;
delete from dept where deptno=50;

:new --是 一个引用最新的列值
:old --是 一个引用以前的列值
create or replace trigger tri2
before update or delete or insert
on dept for each row
begin
	if updating then
		dbms_output.put_line('修改了'||:old.dname||:new.dname);
	end if;
	if deleting then
		dbms_output.put_line('删除了'||:old.dname||:new.dname);
	end if;
	if inserting then
		dbms_output.put_line('增加了'||:old.dname||:new.dname);
	end if;
end;
insert into dept values(59,'技术部','广州');
update dept set dname='公关部' where deptno = 59 or deptno = 52;
delete from dept where deptno = 59;


before:表示在sql语句执行前，执行触发器的代码（前置）
create or replace trigger tri2
before update or delete or insert
on dept for each row
begin
	if updating then
		dbms_output.put_line('修改了'||:old.dname||:new.dname);
	end if;
	if deleting then
		dbms_output.put_line('删除了'||:old.dname||:new.dname);
	end if;
	if inserting then
		--dbms_output.put_line('增加了'||:old.dname||:new.dname);
		if :new.deptno <80 then
			:new.deptno:=80;
		end if;
	end if;
end;
insert into dept values(60,'技术部','广州');


after:表示在sql语句执行之后，执行触发器的代码（后置）
create or replace trigger tri2
after update or delete or insert
on dept for each row
begin
	if updating then
		dbms_output.put_line('修改了'||:old.dname||:new.dname);
	end if;
	if deleting then
		dbms_output.put_line('删除了'||:old.dname||:new.dname);
	end if;
	if inserting then
		--dbms_output.put_line('增加了'||:old.dname||:new.dname);
		if :new.deptno <80 then
			:new.deptno:=88;
		end if;
	end if;
end;


--一般我们都会用前置触发器
-- 可以这么玩：
-- 可以生成主键值使用的序列

create table seqtab(
	id number(11) primary key,
    name varchar2(30)
);
-- 创建序列
create sequence seq start with 1 increment by 1;
-- 查看下一个序列
select seq.nextval from dual;
-- 正常添加
insert into seqtab(id,name) values(seq.nextval,'tom');

create or replace trigger tri3
before insert on seqtab for each row
begin
	:new.id := seq.nextval;
end;

insert into seqtab(name) values('刘德华'); 

替换触发器

替换触发器使用在视图上，而且是行级的触发器。

视图的修改是有限制的：
1、视图的修改还是基于基表的
2、有约束限制不能修改
3、有聚合函数
4、有表达式，伪劣。（有的是表达式计算的）
5、复杂视图（多表联合查询的视图）键值保存表的列可以修改，非键值保存表不能修改。
-----------以下为键值保存表和非键值保存表的理解--------------------
-- 什么是非键值保存表？
create table mytable1(
	id number,
    name varchar2(100)
);
insert into mytable1 values(1,'张三');
insert into mytable1 values(2,'李四');
select * from mytable1;

create table mytable2(
	a_id number,
    address varchar2
);
insert into mytable2 values(1,'北京');
insert into mytable2 values(2,'上海');
select * from mytable2;
-- 创建视图
create or replace view myView
as select * from mytable1,mytable2
where mytable1.id = mytable2.a_id;
-- 查看视图
select * from myView1;
-- 普通的表，没有主键约束，不能修改的，都是非键值保存表
update myView set name = 'abc' where id = 1;

-- 键值保存表
create table mytable3(
	id number primary key,
    name varchar2(100)
);
insert into mytable3 values(1,'张三');
insert into mytable3 values(2,'李四');
select * from mytable3;


create table mytable4()
	a_id number primary key,
    address varchar2(100),
    m_id number
);
insert into mytable4 values(1,'北京',1);
insert into mytable4 values(2,'上海',2);
insert into mytable4 values(3,'广州',1);
insert into mytable4 values(4,'深圳',2);
select * from mytable3;

-- 创建视图
create or replace view myView2
as
select * from mytable3,mytable4 where mytable3.id=mytable4.id;
-- 查看视图
select * from myView2;--查询出来的id,name 为非键值保存列。
-- 在试图中可以修改键值保存表的列
update myView2 set address = 'a' where a_id=1;
-- 在视图中无法修改非键值保存表的列
update myView2 set name = 'abc' where a_id=1;

-----------以上为键值保存表和非键值保存表的理解---------------------
/*
替换触发器使用在视图上，当视图不能修改，使用这个触发器可以完成修改。
而且是行级的触发器

语法：
  create or replace trigger 触发器名 
  instead of insert or update or delete on 视图名 for each row
  begin
  end;
*/
-- 准备工作
-- 创建视图
create view de 
as 
select d.deptno dno, d.dname,d.loc,e.* from dept d,emp e where d.deptno=e.deptno;
-- 查询视图
select * from de;
-- 完成修改视图，把empno为7369的dname修改为财务部（改不了）
update de set dname = '财务部' where empno=7369;

-- 创建替换触发器（instead of 触发器）
create or replace trigger tri5
instead of update on de for each row
begin
	dbms_output.put_line('替换触发器执行了');
end;
-- 再次执行的时候，就不会报错了，触发器执行了，但是并没有更新成功
update de set dname = '财务部' where empno=7369;
-- 需要这个修改触发器
-- 创建替换触发器（instead of 触发器）
create or replace trigger tri5
instead of update on de for each row
	v_deptno dept.deptno%type;
begin
	dbms_output.put_line('替换触发器执行了');
	-- 根据7369查询部门编号
	select deptno into v_deptno from emp where empno = :new.empno;
	dbms_output.put_line(v_deptno);
	-- 更改dept表的数据
	update dept set dname = :new.dname where deptno = v_deptno;	
end;
/*
注意：触发器的代码中不能出现 commit，rollback，savapoint
*/

模式触发器（作为了解）

在模式中执行DDL语句时执行。

-- 创建表
CREATE TABLE dropped_obj (
obj_name VARCHAR2(30),   
obj_type VARCHAR2(20),
   drop_date DATE
);
select * from dropped_obj;
-- 创建触发器
CREATE OR REPLACE TRIGGER 触发器名字
before ALTER or DROP or CREATE ON SCHEMA
BEGIN
  INSERT INTO dropped_obj   VALUES(ORA_DICT_OBJ_NAME, 
  ORA_DICT_OBJ_TYPE, SYSDATE);
END;
create table table1(
	a number
);
create table table2(
	a number
);


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

数据库级触发器（作为了解）

在发生打开、关闭、登录和退出数据库等系统事件时执行

-- 创建一张表
create table event_table(
	event varchar2(30),
    time date
);
select * from event_table;
-- 数据库启动触发器
create or replace trigger tr_startup
after startup on database
begin 
   insert into  event_table values(ora_sysevent, sysdate);
end;
-- 现在直接查看就查不出来的，因为我们的数据库是一只打开的

-- 利用cmd命令关闭再打开
sqlplus sys/sys as sysdba;--登录
shutdown immediate;-- 关闭
startup;--开启


--用户登陆触发器
-- 创建表
create table log_table(
    username  varchar2(20), 
    logon_time  date
);

create or replace trigger tr_logon
after logon on database
begin 
   insert into log_table(username,logon_time) values(ora_login_user,  sysdate);
end;
-- 为了方便 我用cmd登录
sqlplus scott/scott
注：启用、禁用和删除触发器
	启用和禁用触发器
		ALTER TRIGGER 触发器名字 DISABLE;
		ALTER TRIGGER 触发器名字 ENABLE;
	删除触发器
		DROP TRIGGER aiu_itemfile;