create table vegetable(
id number,
name varchar2(15),
price number(5,2),
p_date date
)
create table price_change(
id number,
name varchar2(15),
old_price number(5,2),
chg_date date
)
第二步: 向表中添加测试数据
insert into vegetable
select 1001,'西红柿',2.02,to_date('2010-05-10 06:00:00','yyyy-mm-dd hh24:mi:ss') from dual union all
select 1004,'大葱',5.50,to_date('2010-06-18 12:05:56','yyyy-mm-dd hh24:mi:ss') from dual union all
select 1102,'胡萝卜',3.05,to_date('2010-06-01 00:10:00','yyyy-mm-dd hh24:mi:ss') from dual union all
select 1150,'大白菜',0.55,to_date('2010-07-08 15:20:00','yyyy-mm-dd hh24:mi:ss') from dual;
commit;
第三步: 创建触发器
create or replace trigger tri_price_chg
before update or delete or insert of price on vegetable
for each row when(new.price <> old.price)
begin
if inserting then ...
elsif deleting then ...
end;
dbms_output.put_line('vegetable id = '|| :old.id);
dbms_output.put_line('old price = '|| :old.price);
dbms_output.put_line('new price = '|| :new.price);
dbms_output.put_line('the price has been changed');
insert into price_change(id,name,old_price,chg_date)
values(:old.id,:old.name,:old.price,sysdate);
end tri_price_chg;
第四步: 修改数据做测试
update vegetable set price=1.55 where id=1001;
第五步: 验证效果:
select * from vegetable;
--vegetable中的数据被更新
--price_change表中通过触发器的操作记录了旧的价格数据.
与大家共享......