PL/SQL 训练08--触发器

--什么是触发器呢？
--一触即发，某个事件发生时，执行的程序块？
--数据库触发器是一个当数据库发生某种事件时作为对这个事件的响应而执行的一个被命名的程序单元

--适合场景
--对表的修改做验证
--数据库的自动维护
--通过不同粒度落实数据库活动的管理规则

--ORACLE中有五种不同类型的事件可以挂载触发器代码

--数据操作语句DML：数据表的增删改
--数据定义语句DDL：比如创建表，索引
--数据库事件：数据库启动或者关闭时触发
--INSTEAD OF：替代触发器，实际上是DML触发器的替代品，是在插入、更新、删除操作将要发生触发的
--我们的代码指定用什么操作来取代这些DML操作，控制的是视图而不是对表的操作
--挂起的语句：如果语句执行过程遇到了空间问题可以进入挂起模式，直到空间问题解决后继续运行

--DML触发器

--使用触发器前，使用哪些触发器，需要回答以下问题
--触发器应该是对整个DML语句触发一次，还是要为语句所涉及的每一行都触发一次
--触发器应该在整个语句开始之前还是结束之后，后者是对每一行记录处理之前或者之后触发
--触发器到底是由插入、更新、删除或者是某种组合触发的

--深入语法的学习之前，先了解下DML触发器有关的概念

--before触发器：在某个操作发生之前触发的
--after触发器:在某个操作发生之后触发的
--语句级别触发器：由整个SQL语句触发的，可能涉及一条或多条记录
--行级别触发器:针对的是SQL语句执行过程中操作到的每一条记录
--伪记录NEW：包含的是被操作的行修改之后的值，只有在更新操作或者插入操作的DML触发器中使用
--伪记录OLD：包含的是被操作的行修改之前的值，只有在更新操作或者删除操作的DML触发器中使用
--when子句：确定是否应该执行触发器的代码，避免不必要的执行

--事务的参与
--Dml触发器会参与到触发它们的事务中
--如果触发器发生了异常，这部分事务会回滚
--如果触发器本身也执行了DML语句，这个DML会同时成为主事务中的一部分
--不能在触发器中执行COMMIT或ROLLBACK语句，除非使用到了自治事务

--触发器的定义

CREATE [OR REPLACE]TRIGGER trigger_name
{before | after}
{insert|delete | update| update of column_list} on table_name
[for each row]
[when (...)]
[declare ...]
begin 

  ....
[exception]

end [trigger_name];
--trigger_name:触发器名字，表和触发器可以使用相同的名字，不过，一般要避免同名的冲突
--如果指定了FOR EACH ROW，则语句处理的每一行都会集合触发器，如果没有这句，就是语句级别的触发器

--例子，当更新用户表的积分字段时，检查积分值是否合理

create or replace trigger tri_update
  before update of user_point on ma_users
  for each row
begin
  if :new.user_point < 0 then
    raise_application_error(-20001, '积分数不能为零');
  end if;
end tri_update;
/
--更新操作前对积分做了判断，如果是负数则抛出异常，事务被自动回滚
update ma_users t
set  t.user_point = -100
where t.user_name ='乱世佳人';

--日志审计：插入用户表前，记录日志信息使用自治事务
CREATE OR REPLACE trigger tri_user_insert
  before insert on ma_users
  for each row
declare
  pragma autonomous_transaction;
begin
  insert into ma_user_log
    (user_name, trace_mark)
  values
    (:new.user_name, '新注册了一个用户');
  commit;
end tri_user_insert;
/

begin
  insert into ma_users
    (user_name, user_password, user_phone, user_email, user_birth_date)
  values
    ('触发器6', 'test1234', '', '[e-mail]', date '1988-10-01');

end;
/
--注意如果插入时报错了，触发器还是会被执行一遍的，如果是AFTER触发器呢
select * from ma_user_log;
--drop trigger tri_user_insert;
CREATE OR REPLACE trigger tri_user_insert2
  after insert on ma_users
  for each row
