开发者

trigger error:=ORA-04079: invalid trigger specification

开发者 https://www.devze.com 2023-02-26 10:38 出处:网络
trigger error:=ORA-04079: invalid trigger specification the code causing that error is below: CREATE OR REPLACE TRIGGER TRGBILLINGADDRESS

trigger error:=ORA-04079: invalid trigger specification

the code causing that error is below:

CREATE OR REPLACE TRIGGER TRGBILLINGADDRESS 
AFTER INSERT OR DELETE OR UPDATE ON TBLMACCOUNTADDRESS 
    add1 wom.tbltaddress.address1%TYPE;
    add2 wom.tbltaddress.address2%TYPE;
    cityid wom.tbltaddress.city_id%TYPE;
    stateid wom.tbltaddress.state_id%TYPE;
    pincd wom.tbltaddress.pincode%TYPE;
BEGIN
   SELECT address1,address2,city_id,state_id,pincode
          INTO add1,add2,cityid,stateid,pincd  FROM wom.tbltaddress ta  WHERE ta.ID IN (
          SELECT vbac.billing_address_id
          FROM wom.vw_billaddresschange vbac, wom.tbltaddress ita
          WHERE vbac.billing_address_id = ita.ID
          AND vbac.lcid = parlcid);
   IF add1 = :NEW.address1 AND add2 = :NEW.address2 AND  cityid = :NEW.cityid AND  stateid = :NEW.stateid AND  pincode = :NEW.zip THEN            
          dbms_output.put_line('Address Already Exist in tbltaddress table');
   ELSE
          UPDATE wom.tbltaddress ta
               开发者_Python百科    SET ta.address1 = :NEW.address1,
                       ta.address2 = :NEW.address2,
                       ta.city_id = :NEW.cityid,
                       ta.country_id = 'CTR0001',
                       ta.state_id = :NEW.stateid,
                       ta.pincode = :NEW.zip
                 WHERE ta.ID IN (
                          SELECT vbac.billing_address_id
                            FROM wom.vw_billaddresschange vbac, wom.tbltaddress ita
                           WHERE vbac.billing_address_id = ita.ID
                                 AND vbac.lcid = parlcid);
   END IF;
END;


You should use DECLARE before declaring your variables.

CREATE OR REPLACE TRIGGER TRGBILLINGADDRESS 
AFTER INSERT OR DELETE OR UPDATE ON TBLMACCOUNTADDRESS 
DECLARE
    add1 wom.tbltaddress.address1%TYPE;
    add2 wom.tbltaddress.address2%TYPE;
    cityid wom.tbltaddress.city_id%TYPE;
    stateid wom.tbltaddress.state_id%TYPE;
    pincd wom.tbltaddress.pincode%TYPE;
BEGIN
0

精彩评论

暂无评论...
验证码 换一张
取 消