How to use a quoted “referencing” identifier in an Oracle trigger body

user_521799 0
Viewed 260

Assuming I have a trigger specification like this:

create table "t" (
  "i" number(10)
);

create or replace trigger "trg"
before insert on "t"
referencing new as "n"
for each row
begin
  null;
end;

How can I reference the "n" identifier from within the body? None of these work:

"n"."i" := 2;
:"n"."i" := 2;
":n"."i" := 2;
:n."i" := 2;

All of these attempts compile, but produce this error when trying to insert into the table:

ORA-04098: trigger 'TEST.trg' is invalid and failed re-validation

Obviously, I can avoid quoting the variable name, but 1) this is about code generation from a tool, which has to get this syntax right regardless of the quoting, and 2) I'm curious about the correct syntax.

Bind variables

In the CREATE TRIGGER docs, there's a reference to (emphasis mine):

In the trigger_body of a simple trigger or the tps_body of a compound trigger, a correlation name is a placeholder for a bind variable.

It seems that bind variables in general do not support quoting, e.g. while this works:

BEGIN
  EXECUTE IMMEDIATE 'BEGIN dbms_output.put_line(:x); END;' USING 'a';
END;

This, or any similar syntax, doesn't:

BEGIN
  EXECUTE IMMEDIATE 'BEGIN dbms_output.put_line(:"x"); END;' USING 'a';
END;

So, this seems consistent, but I'm still curious about the specification of why I can declare the name like this, but cannot seem to reference it.

The database version is Oracle Database 18c Express Edition Release 18.0.0.0.0

triggers plsql oracle sql
1 Answers

According to the documentation, these are not normal identifiers, but "correlation names", expected to be new, old or parent:

Specifies correlation names, which refer to old, new, and parent values of the current row. Defaults: OLD, NEW, and PARENT.

Alternative names may be used:

If your trigger is associated with a table named OLD, NEW, or PARENT, then use this clause to specify different correlation names to avoid confusion between the table names and the correlation names.

But you cannot use " in any references to the correlation name. Interestingly, if the identifier is all uppercase, you can successfully reference it:

create or replace trigger "trg"
before insert on "t"
referencing new as "N"
for each row
begin
  :N."i" := 2;
  :n."i" := 2;
end;

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/CREATE-TRIGGER-statement.html#GUID-AF9E33F1-64D1-4382-A6A4-EC33C36F237B__BABEBAAB

user_1032950
Related