Using Triggers

Why use triggers?
Use of :OLD and :NEW
Cascading Triggers
Mutating Tables
Considerations when using triggers
- Performance
- Caching
Trigger Firing Sequence
AFTER LOGON triggers
Auditing actions via Triggers

Why use triggers?

Triggers are pieces of code which are defined to occur for a given action. As of 9.2, the current triggers actions are

Trigger Type	Example
BEFORE ROW / STATEMENT	BEFORE INSERT [FOR EACH ROW])
AFTER ROW / STATEMENT	AFTER UPDATE [FOR EACH ROW])
INSTEAD OF (* - applies to views only)	INSTEAD OF UPDATE
System Event	BEFORE SHUTDOWN, AFTER STARTUP, SERVERERROR
Client Event	AFTER LOGON, BEFORE CREATE

They can be incredibly useful for actions such as auditing and initialisation. This is a quick example of how to create a trigger :

SQL> CREATE TABLE t ( a VARCHAR2(10) );

Table created.

SQL> CREATE TRIGGER t_trig
  2  BEFORE INSERT ON t
  3  FOR EACH ROW
  4  BEGIN
  5    NULL;
  6  END t_trig;
  7  /

Trigger created.

*DEV NOTE*
BEFORE or AFTER ROW / STATEMENT Triggers can be multiple types, and you test for which condition is occurring via the INSERTING / UPDATING / DELETING built-ins, i.e.

SQL> CREATE OR REPLACE TRIGGER t_trig
  2  BEFORE INSERT OR UPDATE OR DELETE ON t
  3  FOR EACH ROW
  4  BEGIN
  5    IF DELETING
  6    THEN
  7      NULL;
  8    ELSIF UPDATING
  9    THEN
 10      NULL;
 11    ELSIF INSERTING
 12    THEN
 13      NULL;
 14    END IF;
 15  END t_trig;
 16  /

Trigger created.

Use of :OLD and :NEW

In each BEFORE or AFTER ROW trigger, there are mechanisms for accessing the values of the columns of the table before and after the modification has taken place, i.e.

SQL> CREATE OR REPLACE TRIGGER t_trig
  2  AFTER INSERT ON t
  3  FOR EACH ROW
  4  BEGIN
  5    IF :old.a <> :new.a
  6    THEN
  7      NULL;
  8    END IF;
  9  END t_trig;
 10  /

Trigger created.

There are caveats to remember though. :OLD and :NEW only apply to ROW triggers (but this makes sense of course), i.e.

SQL> CREATE OR REPLACE TRIGGER t_trig
  2  AFTER INSERT ON t
  3  BEGIN
  4    debug('OLD A : ' || :old.a);
  5    debug('NEW A : ' || :new.a);
  6  END t_trig;
  7  /
CREATE OR REPLACE TRIGGER t_trig
                          *
ERROR at line 1:
ORA-04082: NEW or OLD references not allowed in table level triggers

So what are the values of :NEW and :OLD for triggers, well, fortunately, you can easily tabulate this :

Trigger Type	:OLD	:NEW
BEFORE INSERT	NULL	INSERTed value
AFTER INSERT	NULL	INSERTed value
BEFORE UPDATE	Value before UPDATE	Value after UPDATE
AFTER UPDATE	Value before UPDATE	Value after UPDATE
BEFORE DELETE	Value before DELETE	NULL
AFTER DELETE	Value before DELETE	NULL

Why have BEFORE and AFTER triggers? Well, it's really down to validation. You may only want the code to run if the row has been successfully INSERTed, for example, or you may want the ATTEMPT at the operation audited, and not necessarily only if successful, etc. etc.

Cascading Triggers

Frequently asked question :

Are cascading triggers all subject to the commit/rollback of the initiating task? If a task updates a table that has a trigger associated with it that updates a second table that has a trigger associated with ... are all updates subject to the initiating tasks commit/rollback?

