Use of 'REFERENCING NEW AS NEW OLD AS OLD ' while creating a trigger

Question

I found two similar trigger on different table.

create or replace TRIGGER "GM_OWNER".CHG_TYPE_TRG
AFTER INSERT OR UPDATE OR DELETE
ON CHG_TYPE
FOR EACH ROW
BEGIN
    If Inserting Then
       INSERT INTO CHG_TYPE_H

and

create or replace TRIGGER invoice_trg
AFTER INSERT  OR  DELETE  OR UPDATE
ON invoice
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
    If Inserting Then
       INSERT INTO INVOICE_H

Both trigger inserting or updating or deleting data in Hist table related to particular table. What is the use of 'REFERENCING NEW AS NEW OLD AS OLD' when we are already using :old and :new to reference the data?

Answer 1

:new and :old are the default names to address the values of the old and new record.

You can name them something else using REFERENCING NEW AS A OLD AS B for example.

Answer 2

If you omit the referencing clause then the defaults are used; if you include it then you can keep the defaults, or change a specific name while leaving the others defaulted.

So if you don't want to change the correlation names, there doesn't seem to be any point including that clause - some might see it as being a good thing as it's more explicit, but personally I'd see it as noise to just restate the defaults.

As to why you might want to change the names from the defaults, the documentation says:

referencing_clause

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

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.

The confusion part seems to be just from the trigger writer's point of view, as there doesn't seem to be a problem using the same name as the table the trigger is on, or a column etc.:

create table new (id number, new number);

create trigger trig
before insert on new
for each row
begin
  :new.new := 2;
end;
/

insert into new (id) values (1);

select * from new;

        ID        NEW
---------- ----------
         1          2

The documentation actually has an example which says "To avoid conflict between the table name and the correlation name, ..." but there doesn't seem to be any actual conflict. It could be seen as clearer to use a different name though.

If you had a table with a 'conflicting' name, I think it would be less confusing to stick to the defaults anyway, since :old and :new references are instantly recognisable, and anything else would just add (a tiny amount of) cognitive load.

Of course, as OLD, NEW and PARENT are keywords (but not reserved words) you should probably avoid using them as identifiers anyway, but you are allowed to.