REFERENCING OLD AS OLD NEW AS NEW FOR EACH Row
CREATE OR REPLACE TRIGGER TRI_DELETE_INFO
AFTER DELETE ON T_INFO_2012
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
V_SQLERRM VARCHAR2(200);
BEGIN
DELETE FROM T_INFO_2013 WHERE CODE = :OLD.CODE;
--COMMIT; 触发器中不可以使用COMMIT;
EXCEPTION
WHEN OTHERS THEN
V_SQLERRM := '触发器 TRI_DELETE_INFO 出错:' || SQLERRM;
INSERT INTO PROCEDURE_LOG
(ERRMSG, ERRDATE)
VALUES
(V_SQLERRM, SYSDATE);
END;
1、For each row的意义是:在一次操作表的语句中,每操作成功一行就会触发一次;不写的话,表示是表级触发器,则无论操作多少行,都只触发一次;
2、REFERENCING OLD 和 REFERENCING NEW 的含义不同,具体取决于触发器是行级还是语句级触发器。对于行级触发器,REFERENCING OLD 子句允许引用更新或删除之前行中的值,REFERENCING NEW 子句允许引用已插入或更新的值。在 BEFORE 和 AFTER 触发器中可以引用 OLD 和 NEW 行。REFERENCING NEW 子句允许在插入或更新操作发生之前在 BEFORE 触发器中修改新行
3、:NEW 和:OLD使用方法和意义,new 只出现在insert和update时,old只出现在update和delete时。在insert时new表示新插入的行数据,update时new表示要替换的新数据、old表示要被更改的原来的数据行,delete时old表示要被删除的数据。
begin前面出现的new,old不加冒号,begin和end之间出现的new和old都要在前面加上":"。
REFERENCING OLD AS OLD NEW AS NEW
这样写之后就可以在begin end直接直接使用别名来引用。
Example:
CREATE OR REPLACE TRIGGER OWNER_HUB.TRG_HOM_PRICELIST_FEE
AFTER INSERT OR DELETE OR UPDATE ON OWNER_HUB.MV_HOM_PRICELIST_FEE REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v#change char(1);
v#sqlErrm varchar2(2000);
v#OK varchar2(5) := 'OK';
v#ERR varchar2(5) := 'ERR';
v#SourceSystem varchar2(5) := 'HOM';
BEGIN
IF inserting or updating THEN
if inserting then
v#change := 'I';
else
v#change := 'U';
end if;
begin
INSERT INTO OWNER_INT.INT_HOM_PRICELIST_FEE (
CODE_SOURCE_SYSTEM,
DATE_EFFECTIVE_INSERTED,
CODE_CHANGE_TYPE,
CODE_LOAD_STATUS,
DESC_LOAD_ERROR,
DTIME_INSERTED,
SK_PROC_INSERTED,
id,
id_pricelist,
id_feetype,
valid_from,
valid_to,
canceled,
calcul_type,
limit_from,
limit_to
)
VALUES
(v#SourceSystem,
to_date(sys_context('CONTEXT_ETL', 'DATE_EFFECTIVE'), 'DD.MM.YYYY'),
v#change,
v#OK,
NULL,
SYSDATE,
to_number(sys_context('CONTEXT_ETL', 'PROCESS_ID')),
:new.id,
:new.id_pricelist,
:new.id_feetype,
:new.valid_from,
:new.valid_to,
:new.canceled,
:new.calcul_type,
:new.limit_from,
:new.limit_to);
end;
ELSE
v#change := 'D';
begin
INSERT INTO OWNER_INT.INT_HOM_PRICELIST_FEE (
CODE_SOURCE_SYSTEM,
DATE_EFFECTIVE_INSERTED,
CODE_CHANGE_TYPE,
CODE_LOAD_STATUS,
DESC_LOAD_ERROR,
DTIME_INSERTED,
SK_PROC_INSERTED,
id,
id_pricelist,
id_feetype,
valid_from,
valid_to,
canceled,
calcul_type,
limit_from,
limit_to
)
VALUES
(v#SourceSystem,
to_date(sys_context('CONTEXT_ETL', 'DATE_EFFECTIVE'), 'DD.MM.YYYY'),
v#change,
v#OK,
NULL,
SYSDATE,
to_number(sys_context('CONTEXT_ETL', 'PROCESS_ID')),
:old.id,
:old.id_pricelist,
:old.id_feetype,
:old.valid_from,
:old.valid_to,
:old.canceled,
:old.calcul_type,
:old.limit_from,
:old.limit_to);
end;
END IF;
END;