6

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

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509

1 Answers1

3

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

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
  • Interesting, calling the value `"N"` and referencing it as `:N` works. A correlation name to me is a name of a table, and a table name can be quoted. Compare this to the usage of the term "correlation name" in the [`SELECT` clause documentation](https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6), e.g. when using table aliases. I personally think that's just a parser bug. `:"n"` seems to be reasonable to me. – Lukas Eder Feb 02 '21 at 11:42
  • jinx :) got that one :) – Jeffrey Kemp Feb 02 '21 at 11:43
  • Agreed, it would be preferred that the syntax is consistent. I'm not sure if there would be enough demand, in this particular instance, for it to be fixed, unfortunately. – Jeffrey Kemp Feb 02 '21 at 11:44
  • I updated my question with a hint about the syntax being about bind variables. – Lukas Eder Feb 02 '21 at 11:51
  • I believe it's significant to distinguish a *bind variable* as distinct from a *placeholder for a bind variable*. – Jeffrey Kemp Feb 02 '21 at 11:52
  • It seems important, yes. But what does it mean :) – Lukas Eder Feb 02 '21 at 11:53
  • And why does the ojdbc driver not make that distinction, thinking I missed a bind variable for the `:n` placeholder, when creating a trigger as a prepared statement? – Lukas Eder Feb 02 '21 at 12:24