当前位置:首页 > 开发 > 数据库 > 正文

oracle 触发器 trigger

发表于: 2012-07-09   作者:blackproof   来源:转载   浏览:
摘要: oracle trigger   第一个demo是最简单的trigger的实例,是表触发器   -- unit one -- table trigger 最简单的表触发器,在table名为project表insert,update,delete时触发 create or replace trigger project_trigger_name after

oracle trigger

 

第一个demo是最简单的trigger的实例,是表触发器

 

-- unit one -- table trigger 最简单的表触发器,在table名为project表insert,update,delete时触发
create or replace trigger project_trigger_name
  after insert or update or delete on project
begin
  if to_char(sysdate, 'DY', 'nls_date_language=AMERICAN') in ('SAT', 'SUM') THEN
    dbms_output.put_line('having break now');
  else
    case
      when not INSERTING then -- other condition like deleting,inserting,updating
        dbms_output.put_line('go to work');
        else
          null;
    end case;
  end if;
end;
drop trigger project_trigger_name;

-- table trigger test
select * from project;
 insert into project(xmid, xmmc, xmbbh) values('test_20120706', '测试项目', 0);
delete from project pro
 where pro.xmid = 'test_20120706'
   and pro.xmbbh = 0;

 

第二个demo,是行触发器(for each),可以用old,new别名取当前操作行的新旧值,行触发器是每行触发一次

         并且添加行触发器的限制条件,如 when (old.xmmc = 'other name')

 

--unit two -- table row trigger 行触发器,
create or replace trigger project_row_trigger_name
  after update of xmmc, xmid on project
  for each row
  when (old.xmmc = 'other name') -- add limited condition for table row trigger
begin
  if inserting then
    dbms_output.put_line('dead');
  else
    dbms_output.put_line('have a life');
  end if;
  if :new.xmmc = :old.xmmc then
    -- use new and old can use value
    dbms_output.put_line('same name update');
  else
    dbms_output.put_line('different name update');
  end if;
end;
 
 -- table row trigger test
 update project set xmmc = 'other name' where xmid = 'test_20120706';
 

第三个demo,是无法运行的trigger,有错,因为在触发器里同时操作了触发条件的表project

 

 -- unit three -- wrong table row trigger test
 create or replace trigger project_trigger_error
   after update of xmmc, xmid on project
   for each row -- decorate each row
   when (old.xmmc = 'other name')
 declare
   maxmc varchar(100);
 begin
   -- operate the table,as update table -- operate conflict
   select max(xmmc)into maxmc from project; 
   dbms_output.put_line(maxmc);
 end;
 

 

第四个trigger 是 oracle特殊的instead of触发器

可以允许复杂视图进行DML操作:

 

 -- unit four -- 'instead of' trigger
 --test for update table and view, complicated view doesn't allow to do something DML operation.
 select * from v_lpromis_yxgl_khxx contract where contract.ID = 'id1';
 update v_lpromis_yxgl_khxx contract set contract.KHQC = '微软' where contract.ID = 'id1';
 
 select * from contract contract;
 update contract contract set contract.khqc = '微软' where contract.ID = 'id1';
 -- use 'instead of' trigger can do DML operation on complicated view
 create or replace trigger tr_instead_of_contract
   instead of update on v_lpromis_yxgl_khxx -- use instead of to replace after or befer
   for each row
 begin
 
   update contract contract
      set contract.khqc = :new.khqc
    where contract.ID = :new.id;
 
 end;
 -- test for update view after create 'instead of' trigger
  update v_lpromis_yxgl_khxx contract set contract.KHQC = '微软' where contract.ID = 'id1';
 

 

-- 目前还缺少系统事务,管理事务的demo,有时间一定补上来

oracle 触发器 trigger

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号