Trigger example

最新推荐文章于 2021-06-11 21:34:50 发布

原创最新推荐文章于 2021-06-11 21:34:50 发布 · 329 阅读

0 ·

CC 4.0 BY-SA版权

ORA-Dev 专栏收录该内容

59 篇文章

订阅专栏

本文通过一个具体的Oracle数据库触发器案例，详细介绍了如何使用触发器来记录表上的增删改操作，并展示这些操作如何被准确地记录到日志表中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、一个例子

表test1(id1 int,name varchar2(200);

表log(trigger_info)

create or replace trigger audit_test1
after insert or update of name or delete on test1 --只有update能到列
for each row
when (nvl(old.id1,0)>=0) --当insert无old时，为old.id1 is null
declare

begin
case --deleting、inserting、updating三个逻辑值，当相应动作时，该值为TRUE
    when deleting then
      --delete只有OLD，new为null
      insert into log
      values
        ('delete id' || :old.id1 || ',name' || :old.name);
    when inserting then
      --insert只有new，old为null
      insert into log
      values
        ('insert id' || :new.id1 || ',name' || :new.name);
    when updating then
      --既有new也有old
      insert into log
      values
        ('update oldid' || :old.id1 || ',oldname' || :old.name ||
         'to newid' || :new.id1 || ',newname' || :new.name);
end case;
--commit 触发器里不能有提交，它与对应的DML属于同一个事务
end ;

分析：

a、insert

SQL> insert into test1 values(33,'dqu');

1 row inserted

SQL> select * from log;

TRIGGER_INFO
--------------------
insert id33,namedqu

SQL> rollback;

Rollback complete

SQL> select * from log;

TRIGGER_INFO
--------------------

SQL> insert into test1 values(33,'dqu');

1 row inserted

SQL>commit;

SQL> select * from log;

TRIGGER_INFO
--------------------
insert id33,namedqu

b、update

SQL> update test1 set id1=32;

1 row updated

SQL> select * from log;

TRIGGER_INFO
--------------------
insert id33,namedqu

SQL> commit;

Commit complete

SQL> select * from test1;

ID1 NAME
--------------------------------------- --------------------
32 dqu

SQL> select * from log; ---未更新name列，触发器未插入日志

TRIGGER_INFO
--------------------------------------------------------------------------------
insert id33,namedqu

SQL> update test1 set name ='duqiang new';

1 row updated

SQL> select * from log;

TRIGGER_INFO
--------------------------------------------------------------------------------
insert id33,namedqu
update oldid32,oldnamedquto newid32,newnameduqiang new

c、delete

SQL> delete test1;

1 row deleted

SQL> select * from log;

TRIGGER_INFO
--------------------------------------------------------------------------------
insert id33,namedqu
update oldid32,oldnamedquto newid32,newnameduqiang new
delete id32,nameduqiang new

d、停用启用trigger

SQL> alter trigger audit_inset_into_test1 disable;
Trigger altered

SQL> alter trigger audit_inset_into_test1 enable;
Trigger altered