oracle 触发器 增删改,Oracle开发触发器(增删改)一例

需求描述：

新增表table_a一条记录 后，在table_b中记录这条新记录；

修改表table_a 一条记录后，查找table_b还有没有对应的记录，如果有，则同样更新；如果没有，则再次记录它，并作已更新标记；

删除表table_a 一条记录后，查找table_b还有没有对应的记录，如果有，则删除它；如果没有，则再次记录它，并作已删除标记；

为什么table_b的记录不总是与table_a 对应呢？因为本案例实际业务应用中有一个监视器专门负责提取table_b表的数据，每提取一条就删除table_b一条记录。

针对上述需求描述，特建立如下表和触发器：

表table_a ：

create table table_a (

f1 number primary key,

f2 varchar(10),

f3 varchar(10),

f4 varchar(10)

);

/

表table_b，它只包含业务所需的几个字段f2和f4，增加了一个状态记录字段status：

create table table_b (

f1 number primary key,

f2 varchar(10),

f4 varchar(10),

status number default 0

);

/

辅助函数，用来查询table_b是否存在主键为ID值的记录：

CREATE OR REPLACE

FUNCTION EXISTS_TABLEB_REC(ID NUMBER)

RETURN NUMBER

AS

iReturn NUMBER;

CURSOR REC_EXISTS(k NUMBER) IS

SELECT 1 FROM TABLE_B WHERE F1=k;

BEGIN

iReturn := 0;

OPEN REC_EXISTS(ID);

FETCH REC_EXISTS INTO iReturn;

CLOSE REC_EXISTS;

RETURN iReturn;

END;

/

INSERT后触发器，对表table_a执行insert后立即对表table_b执行insert：

CREATE OR REPLACE

TRIGGER TRI_TABLEA_INS AFTER

INSERT ON TABLE_A FOR EACH ROW BEGIN

INSERT INTO TABLE_B(f1, f2, f4) VALUES (:NEW.F1,:NEW.F2,:NEW.F4);

END;

/

UPDATE后触发器，对表table_a执行update后，检查table_b是否存在记录，如不存在，则insert，否则update：

CREATE OR REPLACE

TRIGGER TRI_TABLEA_UPD AFTER

UPDATE ON TABLE_A FOR EACH ROW BEGIN

IF (EXISTS_TABLEB_REC(:OLD.F1)=0) THEN

INSERT INTO TABLE_B(f1, f2, f4, status) VALUES (:NEW.F1,:NEW.F2,:NEW.F4, 1);

ELSE

UPDATE TABLE_B SET F2=:NEW.F2, F4=:NEW.F4 WHERE F1=:OLD.F1;

END IF;

END;

/

DELETE后触发器，对表table_a执行delete后，检查table_b是否存在记录，如不存在，则insert，否则delete：

CREATE OR REPLACE

TRIGGER TRI_TABLEA_DEL AFTER

DELETE ON TABLE_A FOR EACH ROW BEGIN

IF (EXISTS_TABLEB_REC(:OLD.F1)=0) THEN

INSERT INTO TABLE_B(f1, f2, f4, status) VALUES (:OLD.F1,:OLD.F2,:OLD.F4, -1);

ELSE

DELETE FROM TABLE_B WHERE F1=:OLD.F1;

END IF;

END;

/

测试数据：

insert into table_a values (1, '1','11','111');

insert into table_a values (2, '2','22','222');

insert into table_a values (3, '3','33','333');

insert into table_a values (4, '4','44','444');

insert into table_a values (5, '5','55','555');

update table_a set f2='WW' where f1=5;

delete from table_b where f1=2;

update table_a set f2='EE' where f1=2;

delete from table_b where f1=1;

delete from table_a where f1=1;

insert into table_a values (6, '6','66','666');

delete from table_a where f1=5;

测试结果

table_a 数据记录：

(2,'EE','22','222');

(3,'3','33','333');

(4,'4','44','444');

(5,'WW','55','555');

(6,'6','66','666');

table_b数据记录：

(3,'3','333',0);(4,'4','444',0);(5,'WW','555',0);(2,'EE','222',1);(1,'1','111',-1);(6,'6','666',0);