Notes 0013

Triggers

Triggers are basically PL/SQL procedures that are associated with tables, and are called whenever a certain modification (event) occurs. The modification statements may include INSERT, UPDATE, and DELETE.

The general structure of triggers is:

CREATE [OR REPLACE]
TRIGGER trigger_name
BEFORE (or AFTER)
INSERT OR UPDATE [OF COLUMNS] OR DELETE
ON tablename
[FOR EACH ROW [WHEN (condition)]]
BEGIN
...
END;

The usual CREATE OR REPLACE we have already seen with procedures and functions... TRIGGER specifies just what type of object we are creating.

The BEFORE (or AFTER) in the trigger definition refers to when you want to run the trigger, either before the actual database modification (update, delete, insert) or after.

The list of various statements, INSERT OR UPDATE [OF COLUMNS] OR DELETE refers to statements that trigger this trigger. You can specify all three, or just one. Let's say you wanted the trigger to fire only when you do a delete; well, then you'd only specify a DELETE in the list.

On some table specifies that the trigger is associated with such table. As we shall see later, this does not necessarily has to be a table, but could also be a view.

There are several types of triggers; ones for each row and others per statement. For example, when you're doing an update, you can have a trigger fire once for each thing being updated (if you update 20 rows, the thing would fire 20 times), or you can have it fire just once per statement (if a single update statement is updating 20 rows, the trigger would fire just once). This is what that FOR EACH ROW in the trigger definition means.

The PL/SQL block (between BEGIN and END) is a usual code block where you can place PL/SQL commands. The only limitation is that you cannot use COMMIT (or ROLLBACK) for obvious reasons.

Permissions

Just like with procedures and functions, creating triggers requires certain privileges which are not part of the default privilege set. If you cannot create triggers from these notes because of permissions, you (or the admin) has to GRANT CREATE TRIGGER privilege on your username.

For example, to allow user 'alex' to create triggers, I may do something like this:

GRANT CREATE TRIGGER TO alex;

Note that if you are accessing a public Oracle server you must ask the admin to setup these things for you (in case of our class, you ask the instructor; in other words: me).

Sample Table to be Triggered

Before we begin playing with triggers, let's create a simple table with which we can experiment:

CREATE TABLE PERSON (
    ID   INT,
    NAME VARCHAR(30),
    DOB  DATE,
    PRIMARY KEY(ID)
);

The above creates a PERSON table with an ID, a NAME and a DOB columns (fields). Whatever triggers we define in these notes will relate to this table.

Also, lets not forget to setup: SET SERVEROUTPUT ON;

Before Insert Trigger

Let's start out our quest to learn triggers with the simplest case. We have nothing in the database (our PERSON table is empty). Before we insert any data, we'd like to perform some operation (let's say for logging purposes, or whatever). We write a trigger to fire before the insert takes place.

CREATE OR REPLACE
TRIGGER PERSON_INSERT_BEFORE
BEFORE
INSERT
ON PERSON
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('BEFORE INSERT OF ' || :NEW.NAME);
END;

Now let us test it out:

INSERT INTO PERSON(ID,NAME,DOB) VALUES (1,'JOHN DOE',SYSDATE);

The single INSERT statement fires the trigger. When we run it, we get the print out of 'BEFORE INSERT OF JOHN DOE'. Ie:

SQL> INSERT INTO PERSON(ID,NAME,DOB) VALUES (1,'JOHN DOE',SYSDATE);
BEFORE INSERT OF JOHN DOE

1 row created.

After Insert Trigger

Can you guess what the trigger would look like that would fire AFTER the insert? Well?

CREATE OR REPLACE
TRIGGER PERSON_INSERT_AFTER
AFTER
INSERT
ON PERSON
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('AFTER INSERT OF ' || :NEW.NAME);
END;

And with our 2nd test INSERT:

INSERT INTO PERSON(ID,NAME,DOB) VALUES (2,'JANE DOE',SYSDATE);

For a total result of:

SQL> INSERT INTO PERSON(ID,NAME,DOB) VALUES (2,'JANE DOE',SYSDATE);
BEFORE INSERT OF JANE DOE
AFTER INSERT OF JANE DOE

1 row created.

Notice that both triggers have fired. One before the INSERT the other one after.

Before Update Statement Trigger

Now that we have some data in the table, we can create an update trigger, that would fire whenever someone tries to update any person (or persons).

CREATE OR REPLACE
TRIGGER PERSON_UPDATE_S_BEFORE
BEFORE UPDATE
ON PERSON
BEGIN
    DBMS_OUTPUT.PUT_LINE('BEFORE UPDATING SOME PERSON(S)');
END;

Now, let's run an update...

UPDATE PERSON SET DOB = SYSDATE;

Which produces the result:

SQL> UPDATE PERSON SET DOB = SYSDATE;
BEFORE UPDATING SOME PERSON(S)

2 rows updated.

Note that is says 2 rows updated but we've only seen one BEFORE UPDATING SOME PERSON(S), meaning that our trigger only fired once. This is because we did not specify FOR EACH ROW (which we'll do next).

Btw, from now on, we'll leave out a few details (I'll assume you can figure out how to write PERSON_UPDATE_S_BEFORE trigger, and such, etc.)

FOR EACH ROW Before Update Trigger

Right now, all we are doing is adding a FOR EACH ROW to last example:

CREATE OR REPLACE
TRIGGER PERSON_UPDATE_BEFORE
BEFORE UPDATE
ON PERSON
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('BEFORE UPDATING ' ||
       TO_CHAR(:OLD.DOB,'HH:MI:SS') || ' TO ' ||
       TO_CHAR(:NEW.DOB,'HH:MI:SS'));
END;

We're also printing out (displaying) the old value of PERSON.DOB and the new value. Now, let's run our update statement:

UPDATE PERSON SET DOB = SYSDATE;

Which gives the results:

 SQL> UPDATE PERSON SET DOB = SYSDATE;
BEFORE UPDATING SOME PERSON(S)
BEFORE UPDATING 10:54:06 TO 11:10:01
BEFORE UPDATING 10:54:06 TO 11:10:01

2 rows updated.

Notice that we still get the firing of the initial 'non' per row trigger (that's the first one), then the FOR EACH ROW trigger fires, which actually does run for each row that is updated (in this case, twice).

We won't go into the AFTER update trigger; but you should still know it and practice it.

We shall discuss triggers in more detail next time...

© 2006, Particle