declare
  pragma autonomous_transaction;
begin
  insert into ma_user_log
    (user_name, trace_mark)
  values
    (:new.user_name, '新注册了一个用户2');
  commit;
end tri_user_insert2;
/
begin
  insert into ma_users
    (user_name, user_password, user_phone, user_email, user_birth_date)
  values
    ('触发器8', 'test1234', '233ss33', '[e-mail]', date '1988-10-01');

end;
/

--when 子句
--可以通过WHEN子句对触发器真正执行的时机进行微调

CREATE OR REPLACE trigger tri_user_insert
  before insert on ma_users
  for each row
when (to_char(sysdate,'HH24') between 9 and 14)
declare

begin
   :new.user_point
   := 100;
end tri_user_insert;
/
begin
  insert into ma_users
    (user_name, user_password, user_phone, user_email, user_birth_date,REGISTER_DATE)
  values
    ('触发器10', 'test1234', '233a343212', '[e-mail]', date '1988-10-01',sysdate);

end;
/

--使用WHEN子句需要注意的地方
--要把整个逻辑表达式封装到括号中
--不要在OLD和NEW之前加上:,这个冒号在触发器体的PL/SQL代码中是必须的，但不能出现在WHEN 子句中
--在when子句中，只能调用SQL的内置函数，不能调用用户的自定义函数或者那些定义在内置包中的函数
--确定要用这些函数，可以将逻辑移到触发器的执行单元的开始部分
--when子句只能用于行级触发器。如果在语句级别触发器使用，会遇到编译报错

--使用NEW和OLD伪记录
--每当触发行级触发器时，PL/SQL运行引擎会创建并且填充两个数据结构，功能上类似于记录
--OLD保留的是要处理记录的原始值，new代表的是新的值，跟%ROWTYPE声明的记录结构完全相同

create or replace trigger tri_update
  before update of user_point on ma_users
  for each row
  when (new.user_point - old.user_point > 1000)
begin
  dbms_output.put_line(:new.user_name || '新积分' || :new.user_point ||
                       '超过原积分' || :old.user_point || ' ' ||
                       (:new.user_point - :old.user_point));

end tri_update;
/

update ma_users t
set  t.user_point = 150000
where t.user_name ='乱世佳人';

--使用规则
--如果用于INSERT操作的触发器OLD结构没有任何数据
--如果用于UPDATE操作的触发器，OLD和NEW都会填充值
--如果用于DELETE操作的触发器，NEW结构没有任何数据
--new和OLD都包括ROWID位列，任何情况下，这个列在OLD和NEW中值相同
--不能修改OLD结构中的字段值，但可以修改NEW结构的字段值

create or replace trigger tri_update
  before update of user_point on ma_users
  for each row
  when (new.user_point - old.user_point > 1000)
begin
  :new.user_point := :new.user_point - :old.user_point;

end tri_update;
/

update ma_users t
set  t.user_point = 65000
where t.user_name ='乱世佳人';

select * from  ma_users t
where t.user_name ='乱世佳人';
--如果在触发器中调用了其它函数或者过程，不能把NEW和OLD两个结构作为记录参数传递
--只能传递伪记录中的个别字段
--在触发器的匿名块中使用NEW和OLD结构时，必须在两个关键字前面加上冒号
--不能对NEW和OLD结构执行记录级别的操作
 :new := null ;

--可以利用REFERENCING子句改变两个伪记录的名字
create or replace trigger tri_update
  before update of user_point on ma_users
  referencing old as ori_data new as new_data
  for each row
  when (new_data.user_point - ori_data.user_point > 1000)
begin
  :new_data.user_point := :new_data.user_point - :ori_data.user_point;

end tri_update;
/