Historically and by default -- YES. Triggers are considered all part of the same exact transaction.

Not only are they part of the transaction but they are atomic with respect to the firing statment. What I mean by that is if you have tables T1, T2 and T3 and table T1 has a trigger to insert into T2 and T2 has one to insert into T3 and you execute:

  insert into T1 values ( ... );

either:

both triggers and all three INSERTS succeed, or
all fail and are undone.

the insert is atomic -- its side effects (the triggers) are part of it. Either all of the triggers fire and succeed or they are all undone. To take that further lets say you execute:

   insert into some_table values ( ... );  /* this succeeds */
   insert into t1 values ( .... ); /* this fails */

After executing these two statements -- the work done by the second insert is "undone" (including any side effects from the triggers) but the work done by the FIRST insert is still there -- you need to either commit it or roll it back.

Now, there are somethings you can do in plsql that are not transactional -- writing to a file for example, or changing the value of a global variable in a package. These operations are NOT undone -- only the inserts/updates/deletes you do are. Therefore, if the trigger writes to an ascii file -- then closes the file and then FAILS -- the file will still exist, we cannot roll that back. In cases like this where I want the file to exists IF and ONLY IF the transaction succeeds, I use DBMS_JOB to schedule the procedure to write the file right AFTER I commit. In that fashion -- that operation that cannot be rolled back will execute after I succeed (and if it fails, the job queues will report the error to me via enterprise manager or a simple query).

In the beginning, I said "historically". Starting with Oracle8i, release 8.1 -- it is possible to have an "autonomous" transaction. they break this rule. See http://osi.oracle.com/~tkyte/autonomous/index.html for info on that feature.

Mutating Tables

There are restrictions on the use of certain actions within certain triggers, but the most common is the mutating table error, i.e.

ORA-04091: table XXXX is mutating, trigger/function may not see it

The reasons behind this are quite complex, and can be found in the Oracle Documentation. The main reason, however, is attempting to read from a table which is currently being acted upon by a statement, i.e.

An AFTER ROW trigger cannot read from the same table it is defined against, etc.

Workarounds for this (and the reasons why you should never hit them anyway), can be found http://osi.oracle.com/~tkyte/Mutate/index.html.

Considerations when using triggers

There are various things to consider when using triggers to achieve various functionality :

Performance

The use of triggers for auditing purposes (say, such as populating a created by or updated by column) will be SIGNIFICANTLY quicker if done in the original statement, since there will be no context switch into the PL/SQL environment of the trigger. This is trivial to show :

Let's specify the user in the INSERT statement directly :

SQL> INSERT INTO t
  2  SELECT
  3    user
  4  FROM dba_objects
  5  WHERE rownum < 10000;

9999 rows created.

Elapsed: 00:00:00.05

Takes much less than a second to INSERT 9999 rows.

Now, let's achieve the same result via a trigger :

SQL> CREATE TRIGGER t_trig
  2  BEFORE INSERT ON t
  3  FOR EACH ROW
  4  BEGIN
  5    :new.a := user;
  6  END t_trig;
  7  /

Trigger created.

SQL> INSERT INTO t
  2  SELECT
  3    NULL
  4  FROM dba_objects
  5  WHERE rownum < 10000;

9999 rows created.

Elapsed: 00:00:05.04

SIGNIFICANTLY slower.

Caching

The code within triggers is session cached in the same way as any other PL/SQL code, but any SQL statements within the trigger may be reparsed. You can prevent this reparsing by simply embedding the SQL statement in a stored procedure. This is easily demonstrated, via TKPROF :

SQL> CREATE TABLE t ( a VARCHAR2(10) );

Table created.

SQL> CREATE OR REPLACE PROCEDURE p
  2  AS
  3    l_count   NUMBER;
  4  BEGIN
  5    SELECT count(*) INTO l_count FROM dual IN_PROCEDURE;
  6  END p;
  7  /

Procedure created.

