Stupid things to do with triggers: Pt.42 in a series of 94

As is well known, the concept of :OLD values in an INSERT trigger makes no sense. Obviously there is no OLD record in such a scenario case. What is perhaps less well known is that Oracle will let us build an INSERT trigger which references an :OLD value.

SQL> create or replace trigger t1
  2  before insert on t1
  3  for each row
  4  when ((old.col2 is null) or (new.col3 is null))
  5  begin
  6      if :old.col2 is null then
  7          :new.col3 := 'COL2 is null';
  8      elsif :new.col3 is null then
  9          :new.col3 := 'COL3 is null';
 10      end if;
 11  end;
 12  /

Trigger created.

SQL> 

This means we can use the trigger to corrupt our own data without really trying...

SQL> insert into t1 (col1) values (1)
  2  /

1 row created.

SQL> insert into t1 (col1,col2) values (99,56)
  2  /

1 row created.

SQL> select * from t1
  2  /
      COL1       COL2 COL3
---------- ---------- --------------------------------
         1            COL2 is null
        99         56 COL2 is null

SQL>

Obviously it is unlikely that anybody would deliberately set out to do this. But it is the sort of mistake we might make by confusing the scope of variables between the INSERT and UPDATE events. Another good reason for having separate triggers for each event. Or indeed, if you are of the anti-trigger persuasion, another good reason why triggers should never have been invented in the first place.