--在触发器中确定DML操作
--INSERTING :如果触发器是由对表的插入引起的则返回TRUE，否则返回FALSE
--UPDATING :如果触发器是由对表的更新操作引起的返回TRUE，否则返回FALSE
--DELETING :如果触发器是由于对表的删除操作引起的，则返回TRUE否则返回FALSE

CREATE OR REPLACE TRIGGER tri_all
  before insert or delete or update on ma_users
  for each row
begin

  if inserting then
    dbms_output.put_line('插入了新用户' || :new.user_name);
  elsif updating then
    dbms_output.put_line('更新了用户' || :new.user_name);
    if updating('USER_POINT') THEN
      dbms_output.put_line('积分被更新了' || :new.user_point || '新-旧' ||
                           :old.user_point);

    END IF;
  elsif deleting then
    dbms_output.put_line('删除了用户' || :old.user_name);
  end if;
end tri_all;
/
delete  ma_users t
where t.user_name ='乱世佳人';

--如果对一个表的一个操作类型有多个触发器，建议写成一个触发器，便于维护
--但复杂的逻辑分拆的触发器可以减少解析和执行时间
create or replace trigger tri_update
  before update of user_point on ma_users
  for each row
  when (new.user_point between 10000 and 20000)
begin
  :new.USER_REMARK := '金牌会员';
end tri_update;
/
create or replace trigger tri_update1
  before update of user_point on ma_users
  for each row
  when (new.user_point < 10000)
begin
  :new.USER_REMARK := '普通会员';
end tri_update1;
/
create or replace trigger tri_update2
  before update of user_point on ma_users
  for each row
  when (new.user_point > 20000)
begin
  :new.USER_REMARK := '钻石会员';
end tri_update2;
/

create or replace trigger tri_update3
  before update of user_point on ma_users
  for each row
begin
  :new.USER_REMARK :=
  case
      when :new.user_point < 10000 then '普通会员'
      when :new.user_point between 10000 and 20000 then '金牌会员'
      when :new.user_point > 20000 then '钻石会员'
  end;
end tri_update3;
/
--触发器的执行顺序
---在ORACLE中可以通过使用FOLLOW子句来保证触发器的执行顺序
create or replace trigger tri_update
  before update of user_point on ma_users
  for each row
begin
  dbms_output.put_line('金牌会员');
end tri_update;
/
create or replace trigger tri_update1
  before update of user_point on ma_users
  for each row
begin
  dbms_output.put_line('普通会员');
end tri_update1;
/
drop trigger tri_update1;
create or replace trigger tri_update2
  before update of user_point on ma_users
  for each row
  follows tri_update1
begin
  dbms_output.put_line('钻石会员');
end tri_update2;
/

--通常情况下，行级触发器不应该去读或者写导致它被触发的表的内容
--这个约束只针对行级触发器
--突变表的错误

create or replace trigger tri_update
  before update of user_point on ma_users
  for each row
declare
  v_point number;
begin
  /*select max(m.user_point) into v_point from ma_users m;
  if :new.user_point > v_point / 2 then
    :new.user_point := v_point - 1000;
  end if;*/
  null;

end tri_update;
/

--复合触发器

--触发器多的话，管理不易
--11g开始，可以使用复合触发器，将行级和语句触发器放在同一个代码对象中

CREATE TRIGGER tri_compounder
  for insert or delete or update on ma_users compound trigger

v_global_var number := 1;

before statement is
begin
  dbms_output.put_line('compound before statement' || v_global_var);
  v_global_var := v_global_var + 1;
end before statement;

before each row  is
begin
  dbms_output.put_line('compound before each row' || v_global_var);
  v_global_var := v_global_var + 1;
end before each row;

after each row  is
begin
  dbms_output.put_line('compound after each row' || v_global_var);
  v_global_var := v_global_var + 1;
end after each row;

after statement is
begin
  dbms_output.put_line('compound after statement' || v_global_var);
  v_global_var := v_global_var + 1;
end after statement;

end tri_compounder;
/

--ddl触发器

