oracle的触发器的使用举例

oracle中的触发器

　　这个文章演示了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;