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:
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.
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:
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.
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:
After Insert Trigger
Can you guess what the trigger would look like that would fire AFTER the insert? Well?
And with our 2nd test INSERT:
INSERT INTO PERSON(ID,NAME,DOB) VALUES (2,'JANE DOE',SYSDATE);
For a total result of:
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).
Now, let's run an update...
UPDATE PERSON SET DOB = SYSDATE;
Which produces the result:
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:
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:
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...