这个文章演示了oracle触发器的使用,以备不时之需
-- 创建一个表,未来需要在这个表上建立触发器
create table baby_test
(id number(20),
name varchar(50),
birthday date,
login_date timestamp
);
-- 创建一个保存触发器操作的表
-- drop table baby_test_log;
create table baby_test_log
(id number(20),
name varchar(50),
insert_date date default sysdate,
operate varchar2(50)
);
-- 创建一个触发器。当对baby_test表的数据插入,修改和删除的时候,向baby_test_log中插入数据
CREATE OR REPLACE TRIGGER trg_baby_test BEFORE
INSERT OR UPDATE or delete ON baby_test
FOR EACH ROW DECLARE
-- local variables here
BEGIN
CASE
WHEN inserting THEN
INSERT INTO baby_test_log (
id,
name,
operate
) VALUES (
:new.id,
:new.name,
'inserting'
);
WHEN updating('name') THEN
INSERT INTO baby_test_log (
id,
name,
operate
) VALUES (
:old.id,
:old.name,
'updating name'
);
WHEN deleting THEN
INSERT INTO baby_test_log (
id,
name,
operate
) VALUES (
:old.id,
:old.name,
'deleting'
);
END CASE;
END;
-- 测试触发器
insert into baby_test
select 1,'baby',to_date('19990101','yyyymmdd'), sysdate from dual;
select * from baby_test;
update baby_test
set name = 'hugh'
where id = 1;
delete from baby_test where id = 1;
-- 查看触发器的结果
select * from baby_test_log;