Oracle数据库之PLSQL触发器

vlukwhtojc

--=======触发器============
--是一段命名的PL/SQL代码块，只不过该代码块在特定的条件下被触发并且执行。
--对于这样的代码我们称之为触发器
--======触发器的分类=============
--通常根据触发条件以及触发级别的不同分为DML触发器，INSTEAD OF 触发器，系统事件触发器。
---DML触发器: ORACLE 对DML语句进行触发，
--可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。
---INSTEAD OF 触发器 :在ORACLE里，对于简单视图，可以直接使用DML进行操作，
--而复杂视图则不能直接使用DML，因此INSTEAD OF 触发器应运而生。
--INSTEAD OF 触发器主要是为解决复杂视图不能执行DML而创建。
---系统事件触发器  
--在 ORACLE 数据库系统的事件中进行触发,如ORACLE系统的启动与关闭等.
--使用系统触发器,便于系统跟踪,监测数据库变化情况等。
--============触发器的用途=========
--控制DDL语句的行为，如通过更改、创建或重命名对象
--控制DML语句的行为，如插入、更新和删除
--实施参照完整性、复杂业务规则和安全性策略
--在修改视图中的数据时控制和重定向DML语句
--通过创建透明日志来审核系统访问和行为的信息
--=======DML语句触发器==================
--组成部分描述可能值
--触发时间触发事件的时间顺序before,after
--触发事件DML语句是触发事件insert,update,delete
--触发器类型触发器被执行的次数statement,row
--触发器体该触发器将要执行的动作        完整的PLSQL块
--======DML触发器的类型===========
--语句级触发器
--行级触发器 (for each row)
--区别:触发的次数不同,如果DML语句影响1行，那么两种效果一样
--如果影响多行，行级触发次数比语句级触发次数多.
--=========DML触发器的触发顺序===============
--在单行数据上的触发顺序(触发代码仅被执行一次)
---BEFORE 语句级触发器
------BEFORE 行级触发器
------AFTER 行级触发器
-- AFTER 语句级触发器
--.在多行数据上的触发顺序(语句级触发器仅被执行一次，行级触发器在每个作业行上被执行一次)
--BEFORE 语句级触发器
------BEFORE 行级触发器
------AFTER 行级触发器
----------BEFORE 行级触发器
----------AFTER 行级触发器
--AFTER 语句级触发器
--==创建触发器的语法
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}     
--定义触发类型，即那一种或多种DML以及特定的列
{INSERT | DELETE | UPDATE [OF column [, column …]]}  
--特定的触发对象，表或视图
ON {[schema.] table_name | [schema.] view_name}                       
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
--定义触发器为行级触发器
[FOR EACH ROW ]                                                      
[WHEN condition] --满足条件才会触发
BEGIN
trigger_body;
END;
--=====在行级触发器中使用被插入、更新或删除的记录中的列值，(只能用在行级触发)
---可以使用NEW和OLD限定符来表示
--- :old 修饰符访问操作完成前列的值
--- :new 修饰符访问操作完成后列的值
---============================================
--触发事件 ：old.id:new.id
--insert ：old.id结果 为null数据库会把  的值的值插入id列
--update在update之前的值update之后的新值
--deletedelete之前的原始值字段null
--需求:完成sequence的调用( 自动生成主键)
--准备表:
create table t1 (id number(4) ,c1 number(4));
create sequence s;--创建系列s
create or replace trigger  get_pk
--在insert , t1表之前触发
before insert  on t1 for each row  --每一行都被触发
declare
begin
-- :new是个记录类型的变量，结构类似于表结构，每条新插入的记录存储在其中。
--定义行级的before insert触发器，在插入记录之前，改变：new里的值，从而实现自动产生主键值。
select s.nextval into :new.id from dual;
dbms_output.put_line(':new.id'|| :new.id);-- 1,2,3,4,
dbms_output.put_line(' :old.id'|| :old.id); -- 没有
end;
--执行 会触发 get_pk触发器.
insert into t1 values (100,100);
insert into t1 values (100,200);
insert into t1 values (100,300);
--再次查询:select * from t1 ;
-- id  c1
-- 1  100
-- 2  200
-- 3  300
--==== update :new , :old测试
create or replace trigger up_pk
before update on t1 for each row
declare
begin
dbms_output.put_line('update :new .c1'|| :new.c1);--400
dbms_output.put_line('update :old.c1'|| :old.c1); -- 300
end;
-- update:触发up_pk触发器
update t1 set c1 = 400 where id = 3;
-- :new.c1 400 ,   :old.c1 300
--==== 测试delete
create or replace trigger de_pk
before delete on t1 for each row
declare
begin
dbms_output.put_line('delete :new .c1'|| :new.c1);--没有
dbms_output.put_line('delete :old.c1'|| :old.c1);--400
end;
-- delete:触发de_pk触发器
delete from  t1  where id = 3;
-- :new.c1 没有 ,   :old.c1 400

