How can I fix Trigger errors? Oracle 19 Express

Question

I would like to track user activities for a certain table. For this I have created a table in which I can insert the audit data:

CREATE TABLE  "AUDIT_FINALPAPERS" 
   (    "TBLID" NUMBER, 
    "NAME" VARCHAR2(10), 
    "REMARKS" VARCHAR2(255), 
    "AUDIT_USER" VARCHAR2(50), 
    "AUDIT_DATE" DATE, 
    "AUDIT_ACTION" VARCHAR2(30)
   )

Then created a trigger to track user activities and insert data into the audit table:

     CREATE OR REPLACE TRIGGER AUDIT_TBL1
  2     -- starts on every update, insert or delete command
  3     AFTER INSERT OR DELETE OR UPDATE
  4     ON TBL1
  5     FOR EACH ROW
  6  DECLARE
  7     -- variable which declares if update, delete or insert process
  8     v_trg_action  VARCHAR2 (30);
  9     v_user        VARCHAR2 (30);
 10  BEGIN
 11     v_user := SYS_CONTEXT ('APEX$SESSION', 'APP_USER');
 12
 13     IF UPDATING
 14     THEN
 15        -- when update
 16        v_trg_action := 'UPDATED.';
 17     ELSIF DELETING
 18     THEN
 19        -- when delete
 20        v_trg_action := 'DELETED.';
 21     ELSIF INSERTING
 22     THEN
 23        -- when insert
 24        v_trg_action := 'SUBMITTED.';      -- aKtion
 25     ELSE
 26        -- if something else
 27        v_trg_action := NULL;
 28     END IF;
 29
 30     IF v_trg_action IN ('DELETED.', 'UPDATED.')
 31     THEN
 32        -- if v_trg_action is DELETE or UPDATE then insert old table values
 33        INSERT INTO AUDIT_TBL1 (TBLID,
 34                                NAME,
 35                                REMARKS,
 36                                AUDIT_USER,
 37                                AUDIT_DATE,
 38                                AUDIT_ACTION)
 39             VALUES (:OLD.TBLID,
 40                     :OLD.NAME,
 41                     :OLD.REMARKS,
 42                     v_user,
 43                     SYSDATE,
 44                     v_trg_action);
 45     ELSE
 46        INSERT INTO AUDIT_TBL1 (TBLID,
 47                                NAME,
 48                                REMARKS,
 49                                AUDIT_USER,
 50                                AUDIT_DATE,
 51                                AUDIT_ACTION)
 52             VALUES (:NEW.TBLID,
 53                     :NEW.NAME,
 54                     :NEW.REMARKS,
 55                     v_user,
 56                     SYSDATE,
 57                     v_trg_action);
 58     END IF;
 59  -- about the insert command on the audit table
 60  -- for current apex user:SYS_CONTEXT('APEX$SESSION','APP_USER');
 61  -- for date: SYSDATE
 62  -- for sql command: v_trg_action
 63  END AUDIT_TBL1;
 64  /

However I get error: PLS-00049: bad bind variable What exactly does this mean and how can this be solved?

score 0 · Answer 1 · answered Apr 12 '21 at 10:35

Looks like a wrongly reported error. When I ran your code, it went well except for this part as you named the variable v_trg_aKtion (instead of aCtion)

 21     ELSIF INSERTING
 22     THEN
 23        -- when insert
 24        v_trg_action := 'SUBMITTED.';      -- aKtion

Have a look; trigger created.

SQL> CREATE OR REPLACE TRIGGER AUDIT_TBL1
  2     -- starts on every update, insert or delete command
  3     AFTER INSERT OR DELETE OR UPDATE
  4     ON TBL1
  5     FOR EACH ROW
  6  DECLARE
  7     -- variable which declares if update, delete or insert process
  8     v_trg_action  VARCHAR2 (30);
  9     v_user        VARCHAR2 (30);
 10  BEGIN
 11     v_user := SYS_CONTEXT ('APEX$SESSION', 'APP_USER');
 12
 13     IF UPDATING
 14     THEN
 15        -- when update
 16        v_trg_action := 'UPDATED.';
 17     ELSIF DELETING
 18     THEN
 19        -- when delete
 20        v_trg_action := 'DELETED.';
 21     ELSIF INSERTING
 22     THEN
 23        -- when insert
 24        v_trg_action := 'SUBMITTED.';      -- aKtion
 25     ELSE
 26        -- if something else
 27        v_trg_action := NULL;
 28     END IF;
 29
 30     IF v_trg_action IN ('DELETED.', 'UPDATED.')
 31     THEN
 32        -- if v_trg_action is DELETE or UPDATE then insert old table values
 33        INSERT INTO AUDIT_TBL1 (TBLID,
 34                                NAME,
 35                                REMARKS,
 36                                AUDIT_USER,
 37                                AUDIT_DATE,
 38                                AUDIT_ACTION)
 39             VALUES (:OLD.TBLID,
 40                     :OLD.NAME,
 41                     :OLD.REMARKS,
 42                     v_user,
 43                     SYSDATE,
 44                     v_trg_action);
 45     ELSE
 46        INSERT INTO AUDIT_TBL1 (TBLID,
 47                                NAME,
 48                                REMARKS,
 49                                AUDIT_USER,
 50                                AUDIT_DATE,
 51                                AUDIT_ACTION)
 52             VALUES (:NEW.TBLID,
 53                     :NEW.NAME,
 54                     :NEW.REMARKS,
 55                     v_user,
 56                     SYSDATE,
 57                     v_trg_action);
 58     END IF;
 59  -- about the insert command on the audit table
 60  -- for current apex user:SYS_CONTEXT('APEX$SESSION','APP_USER');
 61  -- for date: SYSDATE
 62  -- for sql command: v_trg_action
 63  END AUDIT_TBL1;
 64  /

Trigger created.

SQL>

I don't know what you meant by that. I created AUDIT_TBL1 (as you posted) and - in order to make the trigger compile - TBL1 which contains necessary columns. Though, I'd suggest you to remove double quotes around any Oracle objects you might ever want to create. — Littlefoot, Apr 12 '21 at 11:37

How can I fix Trigger errors? Oracle 19 Express

1 Answers1