PL/SQL Triggers Overview with Examples

---

Triggers:
======
A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

Trigger is a database object or a sub program which is used to protect the data from invalid operations which is performed by end user. Triggers are implicitly invoking without user interaction.

Triggers are classified into two types

• Row level or statement level
• Database triggers

Row level Triggers:
============

1. These triggers will stop the invalid DML operations performed by the end user.
2. These triggers will fire depending on the time, depending on the day or depending on some condition.
3. These triggers will fire each row in the table.
4. These triggers are generally written by database developers.

Database Triggers:
============

1. These triggers are fired by the user log on to the database and by the user log off from the database.
2. These triggers are written by DBA(Database administrator)

Syntax for Creating a Trigger :

CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
-- List of sql statements
END;

Description of above syntax :

CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with the given name or overwrites an existing trigger with the same name.

{BEFORE | AFTER | INSTEAD OF } - This clause indicates at what time should the trigger get fired. i.e for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view.

{INSERT [OR] | UPDATE [OR] | DELETE} - This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.

[OF col_name] - This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.

[ON table_name] - This clause identifies the name of the table or view to which the trigger is associated.

[REFERENCING OLD AS o NEW AS n] - This clause is used to reference the old and new values of the data being changed. By default, you reference the values as :old.column_name or :new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.

[FOR EACH ROW] - This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e.statement level Trigger).

WHEN (condition) - This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.

NOTE : If you want to get more idea on creating and using of triggers refer the following examples.

Example-1 : Write a trigger to execute or to fired when the user trying to delete any record from the existing table(employee).

SQL>Create or replace trigger techsnibtrig1
Before delete on employee
Begin
raise_application_error(-20000,’you are not supported to delete the record from the table’);
End;
/
Trigger created

NOTE : in the above example raise_application_error is a predefined function having two parameters, one is called error number and other one is error message. Error number is always in the range between -20000 to -20999

Example-2 : Write a trigger to stop all the DML operations on employee table.

SQL>Create or replace trigger techsnibtrig2
Before delete or update or insert on employee
Begin
raise_application_error(-20000,we can’t perform any DML operations on this table’);
End;
/
Trigger created

• Syntax to see list of triggers

    SQL> select * from user_triggers;

• Syntax to see the source code of a trigger on a particular table

    SQL>select trigger_name,table_name,status,description
                          from user_triggers where table_name=’EMPLOYEE’;

• Syntax to alter a trigger status

    SQL> alter trigger techsnibtrig1 disable/enable;

• Syntax to drop a trigger

    SQL> drop trigger <trigger_name>;

Example-3: Write a trigger to fire to stop the transaction based on time(day).

SQL>Create or replace trigger techsnibtrig3
Before delete or update or insert on dept
Declare
Cday char(15);
Begin
Cday:=to_char(sysdate,’day’);
raise_application_error(-20000,'we can’t perform any DML operations on this table’);
End;
/
Trigger created

Example-4: Write a trigger to stop the transactions between 11 A.M to 1 P.M.

SQL>Create or replace trigger techsnibtrig4
Before delete or update or insert on parent
Declare
T number;
Begin
T:= to_char(sysdate,’hh24’);
if T between 11 and 13 then
Raise_application_error(-20003,’time completed’);
End if;
End;
/
Trigger created

Example-5: Write a trigger to log the logon details

     Consider Administrator is logon to the database and create mylogon table and by using trigger
he/she is trying to insert the logon details of users who are logged on to the database.

SQL> connect system/orcl -- Oracle DBA(DataBase Administrator)
Connected

SQL> create table mylogon(who varchar2(20),when timestamp);
Table created

SQL>Create or replace trigger logon
After logon on database
Begin
Insert into mylogon values(user,sysdate);
End;
/
Trigger created

SQL> exit; -- Disconnecting from Administrator

SQL> connect techsnib/techsnib -- Connecting to techsnib user
Connected

SQL> connect system/orcl -- Again connecting to Administrator
Connected

SQL> select * from mylogon; -- checking Logon details of users who are logged on to database.

WHO WHEN
---------------------------------------------------------------------------------------
TECHSNIB 04-APR-13 07:34:23 00 00 00 00
SYSTEM 04-APR-13 07:36:23 00 00 00 00

Example-6: Write a trigger to log the logoff details

       Consider Administrator is logon to the database and create mylogff table and by using trigger
he/she is trying to insert the logff details of users who are logged off from the database.

SQL> connect system/orcl -- Oracle DBA(DataBase Administrator)
Connected
SQL> show user User is ‘SYSTEM’
SQL> create table mylogoff(who varchar2(20),when timestamp);
Table created

SQL>Create or replace trigger logoff
before logoff on database
Begin
Insert into mylogoff values(user,sysdate);
End;
/
Trigger created

SQL> exit; -- Disconnecting from Administrator

SQL> connect techsnib/techsnib -- Connecting to techsnib user
Connected
SQL>connect system/orcl -- Again connecting to Administrator
Connected

SQL> select * from mylogoff; -- checking Logoff details of users who are logged off from database.

WHO WHEN
---------------------------------------------------------------------------------------
TECHSNIB 04-APR-13 07:45:25 00 00 00 00
SYSTEM 04-APR-13 07:47:34 00 00 00 00

---