simple trigger/procedure problem for oracle apex sql

Question

im trying to run this audit trail trigger in oracle apex sql but i keep getting the same error and i dont know what im doing wrong. also i need to do this same trigger to every table in my database... is there a way i can do the same thing through a procedure so as to only having to do it once?

create or replace TRIGGER AUDIT_TRAIL_USERS_TRIG
  -- starts on every update, insert or delete command
  AFTER INSERT OR DELETE OR UPDATE ON USERS
  FOR EACH ROW
DECLARE
  -- variable which declares if update, delete or insert process
  v_trg_action varchar2(10);
BEGIN
  IF updating  THEN
    -- when update
    v_trg_action := 'UPDATE';
  ELSIF deleting  THEN
    -- when delete
    v_trg_action := 'DELETE';
  ELSIF inserting  THEN
    -- when insert
    v_trg_action := 'INSERT';
  ELSE
    -- if something else
  END IF;
  IF v_trg_action IN ('DELETE','UPDATE','INSERT') THEN
      -- if v_trg_action is DELETE, UPDATE OR INSERT then insert old table values
   INSERT INTO AUDIT_TRAIL
  ( AUDIT_USER,  AUDIT_DATE,  AUDIT_ACTION)
   VALUES
  (UPPER(v('APP_USER')), SYSDATE, v_trg_action);
  ELSE
  END IF;
  -- about the insert command on the audit table
  -- for current apex user: v('APP_USER')
  -- for date: SYSDATE
  -- for sql command: v_trg_action   
END AUDIT_TRAIL_USERS_TRIG;

the error im getting (im sure i have more than what its saying to me)is as follows:

Compilation failed, line 16 (03:29:53) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.

PLS-00103: Encountered the symbol "END" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge json_exists json_value json_query json_object json_array Compilation failed, line 25 (03:29:53) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.

PLS-00103: Encountered the symbol "END" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge json_exists json_value json_query json_object json_array

You cannot leave empty IF...ELSE blocks.

VN'sCorner
– VN'sCorner

2020-05-22 04:41:33 +00:00
Commented May 22, 2020 at 4:41 — VN'sCorner
– VN'sCorner, Commented May 22, 2020 at 4:41

VN'sCorner · Accepted Answer · 2020-05-22 04:46:06Z

IF..ELSE blocks cannot be left empty. If you don't need them remove it, I have added a dummy NULL call for the code to compile.Add appropriate logic as desired, otherwise remove the block.

create or replace TRIGGER AUDIT_TRAIL_USERS_TRIG
  -- starts on every update, insert or delete command
  AFTER INSERT OR DELETE OR UPDATE ON USERS
  FOR EACH ROW
DECLARE
  -- variable which declares if update, delete or insert process
  v_trg_action varchar2(10);
BEGIN
  IF updating  THEN
    -- when update
    v_trg_action := 'UPDATE';
  ELSIF deleting  THEN
    -- when delete
    v_trg_action := 'DELETE';
  ELSIF inserting  THEN
    -- when insert
    v_trg_action := 'INSERT';
  ELSE
    -- if something else
    NULL;
  END IF;
  IF v_trg_action IN ('DELETE','UPDATE','INSERT') THEN
      -- if v_trg_action is DELETE, UPDATE OR INSERT then insert old table values
   INSERT INTO AUDIT_TRAIL
  ( AUDIT_USER,  AUDIT_DATE,  AUDIT_ACTION)
   VALUES
  (UPPER(v('APP_USER')), SYSDATE, v_trg_action);

  null;
  ELSE
   NULL;
  END IF;
  -- about the insert command on the audit table
  -- for current apex user: v('APP_USER')
  -- for date: SYSDATE
  -- for sql command: v_trg_action   
END AUDIT_TRAIL_USERS_TRIG;

Imran · Accepted Answer · 2021-01-12 10:10:24Z

0

Use this code, it will work same like yours

create or replace TRIGGER AUDIT_TRAIL_USERS_TRIG
  AFTER INSERT OR DELETE OR UPDATE ON USERS
  FOR EACH ROW
DECLARE
BEGIN
    IF inserting or updating or deleting  THEN
        INSERT INTO AUDIT_TRAIL
            (AUDIT_USER,  AUDIT_DATE,  AUDIT_ACTION)
        VALUES
            (UPPER(v('APP_USER')), SYSDATE, v_trg_action);
    END IF;  
END AUDIT_TRAIL_USERS_TRIG;

answered Jan 12, 2021 at 10:10

Imran

1692 silver badges7 bronze badges

Collectives™ on Stack Overflow

simple trigger/procedure problem for oracle apex sql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related