CREATE [OR REPLACE]TRIGGER trigger_name
{before | after}{ddl event} on {database | schema}
[when (...)]
[declare ...]
begin
  ....
end [trigger_name];

--例子

create or replace trigger tri_create
  before create on schema
begin
    dbms_output.put_line('created something');
end;
/
drop table test;
create table test(user_name varchar2(1000));
create index idx_tp on test(user_name);
create or replace trigger tri_create
  before create on schema
begin
    dbms_output.put_line('created something '||ora_dict_obj_type||' named '|| ora_dict_obj_name);
end;
/
--可用属性
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
.....

--数据库事件触发器
--STRATUP
--SHUTDOWN
--SERVERERROR
--LOGON
--LOGOFF
--DB_ROLE_CHANGE

CREATE [OR REPLACE]TRIGGER trigger_name
{before | after}{DATABASE_EVENT} on {database | schema}
[when (...)]
[declare ...]
begin
....
end [trigger_name];

--INSTEAD OF 触发器
--INSTEAD OF触发器控制的是视图，可以让本来不可更新的视图变得可以更新

CREATE [OR REPLACE]TRIGGER trigger_name
instead of operation
on  view_name
for each row
begin
  ....
end [trigger_name];

-- instead of 后面可以跟上INSERT,UPDATE,MERGE或者DELETE来完成

create table ma_orders (id_ma_users varchar2(32),id_ma_orders varchar2(32),
order_no number(20),total_price number);

create table ma_order_items (id_ma_orders varchar2(32),
id_ma_order_items varchar2(32),product_name varchar2(100),product_num number,
product_ori_price number,product_new_price number);

--创建视图
create view ma_user_order as
select m.user_name,
       mo.total_price,
       moi.product_name,
       moi.product_num,
       moi.product_ori_price,
       moi.product_new_price
  from ma_users m, ma_orders mo, ma_order_items moi
 where m.id_ma_users = mo.id_ma_users
   and mo.id_ma_orders = moi.id_ma_orders;

create or replace trigger tri_view
  instead of insert on ma_user_order
  for each row
declare
  v_user_id  varchar2(32);
  v_order_id varchar2(32);
begin

  select t.id_ma_users
    into v_user_id
    from ma_users t
   where t.user_name = :new.user_name
     and rownum = 1;

  select sys_guid() into v_order_id from dual;
  insert into ma_order_items
    (id_ma_orders,
     id_ma_order_items,
     product_name,
     product_num,
     product_ori_price,
     product_new_price)
    select v_order_id,
           sys_guid(),
           :new.product_name,
           :new.product_num,
           :new.product_ori_price,
           :new.product_new_price
      from dual;

  insert into ma_orders
    (id_ma_users, id_ma_orders, total_price)
    select v_order_id,
           sys_guid(),
           :new.product_num * :new.product_new_price
      from dual;

end tri_view;
/

insert into ma_user_order(user_name,total_price,product_name,product_num,product_ori_price,product_new_price)
values ('乱世佳人',null,'书',2,100,80);

select * from ma_orders ;

select * from ma_order_items ;

--管理触发器
--启用、禁用、删除触发器
alter trigger TRI_USER_INSERT disable;
alter trigger trigger_name enable;
drop trigger trigger_name;

--创建一个禁用的触发器
CREATE TRIGGER TEST_TRI
BEFORE INSERT ON MA_ORDERS
DISABLE
BEGIN
  NULL ;
END ;
/
--查看触发器
select * from dba_triggers ;--数据库中的所有触发器

select * from all_triggers; --当前用户可见的全部触发器

select * from user_triggers --属于当前用户的全部触发器

--user_objects 查询触发器的有效性

select * from user_objects t where t.object_name ='TRI_USER_INSERT'

---------------------------------

1.已知有用户订单表，现在假设要举行一个活动，如果是在深夜1点-2点之间下单的，所购买的商品可以酌情打折，具体折扣可配置，请大家使用触发器实现这个功能

