舉幾個 trigger 例子...

create trigger quote_update
update of ptrade,volume on quote referencing old as pre new as post
for each row when(post.ptrade != pre.ptrade or post.volume != pre.volume)
(insert into quotewatch(id,pre_ptrade,post_ptrade,pre_volume,post_volume)
values(pre.id,pre.ptrade,post.ptrade,pre.volume,post.volume) ) ;


create trigger "informix".upqty_i update of quantity on "informix"
.items referencing old as pre_upd new as post_upd
for each row
(
insert into "informix".log_record (item_num,ord_num,username,
update_time,old_qty,new_qty) values (pre_upd.item_num ,pre_upd.order_num
,USER ,CURRENT year to fraction(3) ,pre_upd.quantity ,post_upd.quantity
));


create trigger "informix".ins_tri_testa insert on "informix".testa
referencing new as psot
for each row
(
execute procedure "informix".proc1() with trigger references
);


create trigger "informix".upd_trig_testa update on "informix".testa
referencing old as pre new as post
for each row
(
execute procedure "informix".proc1() with trigger references
);

create trigger "informix".sel_trig_testa select on "informix".testa

for each row
(
execute procedure "informix".proc1() with trigger references
);

create trigger "informix".del_trig_testa delete on "informix".testa
referencing old as prec
for each row
(
execute procedure "informix".proc1() with trigger references
);

create trigger "informix".upd_quotex update on "informix".quotex
referencing old as oldx new as newx
for each row
when ((newx.ptrade != oldx.ptrade ) )
(
execute procedure "informix".sp_quotey(newx.id ,newx.ptrade
,oldx.ptrade ));


create procedure "informix".proc1()
referencing old as prex new as postx for testa ;

if(inserting) then
insert into testb(fld1,fld2) values(postx.fld1,postx.fld2);
end if
if(updating) then
update testb set fld2 = postx.fld2 where fld1 = postx.fld1 ;
end if
if(deleting) then
delete from testb where fld1 = prex.fld1 ;
end if
if(selecting) then
insert into testa_select(fld1,fld2) values(prex.fld1,prex.fld2);
end if
end procedure;

全站熱搜

hedgezzz 發表在 痞客邦 留言(0) 人氣()