Updating primary keys and triggers

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s