Trigeer

1 Triggers
Triggers are simply stored procedures that are ran automatically by the database whenever
some event (usually a table update) happens. We won’t spend a great deal of time talking
about how to write triggers, because if you know how to write stored procedures, you already
know how to write triggers. What we will discuss is how to set them up to be executed
whenever somethinghappens to the table.
2 PL/SQL 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 variousstatements, 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
justonce). 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.
1
2.1 Permissions
Just like with procedures and functions, creating triggers requires certain privileges which
are not part of the defaultprivilege 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.
2.2 SampleTable 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, let’s not forget to setup: SETSERVEROUTPUT ON;
2.3 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
BEFOREINSERT
ON PERSON
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(’BEFORE INSERT OF ’ || :NEW.NAME);
END;
Now let us test it out:
2
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...