插入记录创建触发器

--创建触发器；
create or replace trigger FE44201B_EnquirySummary
  after insert on FE44201B FOR EACH ROW --说明创建的是行级触发器
declare
PRAGMA AUTONOMOUS_TRANSACTION;--自治事务
 V_OUTERRCODE VARCHAR2(200);
 V_OUTERRMSG  VARCHAR2(400);
begin

 -- DBMS_OUTPUT.PUT_LINE(:new.qtukid);
  --INSERT INTO crpdta.fe441001 (acukid,aclitm) values (:new.qtukid,'1');
  --arehoueInOut(:new.qtukid,:new.qtdate01,:new.qtdate02,:new.qtan8,:new.qtmcu,:new.qtmcu2,:new.qtsrp1,:new.qtsrp2,:new.qtsrp3,:new.qtdcto,:new.qtev01,V_OUTERRCODE,V_OUTERRMSG);
   PE44201_EnquirySummary(:NEW.QTUKID,:NEW.QTDRQJ,:NEW.QTRP11,:NEW.QTAC25,:NEW.QTSRP1,V_OUTERRCODE,V_OUTERRMSG);
   commit;

end FE44201B_EnquirySummary;
--------------------------------------------------------------
调用存储过程：
CREATE OR REPLACE PROCEDURE PE44201_EnquirySummary(V_UKID    NUMBER,       -- 唯一编号
                                           V_DRQJ    NUMBER,      --日期
                                           V_RP11    VARCHAR2,    -- 区域
                                           V_AC25    VARCHAR2,    -- 门店分类
                                           V_SRP1    VARCHAR2,    -- 项目大类
                                           V_OUTERRCODE OUT VARCHAR2, --错误代码（0：代表正确；负数：出错）
                                           V_OUTERRMSG  OUT VARCHAR2  --错误信息
                                           )IS
PRAGMA AUTONOMOUS_TRANSACTION;
    V_SQL      VARCHAR2(4000);
    V_TEMP1    VARCHAR2(500);
    V_TEMP2    VARCHAR2(500);
    V_ESUKID   NUMBER;