SQL> CREATE OR REPLACE TRIGGER t_trig
  2  BEFORE INSERT ON t
  3  FOR EACH ROW
  4  DECLARE
  5    l_count  NUMBER;
  6  BEGIN
  7    SELECT COUNT(*) INTO l_count FROM dual IN_TRIGGER;
  8    p;
  9  END t_trig;
 10  /

Trigger created.

Now, do a few inserts with SQL_TRACE=TRUE and look at the resultant trace file :

SQL> ALTER SESSION SET SQL_TRACE=TRUE;

Session altered.

SQL> INSERT INTO t VALUES ('X');

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> ALTER SESSION SET SQL_TRACE=FALSE;

Session altered.

Now, utilising TKPROF on the resultant output file, we see the following output snippet, showing that the IN_TRIGGER select, i.e. the SQL directly within the trigger is parsed 5 times (each INSERT), whereas the IN_PROCEDURE SQL, i.e. the same SQL within the stored procedure, is parsed only once, and cached for the rest of the time :

SELECT COUNT(*)
FROM
 dual IN_TRIGGER

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.00       0.00          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch        5      0.00       0.00          0         15          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       15      0.00       0.00          0         15          0           5

SELECT count(*)
FROM
 dual IN_PROCEDURE

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch        5      0.00       0.00          0         15          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       11      0.01       0.00          0         15          0           5

For further information, and an in depth discussion of this, see http://asktom.oracle.com/pls/ask/f?p=4950:8:9261255159918217703::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:7507706875149,.

The syntax of triggers allows for a stored procedure to be called directly, so any business functionality being called by a trigger should be utilised in a stored procedure (in a package, of course!), i.e.

SQL> CREATE OR REPLACE TRIGGER t_trig
  2  BEFORE INSERT ON t
  3  FOR EACH ROW
  4  CALL p
  5  /

Trigger created.

*DEV NOTE*
The above logic of inserting 9999 rows will not be seriously improved by this approach, however, i.e. :

SQL> CREATE OR REPLACE PROCEDURE p ( user_name OUT VARCHAR2 )
  2  AS
  3  BEGIN
  4    user_name := user;
  5  END p;
  6  /

Procedure created.

SQL> CREATE OR REPLACE TRIGGER t_trig
  2  BEFORE INSERT ON t
  3  FOR EACH ROW
  4  CALL p(:new.a)
  5  /

Trigger created.

SQL> INSERT INTO t
  2  SELECT
  3    NULL
  4  FROM dba_objects
  5  WHERE rownum < 10000;

9999 rows created.

Elapsed: 00:00:05.04

It will only be situations where the trigger calls PL/SQL logic which will benefit.

Trigger Firing Sequence

An often asked question is what order do triggers fire? The answer is that while types of trigger have an order, you cannot guarantee that two triggers of the same type will fire in a particular order, i.e. two BEFORE UPDATE triggers on the same table can fire in ANY order.

It is considered EXTREMELY BAD design if the system relies on the order of same-type triggers, i.e. it WILL NOT always work.

For more information, see Metalink note : 121196.1.

Note, at 11g, there now exists the FOLLOWS keyword as part of the CREATE OR REPLACE TRIGGER syntax. This allows you to specify that trigger "b" fires after trigger "a".

AFTER LOGON triggers

AFTER LOGON triggers are a great way of executing code after a user has logged on. This can be used for a multitude of purposes, e.g. SQL tracing, auditing, intialising variables (particularly in the case of Fine Grained Access Control), etc.

The syntax for creating them is :

CREATE OR REPLACE TRIGGER <trigger_name>
AFTER LOGON ON DATABASE
[ WHEN <condition> ]
BEGIN
  <code_block>;
END;

An example to switch on SQL tracing for a particular user, would therefore be,

CREATE OR REPLACE TRIGGER sadmin_logon_trg
AFTER LOGON ON DATABASE
WHEN (user="SCOTT")           -- remove this line to turn it on for all users.
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE = TRUE';
END;

