Database Triggers

Database triggers – Learn when and how trigger works on database tables. Also Learn their classification and usage in different scenarios with examples

Database Triggers

Triggers are stored programs containing set of PL-SQL statements , which are automatically executed when ever an DML statement performed on the table.

It is a PL-SQL blok like a procedure , to perform some specific task. But a procedure always requires an explicit call for execution , but triggers are executed automatically when any triggering event occurs.

It is associated with a Table or View.

INSERT, UPDATE, DELETE are considered as triggering events of the database trigger. These events initiate the firing of trigger.

The firing of trigger is nothing but the execution of the PL-SQL code associated to that trigger.It is also a Database object.

Notes

Triggers are, in fact, written to be executed in response to any of the following events –

• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
• A database definition (DDL) statement (CREATE, ALTER, or DROP).
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers can be defined on the table, view, schema, or database with which the event is associated.

Advantages

• A database trigger is a security object to provide security to the table like tracking the transaction.
• A database trigger is also used to define the complex business constraints that cannot be defined using integrity constraints.
• Automatically generating values for derived columns or PRIMARY KEY columns.
• Used to implement user defined restrictions on table.
• Provides high security.
• Activated when table are manipulated from other application software also.

Syntax for Creating Triggers

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)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END;

NOTES

Trigger cannot be duplicate trigger name.
Trigger can be attached one table.
When condition is true, Trigger will be executed otherwise not executed.

CREATE [OR REPLACE] TRIGGER trigger_name – Creates or replaces an existing trigger with the trigger_name.
{BEFORE | AFTER | INSTEAD OF} – This specifies when the trigger will be executed. The INSTEAD OF clause is used for creating trigger on a view.
{INSERT [OR] | UPDATE [OR] | DELETE} – This specifies the DML operation.
[OF col_name] – This specifies the column name that will be updated.
[ON table_name] – This specifies the name of the table associated with the trigger.
[REFERENCING OLD AS o NEW AS n] – This allows you to refer new and old values for various DML statements, such as INSERT, UPDATE, and DELETE.
[FOR EACH ROW] – This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger.

WHEN (condition) – This provides a condition for rows for which the trigger would fire. This clause is valid only for row-level triggers.

NEW and OLD- refers to the available in DML statements. Valid in row trigger only.

	NEW	OLD
INSERT	Y	N
UPDATE	Y	Y
DELETE	N	Y

The following points need to be considered here –

• OLD and NEW references are not available for table-level triggers, rather you can use them for record-level triggers.
• If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.
• The above trigger has been written in such a way that it will fire before any DELETE or INSERT or UPDATE operation on the table, but you can write your trigger on a single or multiple operations, for example BEFORE DELETE, which will fire whenever a record will be deleted using the DELETE operation on the table.

Application Trigger

It is fired When DML Event occurs within the Application.

Example : Oracle Forms.

Database Trigger

It is fired when DML Event occurs on a Table/ View no matter which user is connected.

Components of Trigger

Trigger Parts

Indicates when to activate the trigger. i.e defines whether the trigger first before or after the statement is executed.

Before Triggers

These TRIGGERS fire BEFORE any transactions are implemented.

These TRIGGERS can be classified as

BEFORE INSERT

BEFORE UPDATE

BEFORE DELETE

Usage
Trigger provide values for derived columns BEFORE the INSERT OR UPDATE statement was completed.

Trigger determines whether an INSERT, UPDATE or DELETE statement should be allowed to completed.

After Triggers

These TRIGGERS fire AFTER ,any transaction  implemented.

These TRIGGERS can be classified as

AFTER INSERT

AFTER UPDATE

AFTER DELETE

Usage

A TRIGGER should fire for acknowledgement purpose or auditing, after a DML statement  executed.

When a TRIGGER should perform action not specified in a BEFORE trigger.

Restrictions

A trigger may not issue a transactional control statement like COMMIT, SAVEPONT and ROLLBACK.

Any FUNCTION or PROCEDURE called by a trigger cannot issues a transaction control statement.

Level of Triggers

Trigger can be define at two different Levels

They are

1. ROW LEVEL
2. Statement or table level

Row level Trigger

A row trigger will fire as many times as there are rows affecting by triggering event.
When the statement for FOR EACH ROW is present in the CREATE TRIGGER clause, the trigger is a ROW trigger.

Statement Level

Trigger will fire only once for DML statement.

Trigger Body

A set of PL-SQL statements.

The WHEN Clause

The WHEN clause is valid for row-level triggers only.
The trigger body will execute only for those rows that meet the condition

Syntax

WHEN trigger_condition

The :new and :old records can  referenced inside trigger_condition as well .

The colon is only valid in the trigger body.

Triggers activate when tables manipulated from other application software also.