CODEWITHSUNDEEP
oracle-databaseplsqltriggerstoad
A. Rahman
A. Rahman

Reputation: 71

Update trigger to add data from two different tables onto audit table

I've created a after update trigger and would like to add data from two different tables(GORADID and SPRIDEN) when the update fires. The both of the tables are connected by one column(PIDM), so therefore is it possible to also collect data from table 2 ? so far i been getting errors such as ORA: no bind variables.. I also tried doing a select statements. But also no luck. Any suggestions would help.

error: psl-00049: bad bind variable ':new.spriden_id'
error: psl-00049: bad bind variable ':new.spriden_first_name'
error: psl-00049: bad bind variable ':new.spriden_last_name'

Upvotes: 0

Views: 388

Answers (2)

Keyur Panchal
Keyur Panchal

Reputation: 1402

Try below code for your trigger. Please note that you can use :new and :old only for columns of the table GORADID as you are writing trigger ON GORADID table.
You are selecting USER INTO SZRGORA_AUDIT_USER_BANNER_ID. So you should use SZRGORA_AUDIT_USER_BANNER_ID in your next queries instead of saying USER. Directly saying USERNAME = USER will not work I think.

CREATE OR REPLACE TRIGGER AUDIT_USER.GORADID_UPDT_TRG1
AFTER UPDATE OF GORADID_ADDITIONAL_ID
ON GORADID
FOR EACH ROW
  DECLARE
    v_SZRGORA_AUDIT_USER_BANNER_ID VARCHAR2(150 CHAR);
    v_SZRGORA_AUDIT_STUDENT_BANNER VARCHAR2(9 CHAR);
    v_SZRGORA_AUDIT_USER_FIRST_NAME VARCHAR2(150 CHAR);
    v_SZRGORA_AUDIT_USER_LAST_NAME VARCHAR2(150 CHAR);
  BEGIN
    SELECT USER INTO v_SZRGORA_AUDIT_USER_BANNER_ID FROM DUAL;

    SELECT SPRIDEN_ID
     INTO v_SZRGORA_AUDIT_STUDENT_BANNER
    FROM SATURN.SPRIDEN
     WHERE SPRIDEN_PIDM = :new.GORADID_PIDM
      AND GORADID_ADDITIONAL_ID = :new.GORADID_ADDITIONAL_ID;

    SELECT SPRIDEN_FIRST_NAME, SPRIDEN_LAST_NAME
     INTO v_SZRGORA_AUDIT_USER_FIRST_NAME, v_SZRGORA_AUDIT_USER_LAST_NAME
    FROM SATURN.SPRIDEN
     WHERE SPRIDEN_PIDM =
     (SELECT PIDM FROM SATURN.IDM_STAFF_AFF_ST
       WHERE USERNAME = v_SZRGORA_AUDIT_USER_BANNER_ID);

    INSERT INTO AUDIT_USER.SZRGORA_AUDIT VALUES
    ( v_SZRGORA_AUDIT_STUDENT_BANNER,
     :new.GORADID_ADDITIONAL_ID,
     :old.GORADID_ADDITIONAL_ID,
     v_SZRGORA_AUDIT_USER_FIRST_NAME,
     v_SZRGORA_AUDIT_USER_LAST_NAME,
     v_SZRGORA_AUDIT_USER_BANNER_ID,
     'UPDATE',
     SYSDATE,
     :new.GORADID_SURROGATE_ID,
     :new.GORADID_VERSION,
     :new.GORADID_VPDI_CODE);
  END;
/

Upvotes: 1

Serdar Demir
Serdar Demir

Reputation: 108

Your trigger belongs to GORADID table and :new and :old represent this table

can you try change your trigger like this ?

CREATE OR REPLACE TRIGGER GORADID_UPDT_TRG1
   AFTER UPDATE OF GORADID_ADDITIONAL_ID
   ON GORADID
   FOR EACH ROW
DECLARE
   SZRGORA_AUDIT_USER_BANNER_ID    VARCHAR2 (150 CHAR);
   SZRGORA_AUDIT_STUDENT_BANNER    VARCHAR2 (9 CHAR);
   SZRGORA_AUDIT_USER_FIRST_NAME   VARCHAR2 (150 CHAR);
   SZRGORA_AUDIT_USER_LAST_NAME    VARCHAR2 (150 CHAR);
   SZRGORA_AUDIT_ADDITIONAL_ID     VARCHAR2 (50 CHAR);
   SPRIDEN_ID                      VARCHAR2 (9 CHAR);
BEGIN
   SELECT USER INTO SZRGORA_AUDIT_USER_BANNER_ID FROM DUAL;

   SELECT SPRIDEN_ID
     INTO SZRGORA_AUDIT_STUDENT_BANNER
     FROM SPRIDEN
    WHERE     SPRIDEN_PIDM =:old.GORADID_PIDM;


   SELECT SPRIDEN_FIRST_NAME, SPRIDEN_LAST_NAME
     INTO SZRGORA_AUDIT_USER_FIRST_NAME, SZRGORA_AUDIT_USER_LAST_NAME
     FROM SATURN.SPRIDEN
    WHERE SPRIDEN_PIDM = (SELECT PIDM
                            FROM SATURN.IDM_STAFF_AFF_ST
                           WHERE USERNAME = USER);

   INSERT INTO SZRGORA_AUDIT
        VALUES (SZRGORA_AUDIT_STUDENT_BANNER,
                :new.GORADID_ADDITIONAL_ID,
                :old.GORADID_ADDITIONAL_ID,
                SZRGORA_AUDIT_USER_FIRST_NAME,
                SZRGORA_AUDIT_USER_LAST_NAME,
                USER,
                'UPDATE',
                SYSDATE,
                :new.GORADID_SURROGATE_ID,
                :new.GORADID_VERSION,
                :new.GORADID_VPDI_CODE);
END;

Upvotes: 1

Related Questions