Dumb triggers? Let’s make ’em a bit smarter

Some time back, Connor rightly pointed out that triggers that modify data can get in the way when you need to do out-of-the-ordinary data maintenance, e.g. when you need to fix up a row here or re-insert a row over there. You can’t just disable the trigger or else make your users suffer down-time.

Now, the only purpose for which I use triggers is to do common things like setting audit columns and incrementing a VERSION_ID column, and in certain special cases for carefully implementing cross-row constraints; also, I use them to populate a journal table with all changes to the table. Mind you, in recent times features have been added and improved in the Oracle database (such as Flashback Query and Flashback Data Archive) to the point where I’m almost ready to stop doing this. However, there are still some minor use-cases where having a separate “journal” table can be useful. Any argument about that assertion is ruled “out of scope” for this article! 🙂

So, assuming we’re sticking with triggers that might change data, a solution to this problem is already built-in to the journal triggers and Table APIs (TAPI) that my PL/SQL code generator creates. This allows me to disable the trigger on any table, just for my current session without affecting any other concurrent activity – and no DDL required.

UPDATED 16/2/2016: now uses a context variable (thanks Connor for the idea)

In the trigger I have this code:

create or replace TRIGGER EMPS$TRG 
  FOR INSERT OR UPDATE OR DELETE ON EMPS 
  COMPOUND TRIGGER 
 
  BEFORE EACH ROW IS 
  BEGIN 
    IF SYS_CONTEXT('SAMPLE_CTX','EMPS$TRG') IS NULL THEN 
      ...etc... 
    END IF; 
  END BEFORE EACH ROW; 
 
  AFTER EACH ROW IS 
  BEGIN 
    IF SYS_CONTEXT('SAMPLE_CTX','EMPS$TRG') IS NULL THEN 
      ...etc... 
    END IF; 
  END AFTER EACH ROW; 
 
END EMPS$TRG;

The trigger takes advantage of some extra code that is generated in the Table API:

create or replace PACKAGE EMPS$TAPI AS 
/***********************************************
 Table API for emps 
 10-FEB-2016 - Generated by SAMPLE
***********************************************/ 

... 
 
-- Use these procedures to disable and re-enable the
-- journal trigger just for this session (to disable for
-- all sessions, just disable the database trigger 
-- instead). 
PROCEDURE disable_journal_trigger; 
PROCEDURE enable_journal_trigger; 
 
END EMPS$TAPI; 

The package body code is quite simple:

create or replace PACKAGE BODY EMPS$TAPI AS 
/***********************************************
 Table API for emps 
 10-FEB-2016 - Generated by SAMPLE
***********************************************/ 
 
...

-- may be used to disable and re-enable the journal trigger for this session 
PROCEDURE disable_journal_trigger IS 
BEGIN 
  log_start('disable_journal_trigger'); 
 
  SECURITY.disable_journal_trigger('EMPS$TRG');
 
  log_end;
EXCEPTION 
  WHEN OTHERS THEN 
    UTIL.log_sqlerrm; 
    RAISE; 
END disable_journal_trigger; 
 
PROCEDURE enable_journal_trigger IS 
BEGIN 
  log_start('enable_journal_trigger'); 
 
  SECURITY.enable_journal_trigger('EMPS$TRG');
 
  log_end; 
EXCEPTION 
  WHEN OTHERS THEN 
    UTIL.log_sqlerrm; 
    RAISE; 
END enable_journal_trigger; 
 
END EMPS$TAPI;

A context variable is set with the name of the trigger to disable it – the default state for a new session (i.e. the context variable not set) means the trigger is enabled.

create or replace PACKAGE BODY SECURITY AS

...

PROCEDURE disable_journal_trigger
  (trigger_name IN VARCHAR2
  ,client_id    IN VARCHAR2 := NULL) IS
BEGIN
  -- set the context to any non-null value
  DBMS_SESSION.set_context 
    (namespace => 'SAMPLE_CTX'
    ,attribute => trigger_name
    ,value     => 'DISABLED'
    ,client_id => NVL(client_id, SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')));
END disable_journal_trigger;

PROCEDURE enable_journal_trigger
  (trigger_name IN VARCHAR2
  ,client_id    IN VARCHAR2 := NULL) IS
BEGIN
  -- clear the context
  DBMS_SESSION.clear_context 
    (namespace => 'SAMPLE_CTX'
    ,attribute => trigger_name
    ,client_id => NVL(client_id, SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')));
END enable_journal_trigger;

END SECURITY;

So now, to run some data maintenance, I can simply call the TAPI to disable, then re-enable, the trigger:

BEGIN EMPS$TAPI.disable_journal_trigger; END;
/

... do the data maintenance...

BEGIN EMPS$TAPI.enable_journal_trigger; END;
/

Unless the data maintenance is doing something very unusual, this script should be safe to run while the system is still up and running for users.

Also, it would be a trivial exercise to write a routine which disables or enables all the journal triggers at once.

The point of this, of course, is that you should be able to do all this sort of thing without writing a lot of code for each table in your schema – solve it for one table, and then generate the code for all your tables.

Source code/download: http://bitbucket.org/jk64/jk64-sample-apex-tapi

APEX API – call a package for all your DML
APEX API for Tabular Forms

Comments

  1. Food for thought …. a global context or result_cache plsql function instead of a package global, could allow selective control of trigger enablement in current or other sessions.

    Cheers,
    Connor

Leave a Reply to Anonymous Cancel reply

Your email address will not be published / Required fields are marked *