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

Oracle 12c column upgrades

While playing with 12c I tried the upgrade to the DEFAULT column syntax that now allows sequences.

I came across a basic error, but it's just a small trap for new players.

CREATE TABLE seq_test(a NUMBER)
/

ALTER TABLE seq_test MODIFY (a NUMBER DEFAULT sage_seq.NEXTVAL)
/

SQL Error: ORA-02262: ORA-2289 occurs while type-checking column default value expression

*Cause:    New column datatype causes type-checking error for existing column
           default value expression.
*Action:   Remove the default value expression or don't alter the column
           datatype.

Investigating ORA-02262 returns next to nothing. It's a red herring anyway because I didn't realise the ORA-2289 staring me in the face before I checked if my database had that sequence yet.
ORA-02289: sequence does not exist

Typical APEX table definitions will never be the same again, though an even more elegant solution is to use IDENTITY columns. See the performance benefits at oracle-base.com.

Related Posts

Decommissioning Triggers in 12c
Synchronise Identity Column

The trick with triggers

Creating triggers, prior to 11g, would default them to an enabled state.

From 11g, we have this in the documentation:

By default, a trigger is created in enabled state. To create a trigger in disabled state, use the DISABLE clause of the CREATE TRIGGER statement.

So when I write my DDL scripts, given this behaviour, I know what I'd prefer:

SQL> create table test_table(a number);
table TEST_TABLE created.
create or replace trigger test_trigger_good before insert on test_table for each row DISABLE
begin
  null -- missing semicolon
end;
/
TRIGGER test_trigger_good compiled
Warning: execution completed with warning

SQL> insert into test_table (a) values (1);

1 rows inserted.

or

SQL> create or replace trigger test_trigger_bad before insert on test_table for each row 
begin
  null -- missing semicolon
end;
/
TRIGGER test_trigger_bad compiled
Warning: execution completed with warning

SQL> insert into test_table (a) values (1);

SQL Error: ORA-04098: trigger 'DEVMGR.TEST_TRIGGER_BAD' is invalid and failed re-validation
04098. 00000 -  "trigger '%s.%s' is invalid and failed re-validation"
*Cause:    A trigger was attempted to be retrieved for execution and was
           found to be invalid.  This also means that compilation/authorization
           failed for the trigger.
*Action:   Options are to resolve the compilation/authorization errors,
           disable the trigger, or drop the trigger.

Only run this when you know things are cool, ie - the triggers does not have compilation errors.

alter trigger test_trigger_good enable;

Otherwise you get the same problem, since you can enable an invalid trigger.

Tip of the hat to Connor McDonald for suggesting this many moons ago.

APEX matures => sequence usage

Of course many of the new features that come with an Oracle product are well documented. In fact, it's not a bad idea to read through the entire release notes for every APEX release because they also include things like changed behaviour.

Some changes in APEX 4.1 are that minor that you wouldn't even notice them until you fall over it.

Something I found recently was the code generated when choosing to source your primary key from a sequence when building a form.

Previously, the code was a little ugly:

declare
  function get_pk return varchar2
  is
  begin
    for c1 in (select EMP_SEQ.nextval next_val
               from dual)
    loop
        return c1.next_val;
    end loop;
  end;
begin
  :P6_EMPNO := get_pk;
end;

Now it's somewhat simpler:

begin
    if :P6_EMPNO is null then
        select "EMP_SEQ".nextval
          into :P6_EMPNO
          from dual;
    end if;
end;

What they haven't done, however, is clean up the custom function example - and this is a little more noticable.

I noted this in EA for 4.0 - I think they said they've logged the change, but it still hasn't come along. While it's not really promoting this sort of code, it doesn't really have a place anywhere except perhaps an article talking about how bad it is for concurrent user environments.

Perhaps sequence usage will change with 12c?

Idle musings... Scott

Better practices within APEX Forms

When developing in Apex, sometimes it's good to check out the little tips, suggestions and reference information that pops up below
It's just a shame some of the examples the present show some better practices, even in Apex 4.0.

SELECT MAX(empno) + 1 ?

How many years have we been telling people to never use that in their code?

And if you select "Existing sequence" you get an ugly looking page process with

declare 
  function get_pk return varchar2 
  is 
  begin 
    for c1 in (select EMP_SEQ.nextval next_val
               from dual)
    loop
        return c1.next_val;
    end loop;
  end; 
begin 
  :P1_EMP_NO := get_pk; 
end; 

As a better alternative, select "Custom PL/SQL function", or if you want to obfuscate it within a trigger you could do it as follows:

CREATE OR REPLACE TRIGGER emp_bi
BEFORE INSERT ON sage.emp
FOR EACH ROW
BEGIN
  IF :NEW.emp_no IS NULL THEN     
    SELECT emp_seq.NEXTVAL 
    INTO :NEW.emp_no 
    FROM dual;
  END IF;
END;
/

With the key line here being 5 - only source from the sequence if the emp_no is not already supplied. If your table is also being populated from another source, it would be more efficient to use
INSERT INTO emp (emp_no, ...) VALUES (emp_seq.NEXTVAL, ...);

I think within the context of most applications in Application Express, where your users are performing data entry at a page level, I don't think it matters if the sequence is populate like this in a page process or trigger.

Also not that in Oracle 11g, in lieu of the implicit cursor you can use
:NEW.emp_no := emp_seq.NEXTVAL
However, last time I ran a trace on this, it was the same as selecting from dual - handy, not a performance improvement.

Any other preferred methods?

Update March 2016
Sven Weller provides the perfect APEX 'trigger'

PRECEDES follows FOLLOWS

Thanks for a tip-off from volleyball coach / Oracle guru Connor McDonald (and later by colleague Chris Muir), it seems 11gR2 was released while I was 30000ft in the sky.

I wouldn't be practising what I preach if I didn't point you to one of the best books in the online Oracle Documentation - the New Features Guide.

If you want to keep up with Oracle Technology, and learn a thing or two, every time a new version of Oracle is release, I highly recommend a peruse through this book.

Keep a lookout in the blog community because plenty of articles pop-up around these times showing off the shiny new features. One feature I'll mention today is an extension to triggers.

In a recent presentation I included some thoughts on compound triggers and a quick note on the FOLLOWS clause, allowing you to indicate that a trigger fire after a specified trigger.

This can be useful if you need to extend proprietary Oracle software.

create or replace trigger package_trigger
after update of salary
on employees
for each row
begin
dbms_output.put_line('package_trigger');
end old_way;
/

create or replace trigger custom_stuff
after update of salary
on employees
for each row
follows package_trigger
begin
dbms_output.put_line('custom_stuff');
end old_way;
/

I don't know whether it was an afterthought or this was just one of the last features installed in 11gR1 at the pleading request of a client, but it was a little odd that a PRECEDES type functionality wasn't included.

However now in 11gR2, this is now available.

There are caveats however, and in this case PRECEDES may only be applied to a reverse cross edition trigger - this is also a whole new ball game and I can't yet confidently tell you more about editions, except that Connor's been excited about the prospect of these for quite some time & it's impact throughout the database seems widespread.

Other features for the handy developer to keep an eye out for include:

• Enhancements to the Forms->Apex conversion process
• Analytical function improvements
• Recursive WITH clause - ANSI compliant hierarchical queries
• Flashback support for DDL
• Improvements to Oracle Scheduler

Oracle keeps on Oracling.