execute trigger conditionally user Oracle

Question

I want to condition the execution of the trigger to the oracle user.
If it is the Oracle user : X, then do not execute the trigger otherwise yes.

I created a function

CREATE OR REPLACE FUNCTION GET_SESSION_USER 
RETURN NUMBER
IS
    user_name_current VARCHAR2(50);
    resultat NUMBER := 1;
BEGIN
user_name_current := USER;
IF user_name_current = 'X' THEN
 resultat := 0;
end if;
RETURN resultat;
END;

Called the function in a trigger

CREATE OR REPLACE TRIGGER "TEST"."table1_trigger"
BEFORE UPDATE ON table1
FOR EACH ROW
-- WHEN (GET_SESSION_USER() = 1)
DECLARE
   ...
BEGIN
IF (GET_SESSION_USER() = 1) THEN
   ...
END IF;

-- WHEN (GET_SESSION_USER() = 1) return error : ORA-04076: Invalid NEW or OLD specification. So I put a if :
if (GET_SESSION_USER() = 1) then this line works, do I have to do a global if ? Can we do better ?

Thanks

Answer 1

Why do you need a function? Check user in trigger - not in its when clause, though, but if-then-else.

Why you can't use when clause along with function call? Because create trigger documentation says:

• This condition must contain correlation names and cannot contain a query
• You cannot invoke PL/SQL functions or methods in the trigger condition

---

SQL> CREATE TABLE table1
  2  (
  3     name   VARCHAR2 (20)
  4  );

Table created.

SQL> CREATE OR REPLACE TRIGGER table1_trigger
  2     BEFORE INSERT OR UPDATE
  3     ON table1
  4  BEGIN
  5     IF USER = 'SCOTT'
  6     THEN
  7        DBMS_OUTPUT.put_line ('Hello, ' || USER);
  8     ELSE
  9        DBMS_OUTPUT.put_line ('You aren''t Scott');
 10     END IF;
 11  END;
 12  /

Trigger created.

Testing:

SQL> SHOW USER
USER is "SCOTT"
SQL> INSERT INTO table1 (name) VALUES ('Littlefoot');
Hello, SCOTT

1 row created.

SQL>

---

Hardcoding SCOTT in 40 triggers is a tedious job and - possibly - useless (if you decide to move that code into another schema and SCOTT becomes PETER or something else). In that case, you can create function and use it - not in trigger's when clause, though - but in if-then-else:

SQL> CREATE OR REPLACE FUNCTION get_session_user
  2    RETURN NUMBER
  3  IS
  4  BEGIN
  5    RETURN CASE WHEN USER = 'SCOTT' THEN 1
  6                ELSE 0
  7           END;
  8  END;
  9  /

Function created.

SQL> CREATE OR REPLACE TRIGGER table1_trigger
  2     BEFORE INSERT OR UPDATE
  3     ON table1
  4  BEGIN
  5     IF get_session_user = 1                       --> here
  6     THEN
  7        DBMS_OUTPUT.put_line ('Hello, ' || USER);
  8     ELSE
  9        DBMS_OUTPUT.put_line ('You aren''t Scott');
 10     END IF;
 11  END;
 12  /

Trigger created.

SQL> INSERT INTO table1 VALUES ('x');
Hello, SCOTT

1 row created.

SQL>