Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

EnterpriseDB: Declaring and Understanding Triggers Oracle Tips by Burleson

A trigger is a block of SPL that fires when an event occurs.  In Oracle, there are both database and table level triggers.  Currently, EnterpriseDB Advanced Server only supports table level triggers.

The events that fire a trigger in EnterpriseDB Advanced Server are: delete, insert and update.  A trigger may be one of those or any combination of those.  You can write triggers in any language supported by EnterpriseDB Advanced Server but I am going to concentrate on SPL.

There are three variables available to you when you code a trigger:  INSERTING, UPDATING and DELETING.  These are Boolean variables and indicate what event fired to trigger execution of the code.  If the trigger was fired due to an insert, INSERTING will be true while DELETING and UPDATING will be false.  These variables are used to logically group your code, which makes it easier to read and maintain triggers.

The syntax of an SPL trigger is:

CREATE OR REPLACE TRIGGER <trigger name>
  BEFORE | AFTER
  INSERT | UPDATE | DELETE [OR INSERT | UPDATE | DELETE [OR …]]
  ON <table name>
  [FOR EACH ROW]
  [DECLARE
     <variable declarations>]
  BEGIN
    <executable statements>
  [EXCEPTION
     <exception block>]
END;

A trigger may be a BEFORE or AFTER trigger.  A before trigger fires before the DML fires.  An after trigger fires afterwards.  Before triggers are usually written to take action that must happen before the DML occurs, such as calculate a value or do advanced validation.  An after trigger is usually written when you want to take action regardless of the data in the DML, such as auditing.

A trigger may also be a row level trigger or a statement level trigger.  By default, a trigger is a statement level trigger.  When you use the optional keywords FOR EACH ROW, the trigger becomes a row level trigger.

A statement level trigger fires once for the entire DML statement.  If you update 100 rows in a table, a statement level update trigger would fire once for the entire set.  If you need to execute code regardless of the data in the set, use a statement level trigger.

A row level trigger fires once for each row affected by a DML statement.  If you update the same 100 rows, a row level update trigger would fire 100 times.  Obviously, you would use a row level trigger when the data in the individual rows is important to you.

There is a performance benefit to using a statement level trigger instead of a row level trigger when you can.  In my experience, row level triggers are much more common than statement level triggers.  In most instances, the data is more important than the triggering event.

When you need to access the data in a row level trigger, you will need to know if you want to access the old data or the new data.  In an insert, all of the data is new and there is no old data.  In an update, you have new and old data.  In a delete, there is no new data; it is all old data.

In an update, the old data is fully populated and is copied to the new data.  Then any new elements are overwritten with the new values.  That means that any column that had data in the old record will contain data in the new record unless it was specifically set to null.  The point here is that data is not sparsely populated, all columns (both old and new) will have data if not null.

You can access old values by prepending an :old. to the column name and you can access new values by prepending :new. to the column name. 

Below are two triggers showing examples of before row and before statement triggers.  I will use the myemp table created in chapter 3.  If you don't have the myemp table you can create it by executing this (slightly altered) command:

CREATE TABLE myemp (
EMPNO         NUMBER(4)    NOT NULL PRIMARY KEY,
ENAME         VARCHAR2(10) NOT NULL DEFAULT 'BOB',
JOB           VARCHAR2(9)  CHECK (job in ('CLERK', 'MGR', 'GEEK', 'DWEEB')),
MGR           NUMBER(4),
HIREDATE      TIMESTAMP(0),
SAL           NUMBER(7,2),
COMM          NUMBER(7,2),
DEPTNO        NUMBER(2)
);

Before insert or update, row level trigger:

CREATE OR REPLACE TRIGGER myemp_bef_row_trg
  BEFORE
  INSERT OR UPDATE
  ON myemp
  FOR EACH ROW
  BEGIN
    IF INSERTING
    THEN
      DBMS_OUTPUT.PUT_LINE( 'Name is: ' || :new.ename );
    ELSIF UPDATING
    THEN
      DBMS_OUTPUT.PUT_LINE( 'Old Name is: ' || :old.ename ||
                            ', New Name is: ' || :new.ename );
    END IF;

END;
/

To test it, I run a sample script that inserts, updates and deletes:

BEGIN 

  INSERT INTO myemp (empno, ename)
    SELECT empno, ename
      FROM emp
      LIMIT 3; 

  UPDATE myemp
    SET ename = ename || '-x1'; 

  DELETE FROM myemp; 

END; 

This generates the output:

INFO:  Name is: SMITH
CONTEXT:  SQL statement "INSERT  INTO myemp ( empno , ename ) SELECT empno ,
ename FROM emp LIMIT 3"
INFO:  Name is: ALLEN
CONTEXT:  SQL statement "INSERT  INTO myemp ( empno , ename ) SELECT empno ,
ename FROM emp LIMIT 3"
INFO:  Name is: WARD
CONTEXT:  SQL statement "INSERT  INTO myemp ( empno , ename ) SELECT empno ,
ename FROM emp LIMIT 3"
INFO:  Old Name is: SMITH, New Name is: SMITH-x1
CONTEXT:  SQL statement "UPDATE  myemp SET ename = ename || '-x1'"
INFO:  Old Name is: ALLEN, New Name is: ALLEN-x1
CONTEXT:  SQL statement "UPDATE  myemp SET ename = ename || '-x1'"
INFO:  Old Name is: WARD, New Name is: WARD-x1
CONTEXT:  SQL statement "UPDATE  myemp SET ename = ename || '-x1'"

Notice that there are three INFO lines for the insert, three for the update and none for the delete.

Now I will drop the trigger (drop trigger myemp_bef_row_trg) and recreate it as a statement level trigger.

CREATE OR REPLACE TRIGGER myemp_bef_row_trg
  BEFORE
  INSERT OR UPDATE
  ON myemp
  --FOR EACH ROW
  BEGIN
    IF INSERTING
    THEN
--      DBMS_OUTPUT.PUT_LINE( 'Name is: ' || :new.ename );
      DBMS_OUTPUT.PUT_LINE( 'Inserting' );
    ELSIF UPDATING
    THEN
--      DBMS_OUTPUT.PUT_LINE( 'Old Name is: ' || :old.ename ||
--                            ', New Name is: ' || :new.ename );
      DBMS_OUTPUT.PUT_LINE( 'Updating' );
    END IF;
 

END;
/

Notice that to make this a statement level trigger, I commented out the keywords FOR EACH ROW.  Because this is a statement level trigger, I also had to remove references to :new and :old.  Those variable references are only available in row level triggers.

This is the output from my new trigger:

INFO:  Inserting
CONTEXT:  SQL statement "INSERT  INTO myemp ( empno , ename ) SELECT empno ,
ename FROM emp LIMIT 3"
INFO:  Updating
CONTEXT:  SQL statement "UPDATE  myemp SET ename = ename || '-x1'"

There is only one info row for the insert and one info row for the update.  There is no row for delete.

After statement and after row triggers are written the same way.  As a matter of fact, if you modify the above triggers and change the before keyword to an after, you will get the same output.

The important thing to remember about before and after, is that if you are generating data to populate a field, you must use a before row trigger.  You cannot access row level variables in a statement trigger and an after trigger is too late to change anything.  If you are auditing, you want to write an after trigger so that you get any calculated data fields.

        
     

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.