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 FrameworkConsider 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 ISSIt's not rocket surgery...
Audit columnsThere 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 sequenceThe 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 columnsIdentity 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 triggerHere is the best part:
drop trigger orbiters_biur;
ConclusionTo 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 user; drop trigger orbiters_biur;