Notes 0014

Triggers (continued)

Special IF statements

Inside the PL/SQL block of a trigger we can use if statements to determine what statement caused the firing of the trigger. These are generally of the form: IF inserting THEN... where besides "inserting" you can also use updating and deleting. An example would be something like:

CREATE OR REPLACE
TRIGGER PERSON_BIUD
BEFORE INSERT OR UPDATE OR DELETE ON PERSON
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        DBMS_OUTPUT.PUT_LINE('INSERTING PERSON: ' || :NEW.NAME);
    ELSIF UPDATING THEN
        DBMS_OUTPUT.PUT_LINE('UPDATING PERSON: ' ||
            :OLD.NAME || ' TO ' || :NEW.NAME);
    ELSIF DELETING THEN
        DBMS_OUTPUT.PUT_LINE('DELETING PERSON: ' || :OLD.NAME);
    END IF;
END;

Notice that we only have one TRIGGER, and we are using IF statements to determine what statement invoked it, and display an appropriate message in various cases.

For example, when we do an insert:

INSERT INTO PERSON(ID,NAME,DOB) VALUES (3,'SUPERMAN',TO_DATE('09/05/1950','MM/DD/YYYY'));

Then we get output like:

INSERTING PERSON: SUPERMAN

If we go ahead and modify that person:

UPDATE PERSON SET NAME = 'BATMAN' WHERE NAME = 'SUPERMAN';

Then we get an output like:

UPDATING PERSON: SUPERMAN TO BATMAN

And finally, if we go ahead and delete that person:

DELETE PERSON WHERE NAME = 'BATMAN';

Then we would get output like:

DELETING PERSON: BATMAN

Please note that you will have to run SET SERVEROUTPUT ON; in SQL*Plus order to see the output.

Working with Views

For our next example, we will need to create a view (of PERSON table):

CREATE OR REPLACE
VIEW PERSON_VIEW AS
SELECT NAME FROM PERSON;

Now, we know that updating (or inserting) into a view is kind of pointless; however, we can provide this functionality using a trigger! For example:

CREATE OR REPLACE
TRIGGER PERSON_VIEW_INSERT
INSTEAD OF INSERT ON PERSON_VIEW
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('INSERTING: ' || :NEW.NAME);

    -- we can also do
    -- INSERT INTO PERSON(ID,NAME,DOB) VALUES (N,:NEW.NAME,SYSDATE);
END;

When we do an insert statement on PERSON_VIEW:

INSERT INTO PERSON_VIEW(NAME) VALUES ('SUPERMAN');

Which produces the result:

INSERTING: SUPERMAN

So, what did just happen??? Did we insert a value into a view? No, not really. What we did was fire a trigger when someone tried to insert a value into a VIEW.

Now, as the comment in the code indicates, we can actually simulate the insertion statement (by inserting the value into the PERSON table ourselves).

Trigger Exceptions (introduction)

Triggers become part of the transaction of a statement, which implies that it causes (or raises) any exceptions (which we'll talk about later), the whole statement is rolled back.

Think of an exception as a flag that is raised when an error occurs.

Sometimes, an error (or exception) is raised for a good reason. For example, to prevent some action that improperly modifies the database. Let's say that our database should not allow anyone to modify their DOB (after the person is in the database, their DOB is assumed to be static).

Anyway, we can create a trigger that would prevent us from updating the DOB:

CREATE OR REPLACE
TRIGGER PERSON_DOB
BEFORE UPDATE OF DOB ON PERSON
FOR EACH ROW
BEGIN
    RAISE_APPLICATION_ERROR(-20000,'CANNOT CHANGE DATE OF BIRTH');
END;

Notice the format of the trigger declaration. We explicitly specify that it will be called BEFORE UPDATE OF DOB ON PERSON.

The next thing you should notice is the procedure call RAISE_APPLICATION_ERROR, which accepts an error code, and an explanation string. This effectively halts our trigger execution, and raises an error, preventing our DOB from being modified. An error (exception) in a trigger stops the code from updating the DOB.

When we do the actual update for example:

UPDATE PERSON SET DOB = SYSDATE;

We end up with an error, that says we CANNOT CHANGE DATE OF BIRTH.

SQL> UPDATE PERSON SET DOB = SYSDATE;
UPDATE PERSON SET DOB = SYSDATE
       *
ERROR at line 1:
ORA-20000: CANNOT CHANGE DATE OF BIRTH
ORA-06512: at "PARTICLE.PERSON_DOB", line 2
ORA-04088: error during execution of trigger 'PARTICLE.PERSON_DOB'

You should also notice the error code of ORA-20000. This is our -20000 parameter to RAISE_APPLICATION_ERROR.

We will discuss Exceptions in general in more detail later (including how you can catch and handle them when they do occur); but for the time being, you have the ability to prevent some modification operation using an exception raised from a trigger.

Viewing Triggers

You can see all your user defined triggers by doing a select statement on USER_TRIGGERS. For example:

SELECT TRIGGER_NAME FROM USER_TRIGGERS;

Which produces the names of all triggers. You can also select more columns to get more detailed trigger information. You can do that at your own leisure, and explore it on your own.

Dropping Triggers

You can DROP triggers just like anything. The general format would be something like:

DROP TRIGGER trigger_name;

Altering Triggers

If a trigger seems to be getting in the way, and you don't want to drop it, just disable it for a little while, you can alter it to disable it. Note that this is not the same as dropping a trigger; after you drop a trigger, it is gone.

The general format of an alter would be something like this:

ALTER TRIGGER trigger_name [ENABLE|DISABLE];

For example, let's say that with all our troubles, we still need to modify the DOB of 'JOHN DOE'. We cannot do this since we have a trigger on that table that prevents just that! So, we can disable it...

ALTER TRIGGER PERSON_DOB DISABLE;

Now, we can go ahead and modify the DOB :-)

UPDATE PERSON SET DOB = SYSDATE WHERE NAME = 'JOHN DOE';

We can then re-ENABLE the trigger.

ALTER TRIGGER PERSON_DOB ENABLE;

If we then try to do the same type of modification, the trigger kicks and prevents us from modifying the DOB.

That is it.

© 2006, Particle