triggers in new and old columns

Question

Why can't we use :new and :old columns in a statement level trigger?

Answer 1

Because the DML could have been set-based, affecting multiple rows in the table. In fact, as SQL is properly set-based that should be the usual case. Consequently there is no way for the statement level triggers to determine which :OLD and which :NEW values you mean.

Answer 2

Because it might be the case that the statement is inserting/deleting/updating more than one row. So there is no new or old column.

Example:

update FOO set a = 12 where b = 9;

Or:

delete from FOO where b = 9;

Or:

insert into FOO (a, b) select 12, x from BAR;

If FOO table had a statement trigger, in these three sentences there is no way to tell if you are operating on none, single or multiple rows.

Answer 3

As said before statement level triggers can be for one to many row changes so :new and :old aren't available.

If you need to track the :new and :old values and need access to them at the statement trigger you can create a row level trigger that stores the new and old values for use by the statement level. Here is one way we have solved this problem before

The package:

create or replace package table_trigger_helper is

  subtype subtype_rowtype is table_name$rowtype;
  type table_rowtype is table of subtype_rowtype;

  v_old table_rowtype := table_rowtype();
  v_new table_rowtype := table_rowtype();

end table_trigger_helper;
/

The row level trigger:

create or replace trigger row_level_trigger_name
  after insert or delete or update
  on table_name
  for each row
declare

  r_old table_trigger_helper.table_rowtype := NULL;
  r_new table_trigger_helper.table_rowtype := NULL;
  i pls_integer;

begin

 if update or deleting then
  r_old.column_one := :old.column_one
  ...
 end if;

 if update or inserting then
  r_new.column_one := :new.column_one
 end if;

  table_trigger_helper.v_old.extend();
  table_trigger_helper.v_new.extend();
  i := table_trigger_helper.v_old.last;

  table_trigger_helper.v_old( i ) := r_old;
  table_trigger_helper.v_new( i ) := r_new;  
end row_level_trigger_name;
/

The statement level trigger:

create or replace trigger statement_level_trigger_name
 after insert or delete or update
 on table_name
declare
begin
  --process through your new and old records;
  --table_trigger_helper.v_old
  --table_trigger_helper.v_new
end statement_level_trigger_name;
/