*IMPORTANT*
AFTER LOGON triggers fire for users with the DBA role, but they cannot be used to prevent DBAs from logging in, this is to prevent a trigger stopping ALL access to a database, i.e.

/* Create a table and autonomous transaction procedure to audit logons */

SQL> CREATE TABLE temp ( a  VARCHAR2(10) );

Table created.

SQL> CREATE PROCEDURE temp_proc
  2  AS
  3    PRAGMA AUTONOMOUS_TRANSACTION;
  4  BEGIN
  5    INSERT INTO temp VALUES ('LOGGED ON');
  6    COMMIT;
  7  END temp_proc;
  8  /

Procedure created.

/* Create the trigger */

SQL> CREATE OR REPLACE TRIGGER logon_trig
  2  AFTER LOGON ON DATABASE
  3  WHEN (user='ORAUSER')
  4  BEGIN
  5    temp_proc;
  6    RAISE_APPLICATION_ERROR(-20000, 'Sorry, you cannot login');
  7  END logon_trig;
  8  /

Trigger created.

SQL> TRUNCATE TABLE temp;

Table truncated.

SQL> REVOKE dba FROM orauser;

Revoke succeeded.

SQL> conn
Enter user-name: orauser
Enter password: ***********
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Sorry, you cannot login
ORA-06512: at line 3

Warning: You are no longer connected to ORACLE.

SQL> conn
Enter user-name: sys
Enter password: ************************
Connected.

SQL> GRANT DBA TO ORAUSER;

Grant succeeded.

SQL> conn
Enter user-name: orauser
Enter password: ***********
Connected.

SQL> SELECT * FROM temp;

A
----------
LOGGED ON
LOGGED ON       -- Second row proves that the trigger fires for the DBA login, even though the
                          RAISE_APPLICATION_ERROR does not affect the logon process.

2 rows selected.

*IMPORTANT*
AFTER LOGON triggers DO NOT fire AT ALL for INTERNAL / SYSDBA users, i.e.

SQL> CREATE OR REPLACE TRIGGER logon_trig
  2  AFTER LOGON ON DATABASE
  3  BEGIN
  4    RAISE_APPLICATION_ERROR(-20000, 'Sorry, you cannot login');
  5  END logon_trig;
  6  /

Trigger created.

SQL> CONN
Enter user-name: orauser
Enter password: ***********
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Sorry, you cannot login
ORA-06512: at line 2

Warning: You are no longer connected to ORACLE.

SQL> CONN
Enter user-name: sys
Enter password: ************************
Connected.

Auditing actions via Triggers

It's a common requirement to want to audit various actions, such as INSERTs, UPDATEs etc., but also, it may be required to audit DDL such as CREATEs, ALTERs, DROPs etc. These can now all be done via System Event triggers. For more information (and a full list of system events), see http://download-west.oracle.com/docs/cd/B13789_01/appdev.101/b10795/adfns_ev.htm#1006973 (10g documentation).

Here's a script which I use to audit CREATE, ALTER and DROP events on a SCHEMA (or DATABASE if required).

Note the use of the ORA_ functions for getting various information. More ORA_ functions are available, such as ora_sql_txt, and are given in the above link, again noting that it is the 10g documentation.

CREATE OR REPLACE TRIGGER ddl_trigger
AFTER CREATE OR ALTER OR DROP ON SCHEMA
BEGIN
    INSERT INTO log
    SELECT
      ora_sysevent,
      ora_dict_obj_owner,
      ora_dict_obj_name,
      ora_dict_obj_type,
      TO_CHAR(sysdate,'FM DD/MON/YYYY HH:MI:SS AM'),
      machine,
      terminal,
      program,
      osuser
    FROM v$session
    WHERE audsid = TO_NUMBER(sys_context('userenv', 'sessionid'));
END;