Triggers

Because the term trigger is not the least intuitive, your first impulse probably is to ask simply, "What?" Well, a trigger is a superb tool that lets us do something every time something else happens. Every time you add a new student, for example, you can have a trigger fire so that an e-mail is sent to Marketing with the student information for mailing lists. Or every time someone gets a perfect score on a test, a report can be created for the director or instructor.

Get the idea? In many businesses and universities these triggers are all-important. Human Resources always wants to know anytime someone changes name , address, and so forth. So you would put triggers on those columns in the database. Whenever any rows with the flagged data change, such as in an INSERT , UPDATE , or DELETE operation, certain other things will au-tomatically happen, such as an e-mail being sent to Human Resources, a report being printed, or emergency notification being sent to someone.

Triggers can act on rows or statements. Row triggers fire every time a row is changed in a table. Statement triggers fire once for each statement. For example, if you added 20 students to the STUDENTS table, one statement trigger would fire. But if you set up the trigger as a row trigger, you would get 20 trigger firings.

There are four triggers for rows and statements: INSERT , UPDATE , DELETE , and INSTEAD OF . And for all except INSTEAD OF , you can specify before or after. These options provide 14 possible triggers.

We create a trigger like this:

 CREATE OR REPLACE TRIGGER STUDENT_CHANGE AFTER INSERT OR UPDATE ON STUDENTS FOR EACH ROW BEGIN   IF INSERTING THEN        INSERT INTO AUDIT_TABLE VALUES (SAUDIT_SEQ.NEXTVAL, 'INSERT');   ELSE        INSERT INTO AUDIT_TABLE VALUES (SAUDIT_SEQ.NEXTVAL, 'UPDATE');   END IF; END; 

In this example we have told Oracle to create an entry in AUDIT_TABLE anytime the STUDENTS table is updated or a row is inserted. Notice how we slipped in another sequence!

Now let's take a look at how triggers are really used. If you want to monitor a table, you will want the before and after information, or at least the before information. Oracle gives us handy keywords ” NEW and OLD ”to use when we're writing to an audit table or something similar.

Let's create a simple audit table for authors:

 CREATE TABLE AUTHOR_AUDIT (   AUDIT_NO       NUMBER(9),   AUDIT_DATE     DATE,   AUDIT_ACTION   VARCHAR2(10) CHECK (AUDIT_ACTION IN('UPDATE',                     'INSERT')),   AUDIT_AUTHOR_ID NUMBER(9),   AUDIT_AUTHOR    VARCHAR2(30)               ); 

We've already created the sequence AUDIT_SEQ . So let's create a trigger to update the AUTHOR_AUDIT table when a record is inserted or changed:

 CREATE OR REPLACE TRIGGER AUTHOR_TRIGGER   AFTER INSERT OR UPDATE   ON AUTHORS   FOR EACH ROW   BEGIN    IF INSERTING THEN          INSERT INTO AUTHOR_AUDIT VALUES       (AUDIT_SEQ.NextVal, SYSDATE, 'INSERT',  :NEW.AUTHOR_ID, :NEW.AUTHOR);  <-- 2  ELSE         INSERT INTO AUTHOR_AUDIT VALUES       (AUDIT_SEQ.NextVal, SYSDATE, 'UPDATE',      :OLD.AUTHOR_ID, :OLD.AUTHOR);  <-- 2  END IF; END; 

(1) Notice in the next lines that we'll be using a sequence.

(2) Notice the colon before OLD and NEW.

In this example, we are writing to AUDIT_TABLE any new information on an INSERT , and the old information on an UPDATE .

Caution

Creating a trigger requires great attention to detail. If anything is misspelled or left out, for example, the only message you will get is, "Warning: Trigger created with compilation errors." This can be rather frustrating, and all I can tell you is to go back and check your spelling and your use of parentheses, semicolons, and so forth. One misspelled word or one missing semicolon will cause this error, and all you can do is take your code apart piece by piece until you find the culprit. Maybe someday Oracle will enhance its error messages. Obviously the compiler found a problem, but it just isn't designed yet to give us details.

