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,有时间一定补上来