触发器 对商品订单管理 简单分析

最近忙面试，因为准备没充分 问到触发器，表达能力欠缺，因此 准备一下了。

作为触发器 DML操作触发器而言 简单明了 对于创建后 表增删改 触发它

下面就是我的小案例，

 --订单管理

 --创建库存表
 create table kucun
 (
        id number not null primary key,
        isHas number
 )
 --创建订单表
 create table dingdan
 (
        id number not null primary key,
        ddid number not null,
        shopname varchar2(20),
        shuliang number,
        money number
 )
 insert into kucun values(1,1000);
 insert into kucun values(2,1000);
 insert into kucun values(3,1000);

 --创建id自增
 create sequence id_shangpin;
 
 select * from kucun;
 select * from dingdan;
 --创建订单库存触发器
 create or replace trigger jian_ku_cun
 --插入 在 dingdan表之后处理
  before insert on dingdan
  --行级触发器
  for each row
    --定义参数
    declare
    ids number;--库存id
    v_shuliang number;--订单中数量 库存需减去                       
    begin
      v_shuliang := :new.shuliang;
      ids := :new.ddid;
      dbms_output.put_line('订单数量：'||v_shuliang||' 库存id：'||ids);
      update kucun set isHas =isHas-v_shuliang where id=ids;
    end;
   --执行 触发
 insert into dingdan values(id_shangpin.nextval,1,'m4a1',3,11);

好了 如果还不懂 就看看以下 慢慢来别急

1、触发器

   (1)什么是触发器:触发器(trigger)是一种特殊类型的PL/SQL块。

   (2)触发器的理解:触发器类似于函数和过程,也具有声明部分,执行部分和异常处理部分;

    触发器作为oralce对象存储在数据库中,在事件发生时被隐式触发,而且 触发器不能接收参数,不能像过程一样显式调用;

   (3)触发器的类型

      . DML触发器:触发事件有insert,update和delete;(可以分为before与after触发器)

      . 替代触发器:又称为instead of触发器;替代触发器代替数据库视图上的DML操作;

      . 系统触发器:分为数据库级(Database)和模式级(schema)两种;

      . DDL触发器:即由DDL语句(create,alter,drop)触发的触发器;

    (4)创建触发器语法
 create [or replace] trigger trig_name

      {before | after | instead of} trigger_event

      on {table_name | view_name}

      [for each row]

      begin

    trigger_body;

      end trigger_name;

      . trigger:表示创建触发器对象
      
      . trigger_name:触发器名称

      . before | after 表示在事件发生之前触发还是事件发生之后触发

      . trigger_event 指定引起触发器运行的触发事件

      . for each row 可选,表示触发器是行级触发器,如果没有此选项,则默认为语句级触发器;

    (5)管理触发器

      . 查看当前用户触发器: select trigger_type,trigger_name from user_triggers;

      . 改变触发器的状态: alter trigger trigger_name enabled(有效) | disabled(无效);

      . 删除触发器: drop trigger trigger_name;

2、语句级触发器

    (1)语句级触发器:是指当执行DML语句时被隐含执行的触发器;

    (2)建立before语句级触发器

       create or replace trigger trig_name

       before insert or update or delete on table_name  --DML语句执行前触发

       begin

      ......

       end;

    (3)使用触发器的条件谓词

       . 使用触发器的条件谓词从而区分具体的触发事件

       . inserting : 当触发事件是insert操作时;

       . updating  : 当触发事件是update操作时;

       . deleting  : 当触发事件是delete操作时;

    (4)触发器的新值和旧值

       . old关键字指数据操作之前的旧值,new关键字指数据操作之后的新值;

       . old和new关键字在使用时必须在其前面加上冒号(:);

       . old值只对update和delete操作有效,对insert操作无效;

       . new值只对update和insert操作有效,对delete操作无效;

       . old和new关键字只能用于行级触发器(for each row),不能用于在语句级触发器;

         因为在语句级触发器中一次触发涉及许多行,无法指定是哪一个新旧值;

3、行级触发器

   (1)创建触发器时,如果使用了for each row则表示该触发器为行级触发器;

   (2)行级触发器和语句级触发器区别在于:

      . 行级触发器在执行DML操作时,每作用一行就触发一次

   (3)创建before行级触发器

   (4)创建after行级触发器

   (5)限制行级触发器

4、instead of 触发器

   (1)instead of触发器的必要性

      . 普通视图可以直接使用DML实现,影响的是基表

      . 但是对于复杂视图则不允许直接使用DML进行操作,
      
        这时就可以考虑使用instead of触发器实现

   (2)对于复杂视图符合以下情况,则不能直接执行DML操作

      . 具有集合操作符(union,union all)

      . 具有分组函数(min,max,sum,avg,count)

      . 具有group by

      . 具有distinct关键字

      . 具有连接查询

   (3)建立instead of触发器有以下注意事项

      . instead of 选项只适用于视图

      . 当基于视图建立触发器时,不能指定before和after选项

      . 在建立视图时不能指定with check option选项

      . 在建立instead of触发器时,必须指定for each row选项

--1、触发器
--(1)触发器举例:当在emp表中删除记录后进行触发
create or replace trigger trig_del
after delete on scott.emp
declare
  v_count number;  
begin
  select count(*) into v_count from scott.emp;
  dbms_output.put_line(v_count);
end;

--通过删除来触发以上定义的触发器
delete from scott.emp where empno =6666;
update emp set ename = '哈哈' where empno = 6666


--查看当前用户的所有触发器
select * from user_triggers


