Dear Patrick,

We have bought an application that runs on an Oracle database. There are triggers defined on the tables, but we want to add our own code to these triggers, but we don’t have access to the source code. What is the approach we should follow to accomplish this?

Collin Bratforth

Dear Collin,

There are two types of DML triggers. Statement level triggers and row level triggers. Then for these two types there are two triggering moments, before and after. So you get four trigger moments for each DML operation. The statement level triggers fire once for each statement, the row level triggers fire once for each row. This gives you enough moments to add your code. If however you want to add code to a triggering moment already used by the application, you can just add another trigger which will fire at the same moment as the existing one.
This should give you plenty of possibilities to add your own code. Since Oracle 8i you can define multiple triggers at the same firing event, but if you define two or more triggers on the same firing event there is no way to tell which trigger will always fire first. You might think it is based on the creation order or an alphabetical order of the object name, but that is not the case. If the code executed in the triggers is not dependent on each other, then there is no problem, but what if one trigger gets the next value of a sequence and in the other trigger you want to use the current value, you might run into a problem. If the second trigger fires first then the current value of the sequence is either not defined or still holds an old value (previously defined in the session) which is both not correct.

CREATE TABLE trigger_demo
( ID NUMBER
, VALUE VARCHAR2(30)
, first_trigger_value VARCHAR2(30)
, second_trigger_value VARCHAR2(30)
)
/

CREATE SEQUENCE trigger_demo_seq START WITH 1 NOCACHE
/

CREATE OR REPLACE TRIGGER first_trigger
  BEFORE INSERT ON trigger_demo
  FOR EACH ROW
BEGIN
  :new.id                  := trigger_demo_seq.nextval;
  :new.first_trigger_value := 'First Trigger ' || to_char(trigger_demo_seq.currval);
END;
/

CREATE OR REPLACE TRIGGER second_trigger
  BEFORE INSERT ON trigger_demo
  FOR EACH ROW
BEGIN
  :new.second_trigger_value := 'Second Trigger ' || to_char(trigger_demo_seq.currval);
END;
/

INSERT INTO trigger_demo
  (VALUE)
VALUES
  ('Patrick')
/

SELECT *
  FROM trigger_demo
/

If you run this code, chances are about fifty-fifty that you will run into this error:

ORA-08002: sequence TRIGGER_DEMO_SEQ.CURRVAL is not yet defined in this session
ORA-06512: at "SCOTT.SECOND_TRIGGER", line 2
ORA-04088: error during execution of trigger 

The reason is that SCOTT.SECOND_TRIGGER fires before SCOTT.FIRST_TRIGGER fires and trigger_demo_seq.currval is only defined after trigger_demo_seq.nextval has been called which only happens in SCOTT.FIRST_TRIGGER.
Since Oracle 11g you have the possibility to tell the trigger to fire after another trigger. You can do this by adding the FOLLOWS clause to the trigger. This way you can make sure your trigger gets fired after the other one.

CREATE OR REPLACE TRIGGER second_trigger
  BEFORE INSERT ON trigger_demo
  FOR EACH ROW
  FOLLOWS first_trigger
BEGIN
  :new.second_trigger_value := 'Second Trigger ' || to_char(trigger_demo_seq.currval);
END;
/

INSERT INTO trigger_demo
  (VALUE)
VALUES
  ('Patrick')
/

SELECT *
  FROM trigger_demo
/

This way the second_trigger gets fired after the first_trigger. Unfortunately there is no preceding clause for the triggers, so you cannot have triggers get fired before the original code. There is a preceding clause available but this is reserved for cross edition triggers, which are part of Edition Based Redefinition, but that is a whole different subject.
I hope this answers your question.

Happy Oracle’ing,
Patrick Barel

If you have any comments on this subject or you have a question you want answered, please send an email to patrick[at]bar-solutions[dot]com. If I know the answer, or can find it for you, maybe I can help.

This question has been published in OTech Magazine of Fall 2015.