--表:t1(id number,name varchar2(10)); --表:t2(id number,name varchar2(10)); --在t1上建立触发器 CREATE OR REPLACE TRIGGER tr_t1 AFTER UPDATE ON t1 FOR EACH ROW --行级触发器 DECLARE --如果要声明变量,放在这儿 BEGIN --如果更新t1表的name字段 IF updating('name') THEN --插入t2表,:NEW.XX,代表该字段的更新值,引用旧值使用:old.XX INSERT INTO t2 (id, NAME) VALUES (:new.id, :new.name); END IF; END; /
create table goods( id number, name varchar2(15), price number(5,2) ) create table temp_goods( id number, name varchar2(15), price number(5,2) ) insert into goods select 10101,'大萝卜',1.02 from dual union all select 10112,'西红柿',2.00 from dual union all select 11001,'大白菜',0.58 from dual / -- create or replace trigger tri_price_change before update of price on goods--before触发器,当更新goods表的price列时激活此触发器 for each row when (new.price <> old.price)--条件是当新的价格与原来goods的价格不一样 begin --这里打印出被更新的goods的信息,以及新旧价格 dbms_output.put_line('goods_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 temp_goods(id,name,price) values(:old.id,:old.name,:old.price); end tri_change; / SQL> select * from goods;
ID NAME PRICE ---------- --------------- ------- 10101 大萝卜 1.02 10112 西红柿 2.00 11001 大白菜 0.58
SQL> set serveroutput on; SQL> update goods 2 set price=1.02 3 where id=11001 4 /
goods_id = 11001 old price = .58 new price = 1.02 the price has been changed
1 row updated
SQL> select * from goods 2 where id=11001;
ID NAME PRICE ---------- --------------- ------- 11001 大白菜 1.02
SQL> select * from temp_goods;
ID NAME PRICE ---------- --------------- ------- 11001 大白菜 0.58
SQL> select * from goods;
ID NAME PRICE ---------- --------------- ------- 10101 大萝卜 1.02 10112 西红柿 2.00 11001 大白菜 1.02
--创建触发器 trigger CREATE OR REPLACE TRIGGER tri_teacher AFTER INSERT OR UPDATE OR DELETE OF ID,sex ON teacher --格式 FOR EACH ROW --格式 --DECLARE --id int; BEGIN IF inserting THEN INSERT INTO teacher_test VALUES(:NEW.ID,:NEW.sex,'insert'); elsif updating THEN INSERT INTO teacher_test VALUES(:OLD.ID,:OLD.sex,'update'); elsif deleting THEN INSERT INTO teacher_test VALUES(:OLD.ID,:OLD.sex,'delete'); end if; END;
--表:t2(id number,name varchar2(10));
--在t1上建立触发器
CREATE OR REPLACE TRIGGER tr_t1
AFTER UPDATE ON t1
FOR EACH ROW --行级触发器
DECLARE
--如果要声明变量,放在这儿
BEGIN
--如果更新t1表的name字段
IF updating('name') THEN
--插入t2表,:NEW.XX,代表该字段的更新值,引用旧值使用:old.XX
INSERT INTO t2 (id, NAME) VALUES (:new.id, :new.name);
END IF;
END;
/
create table goods(
id number,
name varchar2(15),
price number(5,2)
)
create table temp_goods(
id number,
name varchar2(15),
price number(5,2)
)
insert into goods
select 10101,'大萝卜',1.02 from dual union all
select 10112,'西红柿',2.00 from dual union all
select 11001,'大白菜',0.58 from dual
/
--
create or replace trigger tri_price_change
before update of price on goods--before触发器,当更新goods表的price列时激活此触发器
for each row when (new.price <> old.price)--条件是当新的价格与原来goods的价格不一样
begin
--这里打印出被更新的goods的信息,以及新旧价格
dbms_output.put_line('goods_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 temp_goods(id,name,price)
values(:old.id,:old.name,:old.price);
end tri_change;
/
SQL> select * from goods;
ID NAME PRICE
---------- --------------- -------
10101 大萝卜 1.02
10112 西红柿 2.00
11001 大白菜 0.58
SQL> set serveroutput on;
SQL> update goods
2 set price=1.02
3 where id=11001
4 /
goods_id = 11001
old price = .58
new price = 1.02
the price has been changed
1 row updated
SQL> select * from goods
2 where id=11001;
ID NAME PRICE
---------- --------------- -------
11001 大白菜 1.02
SQL> select * from temp_goods;
ID NAME PRICE
---------- --------------- -------
11001 大白菜 0.58
SQL> select * from goods;
ID NAME PRICE
---------- --------------- -------
10101 大萝卜 1.02
10112 西红柿 2.00
11001 大白菜 1.02
--创建触发器 trigger
CREATE OR REPLACE TRIGGER tri_teacher
AFTER INSERT OR UPDATE OR DELETE OF ID,sex ON teacher --格式
FOR EACH ROW --格式
--DECLARE
--id int;
BEGIN
IF inserting THEN
INSERT INTO teacher_test VALUES(:NEW.ID,:NEW.sex,'insert');
elsif updating THEN
INSERT INTO teacher_test VALUES(:OLD.ID,:OLD.sex,'update');
elsif deleting THEN
INSERT INTO teacher_test VALUES(:OLD.ID,:OLD.sex,'delete');
end if;
END;