--(2)建立before语句级触发器
--问题:建立before语句级触发器实现禁止工作人员在休息日改变雇员信息
create or replace trigger trig_update
before update or insert or delete on emp
begin
  --判断今天是否是周末
  if to_char(sysdate,'DY') in ('星期六','星期日') then
    --throw new Exception("错误信息");
    --自定义错误异常
    raise_application_error(-20001,'周末不能修改数据');
    dbms_output.put_line('周末不能修改数据');
  end if;
end;

--注意触发trig_sec_emp触发器的条件必须为周六或周日否则不成功
update emp set ename = '哈哈' where empno = 6666

--删除触发器
drop trigger trig_update


--(3)触发器中使用条件谓词
create or replace trigger trig_conding
before  insert or update or delete
on emp
begin
  if to_char(sysdate,'DY') in ('星期六','星期日') then
    --throw new Exception("错误信息");
    --自定义错误异常
    --条件谓语判断
    case
      --inserting:新增
      when inserting then
        raise_application_error(-20001,'周末不能新增数据');
      --updating：修改
      when updating then
        raise_application_error(-20001,'周末不能修改数据');
      --deleting：删除
      when deleting then
        raise_application_error(-20001,'周末不能删除数据');
    end case;
  end if;

end;

--测试新增，删除

drop trigger tri_old_new

--(4)触发器中的新值与旧值
create or replace trigger tri_old_new
before update on emp
for each row
declare
  newvalue varchar2(20);
  oldvalue varchar2(20);
begin
  --如何得到新值和旧值  :old   :new
  oldvalue:= :old.ename;
  newvalue:= :new.ename;
 
 
  dbms_output.put_line(newvalue||oldvalue);
end;
select * from emp;
--执行修改操作
update emp set sal = 8000 where empno = 7369


--(5)行级触发器
--(5.1)创建before行级触发器
--问题:使用before行级触发器,确保雇员工资不低于其原有工资
create or replace trigger trig_sal
before update on emp
for each row
begin
  if :new.sal < :old.sal then
    raise_application_error(-20001,'不干了，跳槽了');  
  end if;

end;
--(5.2)创建after行级触发器
drop table emp_sal_change
create table emp_sal_change
(
  empname varchar2(10),
  oldsal number(8,2),
  newsal number(8,2),
  chtime date
)
select * from emp_sal_change
--问题:创建after行级触发器备份雇员工资变化和雇员工资的更新日期
create or replace trigger trig_sal_b
after update on emp
for each row
declare
  v_count number;
begin
  select count(*) into v_count from emp_sal_change
   where empname = :old.ename;
   
  --备份数据
  if v_count = 0 then
     insert into emp_sal_change values
   (:old.ename,:old.sal,:new.sal,sysdate);
  else
    update emp_sal_change set oldsal = :old.sal,newsal = :new.sal
    where empname = :old.ename;
  end if;
 
 
end;

select * from emp;

--问题:限制行级触发(满足条件的才触发)
--问题:创建after行级触发器备份雇员工资变化和雇员工资的更新日期，限制job只能是SALESMAN
create or replace trigger trig_sal_b
after update on emp
for each row
--限制触发器的条件
when (old.job = 'SALESMAN')
declare
  v_count number;
begin
    dbms_output.put_line('触发了');
  select count(*) into v_count from emp_sal_change
   where empname = :old.ename;
   
  --备份数据
  if v_count = 0 then
     insert into emp_sal_change values
   (:old.ename,:old.sal,:new.sal,sysdate);
  else
    update emp_sal_change set oldsal = :old.sal,newsal = :new.sal
    where empname = :old.ename;
  end if;
 
 
end;

select * from emp;
update emp set sal = 8000 where empno = 999


--(6)instead of 触发器
--普通视图
create or replace view view_emp
as
       --写查询语句构建视图结构
       select * from emp;
       
select * from view_emp

insert into view_emp values
(66,'怕啊','test',22,sysdate,2000,300,20)


--复杂视图
create or replace view view_emp
as
       --写查询语句构建视图结构
       select t1.empno,t1.ename,t2.deptno,t2.dname from emp t1 inner join dept t2
       on t1.deptno = t2.deptno;

select * from view_emp



insert into view_emp values (12,'test1234',50,'CEO')

--对于vw_dept_emp复杂视图则不能直接使用DML进行操作,此时就考虑使用instead of触发器实现
create or replace trigger trig_view
instead of
insert on view_emp
for each row
  declare
    v_count number;
begin
  select count(*) into v_count from emp where ename = :new.ename;
 
  if v_count = 0 then
    insert into emp (empno,ename) values (:new.empno,:new.ename);
  end if;
 
  select count(*) into v_count from dept where deptno = :new.deptno;

  if v_count = 0 then
    insert into dept (deptno,dname) values (:new.deptno,:new.dname);
 
  end if;
end;
select * from emp;
select * from dept;
--语句级与行级触发器的区别
drop table back_emp;
create table back_emp
(
  empno number primary key,
  empjob varchar2(10),
  empaddress varchar2(20),
  empsal number
)
insert into back_emp values(1000,'CEO','HuNan ChangSha',2000);
insert into back_emp values(1001,'SALE','HuNan XiangTan',7000);
insert into back_emp values(1002,'MANAGER','HuNan ZhuZhou',5000);
commit;

select * from back_emp;
--语句级触发器
create or replace trigger trig_back
after delete on back_emp
begin
  dbms_output.put_line('语句触发器执行了');
end;


--行级触发器
create or replace trigger trig_back_row
after delete on back_emp
for each row
begin
  dbms_output.put_line('行级触发器执行了');
end;

--做删除，分别触发
delete from back_emp;

--总结：行级触发器只要满足条件的语句，那么每一条都会触发一次
--      语句触发器满足条件只会触发一次