--=========跟记录更新t1表的时间.
-- c2存放修改前的值, c3存储修改后的值
create table t_log (c1 varchar2(30),c2 number(4),c3 number(4) , c4 date);
--在t1表上创建触发器
-- for each row : update一行就触发1次.update 2行就触发2次
create or replace trigger update_log
before update on t1 for each row
-- before update on t1 这里有错, :old.c1, :new.c1不能用在表级触发器
declare
begin
insert into t_log values (' Before update on t1 ',:old.c1, :new.c1 , sysdate);
end;

-- 测试 ,行级 ,表级触发的区别
create table t_log (c1 varchar2(30),c4 date);
--在t1表上创建触发器
-- update 1行触发1次，update 2行 也是触发1次
create or replace trigger update_log
-- before update on t1 for each row
before update on t1
declare
begin
insert into t_log values (' Before update on t1', sysdate);
end;
--== when限制条件的行级触发器
create or replace trigger update_log
before update on t1 for each row
when ( old.id = 1) -- 当满足这个条件的时候触发
declare
begin
insert into t_log values (' Before update on t1', sysdate);
end;
-- == of的使用
create or replace trigger update_log
before update on t1 of id  for each row --表示当id 被update时 才会被触发
when ( old.id = 1) -- 当满足这个条件的时候触发
declare
begin
insert into t_log values (' Before update on t1', sysdate);
end;

-- ==对于复杂的数据完整性，参照完整性，可以通过DML触发器来完成普通约束所不能完成的任务
-- 级联更新
CREATE OR REPLACE TRIGGER update_cascade
AFTER DELETE OR UPDATE
OF deptno ON dept FOR EACH ROW
BEGIN
-- updaing :修改了返回true ,否则 false
--:old.deptno<>:new.deptno :修改前的值 不等于 修改后的值
IF (UPDATING AND :old.deptno<>:new.deptno) THEN
UPDATE emp SET deptno=:new.deptno WHERE deptno=:old.deptno;
END IF;
-- deleting :删除true ,否false;
IF DELETING THEN
DELETE FROM emp WHERE deptno=:old.deptno;
END IF;
END;
--查看触发器 名字，状态
select trigger_name,status from user_triggers where table_name='UPDATE_CASCADE';
--查看触发器源码
select line,text from user_source where name='TR_DEL_UPD_DEPTNO';
--禁用触发器
--当触发器被禁用后，则表上的DML操作将不会触发该触发器，
--直到该触发器被解除禁用(alter trigger trigger_name disable)
alter trigger tr_emp_sal disable;
--启用触发器
-- 被禁用的触发器可以被解除禁用(alter trigger trigger_name enable)
alter trigger tr_emp_sal enable;
-- 禁用、启用表上的所有触发器
alter table emp disable all triggers;
alter table emp enable all triggers;
--重新编译触发器
alter trigger tr_emp_sal compile;
-- 删除触发器
drop trigger tr_emp_sal;