--配置表
create table discount_config(
created_by varchar2(100) default 'system' not null,
created_date date default sysdate not null,
updated_by  varchar2(100) default 'system' not null,
updated_date date default sysdate not null,
id_discount_config varchar2(32) default sys_guid() not null,
discount_starttime date,
discount_endtime  date,
discount_preriod varchar2(20),--hour,day,week,month,year
period_start number,
period_end number,
goods_name varchar2(32),
goods_id  varchar2(32),
discount_mode varchar2(20),--DISCOUNT,CASH_RELIEF
discount_value number,--0.9,10
discount_status number--0,1
--discount_type varchar2(20)--MULTIPLE,ONETIME
);
comment on table  discount_config is '商品打折配置表';
comment on column discount_config.discount_starttime is '打折开始时间';
comment on column discount_config.discount_endtime is '打折结束时间';
comment on column discount_config.discount_preriod is '打折周期类型:hour,day,week,month,year';
comment on column discount_config.period_start is '周期时间';
comment on column discount_config.period_end is '周期结束时间';
comment on column discount_config.goods_name is '打折商品名字';
comment on column discount_config.discount_mode is '打折方式:DISCOUNT 打折率 CASH_RELIEF 现金打折';
comment on column discount_config.discount_value is '打折值按比例或者现金值';
comment on column discount_config.discount_status is '折扣配置状态:0=disable,1=enable';
--comment on column discount_config.discount_type is '折扣类型:商品是一次打折还是多次打折';

insert into discount_config(discount_starttime,discount_endtime,discount_preriod,period_start,period_end,goods_name,goods_id,discount_mode,discount_value,discount_status）
values(sysdate-1,sysdate+10,'day',1,2,'iphone 6s 64g','2EAE613749B16DC9E053BD02A8C0DDDF','DISCOUNT',0.9,1);
insert into discount_config(discount_starttime,discount_endtime,discount_preriod,period_start,period_end,goods_name,goods_id,discount_mode,discount_value,discount_status）
values(sysdate-1,sysdate+10,'day',1,2,'iphone 6s 16g','2EAE613749B26DC9E053BD02A8C0DDDF','DISCOUNT',8.8,1);
commit;

select * from discount_config;
--触发器
create or replace trigger tri_order_dis
  before insert or delete or update on order_goods
  for each row
  declare
  v_sysdate date default sysdate;
  v_current_hour varchar(10):=to_char(sysdate,'HH24');
  v_period_start number;
  v_period_end number;
  v_goods_id varchar2(32);
  v_discount_mode varchar2(32);
  v_discount_value number;
  v_discount number default 0;
begin

  if inserting then

           begin
             select period_start,period_end,goods_id,discount_mode,discount_value
             into v_period_start,v_period_end,v_goods_id,v_discount_mode,v_discount_value
              from discount_config
              where :new.goods_id=goods_id and discount_status=1 and discount_preriod='day'
               and discount_starttime<=v_sysdate and discount_endtime>v_sysdate;
               v_discount :=1;
            exception
            when others then
             v_discount :=0;
           -- :new.goods_price
           end;  

          if    v_discount=1 and v_current_hour >=v_period_start and v_current_hour <v_period_end then
           :new.goods_price :=--:new.goods_price -
             case when v_discount_mode='DISCOUNT' then nvl(:new.goods_price*v_discount_value,0)
                     when v_discount_mode='CASH_RELIEF' then nvl(v_discount_value,0)
                     end ;

          end if;     

  end if;
end tri_order_dis;
/
--测试
insert into order_goods(id_order_goods,ORDER_ID,GOODS_ID,GOODS_NAME,GOODS_NUMBER,GOODS_PRICE)
values(sys_guid(),sys_guid(),'2EAE613749B16DC9E053BD02A8C0DDDF','iphone 6s 64g',1,6000)

commit
select * from order_goods;?