About triggers

A trigger is a named PL/SQL unit that is stored in the database and fired (run) in response to a specified event. The specified event is defined on (associated with) either a table, a view, a schema, or the database, and it is one of these:

  • The database manipulation language (DML) statement DELETE, INSERT, or UPDATE
  • A database definition language (DDL) statement such as CREATE, ALTER, or DROP
  • A database operation such as SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN

Triggers can be categorized in 3 groups:

  • DML triggers;
    • simpe DML triggers; are defined for a tables or views and fire for a specific timing point, like before or after a DML statement is executed
    • Compound trigger (11g); are defined for tables or views and fire at multiple timing points, like before and after a statement is executed
  • System or non-DML triggers;
    • DDL triggers; are defined for against DLL statement; like fire for when a CREATE statement is executed
    • Database triggers; are defined for database operation; like fire when a user log onto the database
  • Crossedition trigger(11); for their special purpose categorized apart, but technically a dml trigger (mostly compound)

Good trigger practices

In general there are a few guidelines you should adhere to in the creation of any trigger. When your completely new to triggers some of these might not make direct sense to you know, but read on and they will become clear.

Trigger Don’ts

  • Do not create recursive triggers
    • Do not create recursive triggers
      • Do not create recursive triggers
        • Do not create recursive triggers
  • Do not implement LOGON triggers without a WHEN OTHERS EXCEPTION clause
    • An unhandled exception might block every database connection

If you follow the don’t it will save you the wrath of your DBA:

  • Recursive triggering is not a good idea as the loop will continue until the database runs out of memory or a very angry DBA intervenes
  • Locking out every database user in production doesn’t earn you any points with the DBA either.

Trigger Do’s

  • Limit the size of triggers
    • 32k; hard limit
    • 60 lines; Oracle recommended limit
    • package larger structures and invoke from trigger
  • If the same result can be achieved using a CONSTRAINT, use a CONSTRAINT
  • Use WHEN clause to prevent unnecessary trigger body execution
  • Be sparse in the use of SYSTEM triggers as they are executed each time for every session

If you follow the do’s it will make your applications perform better.

trigger vs constraint

Triggers and constraints show some similarity, but there are distinct differences as well. Some of the same effects can be accomplished by both constraints and triggers. However how the deliver the effect is a completely different approach. A constraint is much more elementary at table level. Triggers can only alter/cancel out the effect of DML statements on a table.

Similarities:

  • Triggers can do things constraints can also, like enforcing business rules
  • Constraints can do things triggers can do also, like restricting table input

There are differences:

  • A constraints makes an always true statement about the database; they constrain existing and future table data and what a DML statement can do
  • A trigger can only constrain what a (future) DML statement can do; they have no effect on data (already) in the table.

Recommendation when enforcing business rules:

  • If a constrain can do it, use a constraint
  • Use triggers to enforce complex business rules a constraint cannot enforce

General syntax

Simply put, the body is a pl/sql block.  The trigger body contains the logic you want enforced. There is no need to discuss the structure of an anonymous block, but there are some caveats which are good to understand. First the general syntax

...
[DECLARE]
BEGIN
statements
[EXCEPTION]
END [trigger_name];

Trigger body can consist of either

  • a CALL routine (to a PL/SQL subprogram or a PL/SQL wrapped Java program)
  • a pl/sql block, which can include special constructs
    • pseudocolumns
    • conditional predicates
  • a compound trigger block (compound triggers only)

Scope of the trigger body

  • The trigger body runs in the same transaction scope as the DML that fired the trigger

Exception handling in the trigger body

  • If an exception is unhandled all effects of the both the trigger body and the DML statement are rolled back
  • 3 exceptions on that prior rule exist
    • database startup/shutdown operations do not fail; only the trigger effects are rolled back;
    • if DATABASE LOGON trigger and the user has ADMINISTER DATABASE TRIGGER privilege. logon is permitted; only the trigger effects are rolled back;
    • if SCHEMA LOGON trigger and the user has ALTER ANY TRIGGER privilege, logon is permitted; only the trigger effects are rolled back.

SQL restrictions

  • The trigger body should cannot contain transactional control statements such as COMMIT
  • Nor should any subprogram invoked from a trigger
  • A select statement can only be used in a cursor declaration
  • Only a SYSTEM trigger can contain these SQL statements
    • CREATE TABLE ...
    • ALTER TABLE ...
    • DROP TABLE ...
    • ALTER ... COMPILE

Mutating table restrictions

a mutation table is a table that is being modified by a DML statement or the effects of a DELETE CASCADE constraint

  • the session that issues the triggering statement can not query of update a mutating table
  • the restriction applies to all row-level triggers
  • when a mutation table is encountered the effects of the trigger body and the DML that fired the trigger are rolled back;
  • the restriction can be bypassed using a temporary table or a compound trigger (11g)

System trigger restrictions

  • When using event attribute function, check if they are valid. They might return NULL instead of producing an error, causing unpredictable behaviour
  • Only commited triggers fire; thus if a AFTER CREATE trigger is created, the creation of the trigger, does not fire itself.

Conditonal Predicates

Conditional predicates can be useful if you’ve created a trigger that fires for multiple event like BEFORE INSERTING OR UPDATING on table_name.
Using them in conditional constructs allows you to conditionally execute parts of the trigger body only if the an INSERT statement fire the trigger and still fire common code which needs to be fired for both INSERTING and UPDATING.

Especially when you have bigger code blocks which should fire for multiple DML and just small pieces that should only fire for specific DML combining multiple trigger events into 1 trigger using conditional predicates can save you from repeating your code.

Predicates are

  • defined as BOOLEAN values
  • you can use to detect the DML operation that fired the trigger

4 distinct predicates

  • INSERTING; true only if an insert statement fired the trigger
  • UPDATING; true only if an update statement fired the trigger
  • UPDATING (‘column_name’); true only if an update statement that affects the column, fired¬† the trigger
  • DELETING; true only if a delete statement fired the trigger

Predicate syntax examples

IF UPDATING(field) THEN
... specific code
ELSIF INSERTING OR DELETING
... specific code
ELSE UPDATING
... specific code
END IF;
... generic code

CASE
WHEN INSERTING ... specific code
WHEN UPDATING ... specific code
END CASE;
... generic code

That’s the general talk about triggers. Stay tuned for more on triggers.


Posted in Oracle, Triggers Tagged with: ,

Leave a Reply