What happens when we do an INSERT or UPDATE operation? Let's look at an example:

 INSERT INTO AUTHORS VALUES (    AUTHOR_SEQ.NEXTVAL, 'Harry Pottingham') 1 row created. 

AUDIT_NO	AUDIT_DAT	AUDIT_ACTI	AUDIT_AUTHOR_ID	AUDIT_AUTHOR
2	24-JAN-02	INSERT	333333336	Harry Pottingham

Here are the contents of the AUTHOR_AUDIT table:

Yes, you can have three triggers stacked in one script. Here's the code:

 /* TRIGGER CREATE SCRIPT    for three activities */ CREATE OR REPLACE TRIGGER AUTHOR_TRIGGER AFTER INSERT OR UPDATE OR DELETE     /* monitor inserts and updates  */ ON AUTHORS FOR EACH ROW BEGIN   IF INSERTING THEN      /* for an INSERT, get the NEW values */         INSERT INTO AUTHOR_AUDIT VALUES     (AUDIT_SEQ.NextVal, SYSDATE, 'INSERT',   :NEW.AUTHOR_ID, :NEW.AUTHOR); END IF;   IF UPDATING THEN      /*   for an UPDATE, then get the OLD values */       INSERT INTO AUTHOR_AUDIT  VALUES    (AUDIT_SEQ.NextVal, SYSDATE, 'UPDATE',  :OLD.AUTHOR_ID, :OLD.AUTHOR); END IF;   IF DELETING THEN      /* for a DELETE get the OLD values */         INSERT INTO AUTHOR_AUDIT VALUES      (AUDIT_SEQ.NextVal, SYSDATE, 'DELETE',    :OLD.AUTHOR_ID, :OLD.AUTHOR); END IF; END; 

You can add error processing using the EXCEPTION statement. There are three parts to handling exceptions: (1) building the DECLARE statements, (2) adding the IF... logic, and (3) creating the EXCEPTION section. Here's the same script again, modified just for inserts with exception routines:

 CREATE OR REPLACE TRIGGER INSERT_AUTHOR    BEFORE INSERT ON AUTHORS DECLARE  NO_AUTHORITY EXCEPTION; BEGIN   IF SUBSTR(USER,8,3) <> 'BUS'  <-- 2  THEN RAISE NO_AUTHORITY;   END IF;   IF SUBSTR(USER,8,3) <> 'bus'  <-- 2  THEN RAISE NO_AUTHORITY;   END IF; EXCEPTION  WHEN NO_AUTHORITY    THEN      RAISE_APPLICATION_ERROR (-20001, 'User NOT authorized'); END; 

(1) First the DECLARE section . . .

(2) then the logic to check the positions 9, 10, and 11 of the user name to see if any of them equal "BUS" or "bus" . . .

(3) and then at the end, the actual exception handling.

If all goes well, an unauthorized user will get the following message:

 *  ERROR at line 1:   ORA-20001: User NOT authorized   ORA-06512: at "SYSTEM.INSERT_AUTHOR", line 10   ORA-04088: error during execution of trigger   'SYSTEM.INSERT_AUTHOR'  

That's it for triggers! But let me say one more thing. Triggers can impede performance, and in busy shops they can quickly fill up audit tables. If you ever want to stop a trigger, just use the ALTER command (notice that you can take action against a specific trigger or against all triggers in a specific table):

• ALTER TRIGGER audit_trigger DISABLE;

• ALTER TABLE AUTHORS DISABLE ALL TRIGGERS;

To start a trigger, use the same command with ENABLE :

• ALTER TRIGGER audit_trigger ENABLE;

• ALTER TABLE AUTHORS ENABLE ALL TRIGGERS;

(By the way, the BEGIN...END coding you've done for trigger processing is PL/SQL code!)