这几天突然会有这样的一个需求,需要重复表的记录, 除了插入到表的记录的AC列不同之外,其余的字段完全相同的。对于这个需求,我一开始想到的是在倒入数据文件的时候,它会删掉原表的数据,那就使用一个触发器来捕获事件,每次有数据插入式,就执行一下这个触发器:
CREATE OR REPLACE TRIGGER "DQA"."DQATISTA" AFTER
INSERT ON DQAQTST DECLARE
/******************************************************************************
NAME: DQATISTA
PURPOSE: DUPLICATE DQAQTST DATA RECORDS EXCEPT ACTIVITY COLUMNS AND SHARE THE REALER NETWORKS FOR HA ACTIVITIES.
Girer les colonnes techniques
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 20/10/2015 xxxx 1. Created this trigger.
PARAMETERS:
INPUT:
OUTPUT:
RETURNED VALUE:
CALLED BY:
CALLS:
EXAMPLE USE:
ASSUMPTIONS:
LIMITATIONS:
ALGORITHM:
NOTES:
Here is the complete list of available Auto Replace Keywords:
Object Name: DQATISTA
Sysdate: 20/10/2015
Date/Time: 20/10/2015 11:42:17
Date: 20/10/2015
Time: 11:42:17
Username:
******************************************************************************/
/* Main */
BEGIN
DELETE FROM DQAQTST WHERE ACTIVITY LIKE '%HA';
INSERT
INTO DQAQTST
(
BRAND ,
COUNTRY ,
ACTIVITY ,
PROTOCOL ,
IPSOS_DEALER_CODE ,
IPSOS_DEALER_CODEBIS,
RRDI_CODE ,
SITE_NAME ,
RRDI_TOWN_NAME ,
PRIORITY_SITE ,
BRANCH_INFORMATION ,
STATUS ,
SECTOR_CODE ,
SECTOR_TYPE ,
SECTOR_NAME ,
DR_CODE ,
DR_TYPE ,
DR_NAME ,
LEVEL1_CODE ,
LEVEL1_NAME ,
LEVEL2_CODE ,
LEVEL2_NAME ,
LEVEL3_CODE ,
LEVEL3_NAME ,
UPDATE_TIMESTAMP,
USERID
)
SELECT BRAND ,
COUNTRY ,
ACTIVITY
||'HA' ,
PROTOCOL ,
IPSOS_DEALER_CODE ,
IPSOS_DEALER_CODEBIS,
RRDI_CODE ,
SITE_NAME ,
RRDI_TOWN_NAME ,
PRIORITY_SITE ,
BRANCH_INFORMATION ,
STATUS ,
SECTOR_CODE ,
SECTOR_TYPE ,
SECTOR_NAME ,
DR_CODE ,
DR_TYPE ,
DR_NAME ,
LEVEL1_CODE ,
LEVEL1_NAME ,
LEVEL2_CODE ,
LEVEL2_NAME ,
LEVEL3_CODE ,
LEVEL3_NAME ,
UPDATE_TIMESTAMP,
USERID
FROM DQAQTST;
commit;
END DQATISTA;
发现上传文件确实成功了的,但是它会报错并且提示会发生无限次循环触发此触发器,后来总算明白了:
当触发器被触发时又向基表中插入了记录。导致该触发器又被触发执行,导致了死循环。