PL/SQL Triggers

PL/SQL Triggers
A PL/SQL trigger is a construct in PL/SQL that runs or "triggered" on event of changes being made to a table in the database. The triggering event is a INSERT, UPDATE or DELETE done on a table. The trigger can be made so it can be "fired" either BEFORE or AFTER the Data Manipulation Language is executed.

> A database trigger is a block of code that is automatically executed in response to certain events.

> Triggers are executed implicitly whenever the triggering event happens.

> The triggering event is an INSERT, DELETE, or UPDATE command.

> The timing can be either BEFORE or AFTER, INSTEAD OF trigger.

The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the latter fires once for the whole statement.

You can write triggers that fire whenever one of the following operations occurs:

1. DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any user
   
2. DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database
   
3. Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the database

A trigger has three basic parts:

• A triggering event or statement
• A trigger restriction
• A trigger action

Example of PL/SQL trigger

In the below example line 2 is A triggering event or statement, lines 4-9 are A trigger action.

Example of creating a trigger based on the following two tables:

CREATE TABLE T1 (a INTEGER);
CREATE TABLE T2 (b INTEGER);

We will create a trigger that may insert a tuple into T2 when a tuple is inserted into T1. The trigger checks if the inserted row in T1 is has a value less than 5 only then a tuple is inserted in T2.

1 CREATE TRIGGER tr1
2 AFTER INSERT ON T1
3 REFERENCING NEW AS newRow
4 FOR EACH ROW
5 WHEN (newRow.a <= 5)
6 BEGIN
7 INSERT INTO T2
VALUES(:newRow.a);
8 END tr1;
9 .
10 run;



Different types of triggers can be:

Row Triggers and Statement Triggers: A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects. A row trigger fires once for each row affected by the triggering event.

BEFORE and AFTER Triggers: BEFORE triggers run the trigger action before the triggering statement is run. AFTER triggers run the trigger action after the triggering statement is run.

INSTEAD OF Triggers: INSTEAD OF triggers describe how to perform insert, update, and delete operations against views that are too complex to support these operations natively. INSTEAD OF triggers allow applications to use a view as the sole interface for all SQL operations (insert, delete, update and select).

Triggers on System Events and User Events: You can use triggers to publish information about database events to subscribers. System events are for example Database startup and shutdown, Data Guard role transitions etc and User Events are User logon and logoff, DDL statements (CREATE, ALTER, and DROP) etc.

Displaying Trigger Errors

If we get a message Warning: Trigger created with compilation errors. you can check the error messages with:

Show errors trigger <trigger_name>;

You can also type, SHO ERR (SHOW ERRORS) to see the most recent compilation error.

Viewing Defined Triggers

To view all the defined triggers, use:

select name_of_trigger from user_triggers;

For more details on a particular trigger:

select trigger_type, triggering_event, table_name, referencing_names, trigger_body
from user_triggers
where trigger_name = '<name_of_trigger>';

Disabling Triggers

To disable or enable a trigger:

alter trigger <name_of_trigger> {disable | enable};