PL/SQL Triggers | Types of 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. In other words Triggers are a stored PL/SQL code block attached and executed by an event which occurs to a database table.

 Triggers are stored as text and compiled at execution time, because of wise not to include much code in them but to call out previously stored procedures or packages. Triggers contained SQL or PL/SQL statements which are executed as a unit and can call other procedures and triggers. 

Triggers are used to improve the performance of Oracle in order to provide a more convenient database. A trigger may not issue any transaction control statements like Commit, Rollback and Savepoint.

Unlike the stored procedure and functions, which have to be called explicitly, the database triggers are fires (executed) or called implicitly whenever the table is affected by any of the above said DML operations.

When does the Trigger Fire?

A trigger fires based on a triggering statement, which specifies:

• The SQL statement or the database event or DDL event that fires the trigger body. • The options include DELETE, INSERT, and UPDATE. One, two, or all three of these options can be included in the triggering statement specification.

• The table, view, DATABASE, or SCHEMA on which the trigger is defined.

Creating Trigger

To create a trigger, use the CREATE TRIGGER statement. By default, a trigger is created in enabled state. To create a trigger in disabled state, use the DISABLE clause of the CREATE TRIGGER statement. You must have the privilege .I.e. CREATE TRIGGER.

Syntax: 

CREATE [OR REPLACE] TRIGGER trigge_name 
{BEFORE | AFTER |INSTEAD OF} 
{INSERT [OR] |UPDATE [OR] |DELETE} 
[OF col_name] 
ON table_name 
[REFERENCING OLD AS old, NEW AS new] 
[FOR EACH ROW] 
WHEN (condition) 
BEGIN- Sql statements 
END; 
Where: 

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 the trigger should 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 old, NEW AS new] – 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.

Parts of a Trigger

A trigger has three basic parts:

A triggering event or statement

A trigger restriction

A trigger action

A triggering event can be an insert, update, or delete statement or an instance shutdown or startup etc. The trigger fires automatically when any of these events occur a trigger constraint specifies a Boolean expression that must be true for the trigger to fire. This condition is specified using the WHEN clause. The trigger action is a procedure that contains the code to be executed when the trigger fires.

The Triggering Event or Statement

A triggering event or statement is the SQL statement, database event, or user event that causes a trigger to fire. A triggering event can be one or more of the following:

• An INSERT, UPDATE, or DELETE statement on a specific table (or view, in some cases).

•  A CREATE, ALTER, or DROP statement on any schema object.

•  A database startup or instance shutdown.

•  A specific error message or any error message.

•  A user logon or logoff.

Syntax:
 
[INSERT | DELETE | UPDATE of column-list] 
ON <table-name> 
[REFERENCING <OLD as old, NEW as new>] 
 A triggering event can specify multiple SQL statements: 
[... INSERT OR UPDATE OR DELETE OF inventory ... ] 

This part means that when an INSERT, UPDATE, or DELETE statement is issued against the inventory table, fire the trigger. When multiple types of SQL statements can fire a trigger, you can use conditional predicates to detect the type of triggering statement.

Trigger Restriction

A trigger restriction specifies a Boolean expression that must be true for the trigger to fire. The trigger action is not run if the trigger restriction evaluates to false or unknown in the example, the trigger restriction is:

new.parts_on_hand < new. reorder_point 

Consequently, the trigger does not fire unless the number of available parts is less than a present reorder amount.

Trigger Action

The trigger action is a procedure that contains the code to be executed when the trigger fires. Oracle will execute the block when a triggering statement is issued and the trigger restriction evaluates to true.

[DECLARE] 
BEGIN 
[EXCEPTION SECTION] 
END [trigger-name] 

Types of PL/SQL Triggers

A DML trigger is fired by a DML statement, a DDL trigger is fired by a DDL statement, a DELETE trigger is fired by a DELETE statement, and so on. Different types of triggers are as under:

Row Triggers and Statement Triggers

• BEFORE and AFTER Triggers

• INSTEAD OF Triggers

• Triggers on System Events and User Events

Row Triggers and Statement Triggers

When you define a trigger, you can specify the number of times the trigger action is to be run:

Once for every row affected by the triggering statement, such as a trigger fired by an UPDATE statement that updates many rows.

• Once for the triggering statement, no matter how many rows it affects.

Row Triggers

A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.

A Row trigger fires once for each row affected. It uses FOR EACH ROW clause. They are useful if trigger action depends on number of rows affected. Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected.

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, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, statement-level DELETE trigger is fired only once.

In other words Statement Trigger fires once, irrespective of number of rows affected in the table. Statement triggers are useful when triggers action does not depend on Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected.

For example, use a statement trigger to:

• Make a complex security check on the current time or user.

• Generate a single audit record.

Before and after Triggers

‘When defining a trigger, you can specify the trigger timing-whether the trigger action is to be run before or after the triggering statement. BEFORE and AFTER apply to both statement and row triggers.

BEFORE and AFTER triggers fired by DML statements can be defined only on tables, not on views. However, triggers on the base tables of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against the view. BEFORE and AFTER triggers fired by DDL statements can be defined only on the database or a schema, not on particular tables.

Before Triggers

BEFORE triggers run the trigger action before the triggering statement is run. This type of trigger is commonly used in the following situations:

When the trigger action determines whether the triggering statement should be allowed to complete. Using a BEFORE trigger for this purpose, you can eliminate unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised in the trigger action.

To derive specific column values before completing a triggering INSERT or UPDATE statement.

After Triggers

AFTER triggers run the trigger action after the triggering statement is run. AFTER TRIGGERS are used when you want the triggering statement to complete before executing the trigger action.

Instead of Triggers

INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.

You can write normal INSERT, UPDATE, and DELETE statements against the view, and the INSTEAD OF trigger is fired to update the underlying tables appropriately INSTEAD OF triggers are activated for each row of the view that gets modified.