我在trigger中调用一个存储过程,存储过程的作用是更新另外一张表中地记录,存储过程中定义了一些cursor,首先先要进行删除那张要被更新地表中记录的操作,然后利用cursor拼一些字符串,将字符串插入到那张表中。这个存储过程单独执行是没有问题的,可是放到trigger里,好像存储过程就没有正确执行。它只执行了删除操作,后面的拼串、插记录都没有执行。请问,在trigger中调用存储过程要注意什么问题吗?trigger可以调用存储过程吗?trigger如下:CREATE OR REPLACE TRIGGER MYCHANNEL.AF_CPM_PERSON_CHNL_RELATION_2
AFTER INSERT OR UPDATE
ON MYCHANNEL.CPM_PERSON_CHNL_RELATION
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_COUNT NUMBER;
v_errcode NUMBER;
v_errmsg VARCHAR2(100);
BEGIN
SELECT COUNT(*) INTO V_COUNT
FROM CPM_SHOP A
WHERE A.SHOP_ID = :NEW.CHNL_ID; IF V_COUNT < 1 THEN RETURN; END IF; --NOT A SHOP
Sp_Update_Shop_Relation(:NEW.CHNL_ID);
SELECT COUNT(*) INTO V_COUNT
FROM CPM_SHOP A
WHERE A.SHOP_ID = :OLD.CHNL_ID; IF V_COUNT < 1 THEN RETURN; END IF; --NOT A SHOP
Sp_Update_Shop_Relation(:OLD.CHNL_ID); EXCEPTION WHEN OTHERS THEN
V_ERRCODE := SQLCODE;
V_ERRMSG := SUBSTR(SQLERRM, 1, 100);
Mmc_Common.SP_WRITE_LOG('TRIG_PERSON_CHNL_RELATION_2','5','2',v_errcode, v_errmsg);END;
AFTER INSERT OR UPDATE
ON MYCHANNEL.CPM_PERSON_CHNL_RELATION
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_COUNT NUMBER;
v_errcode NUMBER;
v_errmsg VARCHAR2(100);
BEGIN
SELECT COUNT(*) INTO V_COUNT
FROM CPM_SHOP A
WHERE A.SHOP_ID = :NEW.CHNL_ID; IF V_COUNT < 1 THEN RETURN; END IF; --NOT A SHOP
Sp_Update_Shop_Relation(:NEW.CHNL_ID);
SELECT COUNT(*) INTO V_COUNT
FROM CPM_SHOP A
WHERE A.SHOP_ID = :OLD.CHNL_ID; IF V_COUNT < 1 THEN RETURN; END IF; --NOT A SHOP
Sp_Update_Shop_Relation(:OLD.CHNL_ID); EXCEPTION WHEN OTHERS THEN
V_ERRCODE := SQLCODE;
V_ERRMSG := SUBSTR(SQLERRM, 1, 100);
Mmc_Common.SP_WRITE_LOG('TRIG_PERSON_CHNL_RELATION_2','5','2',v_errcode, v_errmsg);END;
CPM_SHOP 是否与要更新的那张表有关联?
trigger对与触发的表有约束关系的表操作时,会有问题,必须要提交以后才能进行操作.
即在存储过程变量定义部分添加"PRAGMA AUTONOMOUS_TRANSACTION;"