EnterpriseDB: Triggers Oracle Tips by Burleson Consulting

Triggers are a special kind of stored procedure.  A trigger is defined for a specific table and fires when an event occurs.  Events are defined as INSERT, UPDATE or DELETE.   The author of the trigger can define which of the three events will fire the trigger.

Triggers are a good way to populate complex default values, default values that are dependant on multiple columns or forcing values (like forcing a field to upper case or encrypting a column).  If you want to perform an action when an event occurs, you could use a trigger for that.  For example, if you would like to insert into an audit table whenever a record is deleted from a particular table, you would use a delete trigger.

A trigger can be defined as a ROW level trigger or as a STATEMENT level trigger.  A ROW level trigger executes for each row involved in the statement.  For example, if you run an update that modifies 5 rows, the ROW level trigger will fire 5 times.  A statement level trigger fires once for each statement.  In our example update of 5 rows, the STATEMENT level trigger would only fire once.

A trigger can also be either a BEFORE or AFTER event trigger.  A BEFORE INSERT trigger would fire before the insert actually happens.  An AFTER INSERT would fire after the inserted data is in the table.

A BEFORE ROW level trigger can modify the incoming data of an insert or an update.  STATEMENT level and AFTER triggers cannot modify the data.

A BEFORE ROW trigger has access to both the new values of the row and old values of the row.  It can access the columns by prepending a :NEW or :OLD designator in front of the column.  For example, a BEFORE ROW trigger could compare :OLD.emp_no = :NEW.emp_no.  On an INSERT, :OLD columns are NULL.  On a DELETE, :NEW columns are NULL.  On an UPDATE, :OLD and :NEW may have values.

An AFTER ROW trigger has access to the :OLD values.

Statement level triggers do not have access to any of the row data.

To detect what event fired the trigger, you can call the functions INSERTING, UPDATING and DELETING.  These functions return TRUE or FALSE.

I will discuss triggers quite a bit more in the next chapter and in the chapter on migrating applications.  Below I show some simple examples for syntax purposes.  Triggers can be written in multiple languages.  The examples below are for SPL.  I will show alternatives in future chapters.

Unlike Oracle triggers, an EnterpriseDB trigger uses the table name as the object name space.  That means that in Oracle, a schema can have only one trigger named "trig1" but in EnterpriseDB you may have a trigger named "trig1" on each table.

CREATE TRIGGER

CREATE  OR REPLACE  TRIGGER name
            { BEFORE | AFTER }
            { INSERT OR UPDATE OR DELETE }
            ON <table name>
            FOR EACH ROW
            DECLARE
            <variable declarations>
            BEGIN
            <executable code>
            END;

We can create a BEFORE INSERT ROW level trigger on the emp table and force the HIREDATE field to be equal to SYSDATE if it is NULL.

CREATE OR REPLACE TRIGGER emp_trig
  BEFORE INSERT ON emp
  FOR EACH ROW
BEGIN
  IF :new.hiredate IS NULL
  THEN
    :new.hiredate := SYSDATE;
  END IF;
END;
/

We can create a BEFORE INSERT, UPDATE, DELETE ROW trigger to select the empno from a sequence if it is null (on insert), increase the comm field on an update to 10% if the comm field is being increased and it is less than 10% and to insert into the emp clone table on delete.

CREATE OR REPLACE TRIGGER emp_trig
  BEFORE INSERT OR UPDATE OR DELETE
  ON emp
  FOR EACH ROW
BEGIN
  IF INSERTING
  THEN
     IF :NEW.empno IS NULL
     THEN
        SELECT emp_seq.nextval
            INTO :NEW.empno
            FROM DUAL;
     END IF;
  ELSEIF UPDATING
  THEN
     IF :NEW.comm > :OLD.comm  -- Being increased
       AND
         :NEW.comm < :OLD.comm * (1.1);  -- Increase less than 10%
     THEN
        :NEW.comm := :OLD.comm * (1.1);  -- Make it 10%
    END IF;
  ELSEIF DELETING
  THEN
    INSERT IN emp (empno, ename, deptno)
      VALUES (:OLD.empno, :OLD.ename, :OLD.deptno);
   END IF;
END;
/

ALTER TRIGGER

An alter can only rename a trigger:

ALTER TRIGGER emp_trigger ON emp RENAME TO emp_trig_raise;

GRANT TRIGGER

You cannot grant to a trigger.  You would grant to the table that has the trigger.

REVOKE TRIGGER

You cannot revoke permissions from a trigger.  You would revoke permissions from a table that has the trigger.

DROP TRIGGER

You can drop a trigger:

DROP TRIGGER emp_trig_raise ON emp;

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