Monday 3 October 2016

Decommissioning triggers in 12c

I've been operating with a 12c environment this year and I can see some standard patterns of mine changing.

One is the use of triggers, or lack thereof. I live in a city with a certain evangelist who does not like triggers, so I was happy to see Sven Weller's "perfect trigger" post. The answer is: there is no trigger.


Well, it's one thing to say 'create all new tables like this', but what about our existing structure? What process should we follow to decommission these triggers?

If you take the weekly DB Design quiz at the PL/SQL Challenge website, you may recognise this example as a quiz from Sept 2016.

Existing Framework

Consider a table with a structure similar to the one I described in a post of mine from 2010
drop table orbiters;
drop sequence orbiter_seq;

create sequence orbiter_seq;

create table orbiters
 (id  number not null
 ,CONSTRAINT orbiter_pk PRIMARY KEY (id)
 ,position    number not null
 ,planet varchar2(15) not null
 ,name   varchar2(30) not null
 ,distance  number not null
 ,created_date date not null
 ,created_by varchar2(50) not null
 );

create or replace TRIGGER orbiters_biur BEFORE INSERT OR UPDATE ON orbiters FOR EACH ROW DECLARE
BEGIN
  IF INSERTING THEN
    :NEW.id            := COALESCE(:NEW.id           , orbiter_seq.NEXTVAL);
    :NEW.created_date  := COALESCE(:NEW.created_date , SYSDATE);
    :NEW.created_by    := COALESCE(:NEW.created_by   , apex_application.g_user, USER);
  END IF;
END;
/

insert into orbiters (position,planet,name,distance) values (3,'Earth', 'Luna', 384) ;
insert into orbiters (position,planet,name,distance) values (3,'Earth', 'SpaceX', 400/1000);
insert into orbiters (position,planet,name,distance) values (3,'Earth', 'ISS', 400/1000);

select id, name from orbiters;

ID  NAME
1   Luna
2   SpaceX
3   ISS
It's not rocket surgery...

Audit columns

There are plenty of options about in regard to auditing your columns and tracking history, but for the pattern you're likely to see regularly, there is no need for a trigger.
alter table orbiters modify created_date default sysdate ;
alter table orbiters modify  created_by default coalesce(
          sys_context('APEX$SESSION','app_user')
         ,regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')
         ,sys_context('userenv','session_user')
         );
Sven's coalesce to resolve the username when the environment may or may not be APEX is neat.

Default using sequence

The sequence itself has no performance benefit using an assignment vs selecting from dual. Instead, it needs to either be in the insert statement, or as a default value as 12c (finally) allows.
alter table orbiters modify id default orbiter_seq.NEXTVAL;
drop trigger orbiters_biur;
If you get ORA-02262, check your sequence exists and you've typed it properly.

Identity columns

Identity columns are ideal for new tables, but existing tables would require more work.

Identity columns actually use a sequence under the hood, so while I thought I was on the right track by using this syntax:
alter table orbiters modify id GENERATED BY DEFAULT ON NULL AS IDENTITY start with 10;
It returns the following error.
ORA-30673: column to be modified is not an identity column
A few bloggers mention this fact, someone posted the question on Reddit of all places.

But I think the value equation of creating a new column and shifting data, foreign key references etc is outweighed by the fact that performance is better regardless of using identity vs sequence. Tim demonstrates this here. I discuss resetting sequences in identity columns here.

Definitely remove the trigger

Here is the best part:
drop trigger orbiters_biur;

Conclusion

To decommission triggers and replace them with new 12c features, use these steps:

alter table orbiters modify id default orbiter_seq.NEXTVAL;
alter table orbiters modify created_date default sysdate;
alter table orbiters modify created_by default sys_context('APEX$SESSION','app_user');
drop trigger orbiters_biur;

Happy #db12c!

Related Posts

12 Column Upgrades
Synchronise Identity Column

2 comments:

DrabJay said...

Hi Scott

To me it is not that triggers are inherently bad; it is the "automagic" functionality that developers include within them that can cause problems and side-effects that are difficult to track down when debugging. The fact that these types of triggers can be replaced by more performant, built-in statements does not remove the fact that these "automagic" actions will occur.

I often use triggers; but only to properly enforce the complex data integrity rules required taking into account such things as concurrency issues. I would not include triggers or similar functionality that perform an action - such as defaulting column values or populating audit tables - but keep these co-located with the associated DML statements so it explicit when looking at the code what will occur.

Scott Wesley said...

DrabJay: You're right, I guess I shouldn't portray is as such an edict, some triggers serve essential purposes. Often security related; sometimes just to help assert data.

I'm targeting triggers that invoke superfluous DML.