BEGIN
     IF V_UKID >100 THEN
       V_ESUKID := V_UKID - 100;
       DELETE FROM FE44201A WHERE ESUKID < V_ESUKID;
     END IF;
     
     V_TEMP1 :='';
     V_TEMP2 :='';
     
     IF TRIM(V_AC25)>' ' THEN
       V_TEMP1 :=', F0101';
       V_TEMP2 := V_TEMP2 ||'AND SDAN8 = ABAN8 AND ABAC25 = '''||V_AC25||'''  ';
     END IF;
     
     IF TRIM(V_SRP1)>' ' THEN
       V_TEMP2 := V_TEMP2 ||'AND IMSRP1 =  '''||V_SRP1||'''  ';
     END IF;
 
     V_SQL := 'INSERT INTO FE44201A (ESUKID,ESDRQJ,ESMCU,ESITM,ESUORG,ESUOR2,ESUOR3,ESUORD,ESSOQS,ESUOM,ESPRP7) '
     --要货数量 SF单
     ||'SELECT  '||V_UKID||','||V_DRQJ||',A.SDMCU,A.SDITM,SFUORG,S5UORG,(NVL(SFUORG,0)+NVL(S5UORG,0)+NVL(SHUORG,0)) UOR3, 0 ESUORD ,SOQS,A.IMUOM1,IBPRP7 FROM '
     ||'(SELECT SDMCU, SDITM, IMUOM1, SFUORG ,IBPRP7 '
        ||'FROM (SELECT SDMCU, SDITM, IMUOM1, SUM(SFUORG) SFUORG '
               ||'FROM (SELECT SDMCU,SDITM,IMUOM1,CASE WHEN SDUOM <> IMUOM1 THEN SFUORG * UMCNV1 / 10000000 ELSE SFUORG END SFUORG '
                          ||'FROM (SELECT SDMCU, SDITM, SDUOM, IMUOM1, SUM(SDUORG) SFUORG '
                                  ||'FROM F4211, F0006, F4101 '||V_TEMP1||' '
                                  ||'WHERE SDITM = IMITM AND SDLTTR < 980 AND SDDRQJ = '||V_DRQJ||' AND SDMCU = MCMCU  AND MCRP11 = '''||V_RP11||''' '
                                  ||'AND SDDCTO = ''SF''  '||V_TEMP2||' AND SDUORG <> 0 '
                                 ||'GROUP BY SDMCU, SDITM, SDUOM, IMUOM1 '
                                 ||'ORDER BY SDMCU, SDITM, SDUOM, IMUOM1) U1 '
                          ||'LEFT JOIN F41002 ON SDITM = UMITM AND SDUOM = UMUM AND IMUOM1 = UMRUM ) '
                 ||'GROUP BY SDMCU, SDITM, IMUOM1) U2 '
          ||'LEFT JOIN F4102 ON SDMCU = IBMCU AND SDITM = IBITM ORDER BY SDMCU, IBPRP7, SDITM) A LEFT JOIN ('
       --补货数量 S5单
      ||'SELECT SDMCU, SDITM, IMUOM1, S5UORG '
        ||'FROM (SELECT SDMCU, SDITM, IMUOM1, SUM(S5UORG) S5UORG '
                ||'FROM (SELECT SDMCU,SDITM,IMUOM1,CASE WHEN SDUOM <> IMUOM1 THEN S5UORG * UMCNV1 / 10000000 ELSE S5UORG END S5UORG '
                        ||'FROM (SELECT SDMCU, SDITM, SDUOM, IMUOM1, SUM(SDUORG) S5UORG '
                                ||'FROM F4211, F0006, F4101 '||V_TEMP1||' '
                               ||'WHERE SDITM = IMITM AND SDLTTR < 980 AND SDDRQJ = '||V_DRQJ||' AND SDMCU = MCMCU AND MCRP11 = '''||V_RP11||''' '
                                 ||'AND SDDCTO = ''S5''  '||V_TEMP2||' AND SDUORG <> 0 '
                               ||'GROUP BY SDMCU, SDITM, SDUOM, IMUOM1 '
                               ||'ORDER BY SDMCU, SDITM, SDUOM, IMUOM1) U1 '
                        ||'LEFT JOIN F41002 ON SDITM = UMITM AND SDUOM = UMUM AND IMUOM1 = UMRUM) '
               ||'GROUP BY SDMCU, SDITM, IMUOM1) U2 '
        ||'LEFT JOIN F4102 ON SDMCU = IBMCU AND SDITM = IBITM '
       ||'ORDER BY SDMCU, IBPRP7, SDITM ) B ON A.SDITM=B.SDITM AND A.SDMCU=B.SDMCU LEFT JOIN ('
       --直送要货数量 Sh单
       ||'SELECT SDMCU, SDITM, IMUOM1, SHUORG '
        ||'FROM (SELECT SDMCU, SDITM, IMUOM1, SUM(SHUORG) SHUORG '
                ||'FROM (SELECT SDMCU,SDITM,IMUOM1,CASE WHEN SDUOM <> IMUOM1 THEN SHUORG * UMCNV1 / 10000000 ELSE SHUORG END SHUORG '
                        ||'FROM (SELECT SDMCU, SDITM, SDUOM, IMUOM1, SUM(SDUORG) SHUORG '
                                ||'FROM F4211, F0006, F4101 '||V_TEMP1||' '
                               ||'WHERE SDITM = IMITM AND SDLTTR < 980 AND SDDRQJ = '||V_DRQJ||' AND SDMCU = MCMCU AND MCRP11 = '''||V_RP11||''' '
                                 ||'AND SDDCTO = ''SH''  '||V_TEMP2||' AND SDUORG <> 0 '
                               ||'GROUP BY SDMCU, SDITM, SDUOM, IMUOM1 '
                               ||'ORDER BY SDMCU, SDITM, SDUOM, IMUOM1) U1 '
                        ||'LEFT JOIN F41002 ON SDITM = UMITM AND SDUOM = UMUM AND IMUOM1 = UMRUM) '
               ||'GROUP BY SDMCU, SDITM, IMUOM1) U2 '
        ||'LEFT JOIN F4102 ON SDMCU = IBMCU AND SDITM = IBITM '
       ||'ORDER BY SDMCU, IBPRP7, SDITM  ) C ON A.SDITM=C.SDITM AND A.SDMCU=C.SDMCU LEFT JOIN ('
       --数量合计
       ||'SELECT SDMCU, SDITM, IMUOM1, SOQS '
          ||'FROM (SELECT SDMCU, SDITM, IMUOM1, SUM(SOQS) SOQS '
                  ||'FROM (SELECT SDMCU,SDITM,IMUOM1,CASE WHEN SDUOM <> IMUOM1 THEN SOQS * UMCNV1 / 10000000 ELSE SOQS END SOQS '
                          ||'FROM (SELECT SDMCU, SDITM, SDUOM, IMUOM1, SUM(SDUORG) SOQS '
                                  ||'FROM F4211, F0006, F4101 '||V_TEMP1||' '
                                 ||'WHERE SDITM = IMITM AND SDLTTR < 980 AND SDDRQJ = '||V_DRQJ||' AND SDMCU = MCMCU AND SDDCTO IN (''S5'',''SF'', ''SH'') '
                                   ||'AND MCRP11 ='''||V_RP11||'''  '||V_TEMP2||' AND SDCOMM = ''C'' AND SDUORG <> 0 '
                                 ||'GROUP BY SDMCU, SDITM, SDUOM, IMUOM1 '
                                 ||'ORDER BY SDMCU, SDITM, SDUOM, IMUOM1) U1 '
                          ||'LEFT JOIN F41002 ON SDITM = UMITM AND SDUOM = UMUM AND IMUOM1 = UMRUM) '
                 ||'GROUP BY SDMCU, SDITM, IMUOM1) U2 '
          ||'LEFT JOIN F4102 ON SDMCU = IBMCU AND SDITM = IBITM ORDER BY SDMCU, IBPRP7, SDITM ) D ON A.SDITM=D.SDITM AND A.SDMCU=D.SDMCU ';
    dbms_output.put_line(V_SQL);
    execute immediate V_SQL;
     commit;
    UPDATE  FE44201A SET ESLITM =(SELECT IMLITM FROM  F4101 WHERE IMITM=ESITM);
    UPDATE  FE44201A SET ESDC = (SELECT MCDC FROM  F0006 WHERE ESMCU=MCMCU);
    UPDATE  FE44201A SET (ESDSC1,ESDSC2) = (SELECT IMDSC1,IMDSC2 FROM  F4101 WHERE IMITM=ESITM);
    commit;
    
   EXCEPTION
    WHEN OTHERS THEN
       ROLLBACK;
       V_OUTERRCODE := SQLCODE;
      V_OUTERRMSG  := SUBSTR(SQLERRM, 1, 255);
END PE44201_EnquirySummary;