--创建测试表:
-- Create table
create table TEST_LIXIONG_TRIGGER_20170824
(
name1 VARCHAR2(64),
name2 VARCHAR2(256),
name3 VARCHAR2(256)
);
insert into TEST_LIXIONG_TRIGGER_20170824
select '1', 'aa', 'mm'
from dual
union all
select '2', 'bb', 'mm'
from dual
union all
select '3', 'cc', 'mm'
from dual
union all
select '4', 'dd', 'mm'
from dual;
select * from TEST_LIXIONG_TRIGGER_20170824;
--目的:
--如果TEST_LIXIONG_TRIGGER_20170824表的name3列发生变更,则更新name2列
--实现步骤
--创建包,存储变更的rowid,两个集合,一个存储变更的ROWID,另一个是空集合
create or replace package ceshi_pkg_LIXIONG is
type settype is table of rowid index by binary_integer;
set_new settype;
set_empty settype;
end ceshi_pkg_LIXIONG;
--创建BEFORE触发器(非行级触发器),每次将set_new置为空
create or replace trigger TEST_TRIGGER_bef
before update of name3 on TEST_LIXIONG_trigger_20170824
begin
ceshi_pkg_LIXIONG.set_new := ceshi_pkg_LIXIONG.set_empty;
end TEST_TRIGGER_bef;
--创建行级触发器,存储变更的ROWID
create or replace trigger TEST_TRIGGER_row
after update of name3 on TEST_LIXIONG_trigger_20170824
for each row
begin
ceshi_pkg_LIXIONG.set_new(ceshi_pkg_LIXIONG.set_new.count + 1) := :new.rowid;
end TEST_TRIGGER_row;
--创建AFTER触发器(非行级触发器)
create or replace trigger TEST_TRIGGER_aft
after update of name3 on TEST_LIXIONG_trigger_20170824
begin
for i in 1 .. ceshi_pkg_LIXIONG.set_new.count loop
update TEST_LIXIONG_trigger_20170824 a
set a.name2 = a.name2 || '_zz'
where rowid = ceshi_pkg_LIXIONG.set_new(i);
end loop;
end TEST_TRIGGER_aft;
--测试
select * from TEST_LIXIONG_trigger_20170824;
update TEST_LIXIONG_trigger_20170824 n
set n.name3 = 'nn'
where n.name1 in ('1','3');
--删除测试表、测试触发器
drop trigger TEST_TRIGGER_AFT;
drop trigger TEST_TRIGGER_BEF;
drop trigger TEST_TRIGGER_ROW;
drop table TEST_LIXIONG_TRIGGER_20170824;
drop PACKAGE CESHI_PKG_LIXIONG;
参考:http://blog.csdn.net/li19236/article/details/77542392