Posted by I recently came across an example where someone wanted to update primary keys with an INSTEAD-OF trigger (on a view). It can be done, but I wanted to demonstrate that you need to take extreme care, because you can get some problems you might not have forseen.
If you choose to allow primary key updates, then you’ve pretty much got two options:
1) “simple update”
The incoming SQL is say:
update ...
set col7 = :new.col7
where ...
and the instead-of trigger takes a “global” approach, ie, all columns
update ...
set pk = :new.pk,
col1 = :new.col1,
col2 = :new.col2
where ...
where the column list includes the primary keys…
The problem here is that even an update of a primary to the same value, is still an update to the primary key – which means increased locking for any child tables. It means a pretty careful review of any foreign keys, and associated foreign key indexes.
2) “conditional update”
The instead-of trigger checks to see if the PK columns are being altered, and only includes them if appropriate
if :old.pk != :new.pk then
update ...
set pk = :new.pk,
col1 = :new.col1,
col2 = :new.col2
where ...
else
update ...
set col1 = :new.col1,
col2 = :new.col2
where ...
end if;
Still challenges here – because a set operation has now become a row-by-row operation….Consider the following example:
SQL> create table T ( x number primary key, y date );
Table created.
SQL> insert into T values (1,sysdate);
1 row created.
SQL> insert into T values (2,sysdate);
1 row created.
SQL> insert into T values (3,sysdate);
1 row created.
SQL> insert into T values (4,sysdate);
1 row created.
SQL> create or replace
2 view V as select * from T;
View created.
SQL> create or replace
2 trigger TRIG instead of update on V
3 for each row
4 begin
5 update T
6 set x = :new.x
7 where x = :old.x;
8 end;
9 /
Trigger created.
That all seems straightforward enough – but now we’ll compare updates on the base table and view…
SQL> update T set x = x + 1;
4 rows updated.
SQL> roll;
Rollback complete.
No problems there – each primary key was incremented by 1, and then oracle ensured statement level integrity, ie, no duplicates
Now lets try the exact same operation on our view
SQL> update V set x = x + 1;
update V set x = x + 1
*
ERROR at line 1:
ORA-00001: unique constraint (SYS_C00177683) violated
ORA-06512: at "TRIG", line 2
ORA-04088: error during execution of trigger TRIG'
Uh oh….ker splat !
That’s actually a best case scenario – because at least the thing crashed. Things can get really gnarly once you start doing primary key updates across tables. For example:
SQL> create table PARENT ( p number primary key );
Table created.
SQL> create table CHILD ( f_p number constraint FK references PARENT );
Table created.
SQL> insert into PARENT values (1);
1 row created.
SQL> insert into PARENT values (2);
1 row created.
SQL> insert into PARENT values (3);
1 row created.
SQL> insert into CHILD values (1);
1 row created.
SQL> insert into CHILD values (2);
1 row created.
SQL> insert into CHILD values (3);
1 row created.
SQL> create or replace
2 trigger TRIG after update on PARENT
3 for each row
4 begin
5 update CHILD set f_p = :new.p where f_p = :old.p;
6 end;
7 /
Trigger created.
SQL> UPDATE PARENT SET p = p+1;
3 rows updated.
SQL> select * from parent;
P
----------
2
3
4
SQL> select * from child;
F_P
----------
4
4
4
Nothing crashed – but we totally corrupted the child table. This last example is actually also contained in the standard Oracle docs.
And you also have to really ask the question – who or what is updating a primary key ? That’s just a bad idea, triggers or not.
Learning is not a spectator sport 