PL SQL Trigger when update acolumn update another column in another table

Question

CREATE OR REPLACE TRIGGER UPDATE_FAC_CUS_MST_MREC_TRG   BEFORE UPDATE
   OF CUST_CODE ON fac_cus_mst   FOR EACH ROW 
     DECLARE
       v_cust_code VARCHAR2(25); 
    BEGIN
       SELECT cust_code
         INTO v_cust_code 
         FROM fac_cus_mst
        WHERE cust_code = :new.cust_code;
          UPDATE money_rec
          SET cust_code = v_cust_code
         WHERE REF = :new.REF; 
   END; 
  /

I tried about this code but, did it did not worked return me a error Like

10/18 PLS-00049: bad bind variable 'NEW.REF' SQL> CREATE OR
REPLACE TRIGGER UPDATE_FAC_CUS_MST_MREC_TRG 2 BEFORE UPDATE OF
CUST_CODE ON fac_cus_mst 3 FOR EACH ROW 4 DECLARE 5
v_cust_code VARCHAR2(25); 6 BEGIN 7 SELECT cust_code 8
INTO v_cust_code 9 FROM fac_cus_mst 10 WHERE
cust_code = :new.cust_code 11 UPDATE money_rec 12
SET cust_code = v_cust_code 13 WHERE REF = :new.REF; 14 END; 15 /

Warning: Trigger created with compilation errors.

SQL> show error Errors for TRIGGER UPDATE_FAC_CUS_MST_MREC_TRG:


  LINE/COL ERROR
   -------- ----------------------------------------------------------------- 4/5      PL/SQL: SQL Statement ignored 8/8      PL/SQL: ORA-00933: SQL command
   not properly ended 10/18    PLS-00049: bad bind variable 'NEW.REF'
   SQL>
   CREATE OR REPLACE TRIGGER UPDATE_FAC_CUS_MST_MREC_TRG   BEFORE UPDATE
   OF CUST_CODE ON fac_cus_mst   FOR EACH ROW 
    DECLARE
       v_cust_code VARCHAR2(25); BEGIN
       SELECT cust_code
         INTO v_cust_code 
         FROM fac_cus_mst
        WHERE cust_code = :new.cust_code;
          UPDATE money_rec
          SET cust_code = v_cust_code
        WHERE REF = :new.REF; 
END; 
/

Kaushik Nayak Kaushik Nayak · Accepted Answer · 2019-01-12T10:27:51

Regardless of the error which you are showing, there's a problem with your Trigger. You can't write queries on the same table inside a Trigger, it will lead to "Table is mutating" error.

CREATE OR REPLACE TRIGGER UPDATE_FAC_CUS_MST_MREC_TRG   
BEFORE UPDATE
   OF CUST_CODE ON fac_cus_mst  FOR EACH ROW 
    BEGIN
          UPDATE money_rec
            SET cust_code  = :new.cust_code
         WHERE REF = :new.REF; 
   END; 
  /

You only need a simple update statement and refer to :new.cust_code directly in it.

Regarding your error PLS-00049: bad bind variable 'NEW.REF', it seems that your money_rec table doesn't apparently have a REF column. Use the correct column name in its place, your Trigger should work fine.

PL SQL Trigger when update acolumn update another column in another table

1 Answers