3 Simple and Interesting PLSQL Programs to Explain Triggers

Triggers in PL/SQL are the blocks which are automatically fired before or after any alteration (insert, update, delete) is done to the table. Here are 3 simple programs which will illustrate the concept of triggers very efficiently. Have a look...

Consider the following table named student. It contains roll no, name and marks of each student.

roll          name     marks
20034   SID        69
20035   HARRY 88
20036   TANK    34

1. Program to illustrate the use of BEFORE TRIGGER. This trigger will always enter the name of student in capital letters.

CREATE OR REPLACE TRIGGER capital_name BEFORE INSERT OR UPDATE ON student FOR EACH ROW
BEGIN
:NEW.name := UPPER(:NEW.name);
END;

Now if you make the following query to student table:

UPDATE student SET name = ‘Steven’ WHERE roll = 20035;

Then instead of ‘Steven’, ‘STEVEN’ is inserted into the table.

2. Program to illustrate the use of BEFORE TRIGGER. This trigger will not allow to do any action with student table on a specified day, here saturday.

CREATE OR REPLACE TRIGGER no_action BEFORE INSERT OR UPDATE OR DELETE ON student FOR EACH ROW
BEGIN
IF(LTRIM(RTRIM(TO_CHAR(SYSDATE,’DAY’)))=’SATURDAY’)
THEN
RAISE_APPLICATION_ERROR(-20998,’Action Denied’);
END IF;
END;

Now if you make the following query to student table:

UPDATE student SET name = ‘Steven’ WHERE roll = 20035;

on saturday. You will get the message ‘Action Denied’. This is very useful feature used to avoid any alteration in sensitive data on weekends when you are not around and anybody else tries to alter it.

3. Program to illustrate the use of AFTER TRIGGER. This trigger will put the altered enteries in a new table named track.

Suppose student table is very critical and crucial. Only you are allowed to alter the enteries. So you can make trigger to track other persons who login with their username and try to alter the table. For this you have to create a table track as follows:

CREATE TABLE track (roll number, name varchar2(40), oldmarks number, newmarks number, uname varchar2(40));

Now create a Trigger as

CREATE OR REPLACE TRIGGER track_action AFTER UPDATE ON student FOR EACH ROW
BEGIN
INSERT INTO track VALUES(:OLD.roll, :OLD.name, :OLD.marks, :NEW.marks, USER);
END;

Now suppose a relative of TANK works in your department and he comes to increase his marks for 34 to 94. He will login from his username and will fire the following query.

UPDATE student SET marks = 94 WHERE roll = 20036;

He will now get delighted that he has altered the table. But he doesn’t know that a secret table named track has strored all the alterations done by him.

Now when you come in morning and fire the following query:

SELECT * FROM track;

Now, there will be one record in this table containing roll as 20036, name as TANK, old marks as 34 and new marks as 94 and most importantly the username of sneaker. So he has been trapped.