CODEWITHSUNDEEP
sqloracle-databaseplsql
Mojimi
Mojimi

Reputation: 3161

ORA 00920 In CASE with "OR" inside Trigger Oracle

The syntax error seems to be in the WHEN INSERTING OR UPDATING (more specifically it underlines the OR), I don't understand why it doesn't work in a CASE condition but it works in a IF condition.

Its a BEFORE DELETE OR INSERT OR UPDATE Trigger

Code portion of the issue:

SELECT 
    field 
INTO 
    my_var
FROM 
    my_table
WHERE 
    column1 = (CASE 
                        WHEN INSERTING OR UPDATING THEN 
                            :new.column2
                        ELSE --deleting
                            :old.column2
                        END);

What would be the solution?

Here is the rest of the trigger if anyone wants to test : https://pastebin.com/AJqGQyG8

Edit : The issue seems to be that the WHEN condition needs an operator, so I tried using :

WHEN INSERTING = TRUE

But that just resulted in another error :

ORA-00904: "TRUE": Invalid identified

Upvotes: 0

Views: 451

Answers (2)

user5683823
user5683823

Reputation:

As Thorsten explained already, inserting and updating and deleting are predicates (expressions of BOOLEAN data type) which exist only in the PL/SQL code part of the trigger. They can't exist (or be used in any way) in a SQL statement, since Oracle SQL does not recognize/implement the BOOLEAN data type.

Moreover, :new.<whatever> and :old.whatever are also available only in the PL/SQL portion of your trigger; if you have embedded SQL, anything like :new.<whatever> is interpreted as a bind variable, not special in any way, and you will be prompted for a value (if you use an "advanced" front-end program) or you will simply get an error telling you that not all variables are bound.

Thorsten has already shown one way to do what you want to do. If you really need (want?) to use a case expression, you can do something like this:

Declare a variable (like v_value in Thorsten's answer) in the DECLARE section of the trigger. Assign the proper data type to it.

Then in the execution block write something like this, BEFORE the SQL statement:

v_value := case when inserting or updating then :new.column2 else :old.column2 end;

and then in the SQL statement compare to v_value, also as in Thorsten's answer.

Alternatively, you can assign the value to v_value in the DECLARE block, right as you declare it, in the usual (PL/SQL) way. In any case, the value must be assigned outside the SQL statement, not in it.

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

I think the problem is that PL/SQL knows these boolean variables inside a trigger, but Oracle's SQL doesn't know BOOLEAN unfortunately.

One solution may be:

IF INSERTING OR UPDATING THEN
  v_value := :new.column2;
ELSE
  v_value := :old.column2;
END IF;

...

SELECT field INTO my_var FROM my_table WHERE column1 = v_value;

Upvotes: 1

Related Questions