PL/SQL - TRIGGERS

TRIGGERS

A database trigger is a stored procedure that is fired when an INSERT, UPDATE, or DELETE statements is issued against the associate table. A trigger defines an action the database should take when some database related event occurs. A trigger can include SQL and PL/SQL statements to executor as a unit and can invoke other stored procedures.

USES OF TRIGGERS

1) Prevent invalid transactions

2) Enforce complex security authorizations

3) Enforce complex business rules

4) Provide auditing

5) Maintain synchronous table replicates

6) Modify table data when DML statements are issued against views

7) Publish information about database events, user events, and SQL statements to subscribing applications

PARTS OF A TRIGGER

A database trigger has three parts:

1) Triggering event or Statement.

2) Trigger constraint (Optional)

3) Trigger action

1) Triggering Event or Statement:

A triggering event or statement is the SQL statement that causes a trigger to be fired. Atriggering event can be an INSERT, UPDATE, or DELETE statement for a specific table.

2) Trigger Constraint or Restriction

A trigger restriction specifies a Boolean (logical) expression that must be TRUE for thetrigger to fire. The trigger action is not executed if the trigger restriction evaluates to FALSE. Atrigger restriction is an option available for triggers that are fired for each row. Its functionis to conditionally control the execution of a trigger. A trigger restriction is specified using aWHEN clause. It is an optional part of trigger.

3) Trigger Action

A trigger action is the procedure (PL/SQL block) that contains the SQL statements andPL/SQL code to be executed when a triggering statement is issued and the trigger restriction evaluates to TRUE.

Types of Triggers

A trigger’s type is defined by the type of triggering transaction and by the level atwhich the trigger is executed. Oracle has the following types of triggers depending on thedifferent applications:

1)Row Level Triggers

2)Statement Level Triggers

3)Before Triggers

4) After Triggers

Row Level Triggers

Row level triggers execute once for each row in a transaction. The commands of row leveltriggers are executed on all rows that are affected by the command that enables thetrigger. For example, if an UPDATE statement updates multiple rows of a table, a row triggeris fired once for each row affected by the UPDATE statement. If the triggering statementaffects no rows, the trigger is not executed at all. Row level triggers are created using theFOR EACH ROW clause in the CREATE TRIGGER command.

Statement Level Triggers

Statement level triggers are triggered only once for each transaction. Statement level trigger are thedefault type of trigger created via the CREATE TRIGGER command. Consider a case where therequirement is to prevent the DELETE operation during Sunday. For this whenever DELETEstatement deletes records, there must be PL/SQL block that will be fired only once by DELETEstatement to check that day must not be Sunday by referencing system date.

Before and After Trigger

Since triggers are executed by events, they may be set to occur immediately before or after those events. When a trigger is defined, you can specify whether the trigger must occur before or after the triggering event i.e. INSERT, UPDATE, or DELETE commands. BEFORE triggers are used, the trigger action should determine whether or not the triggering statement should be allowed to complete. By
using a BEFORE trigger, you can eliminate unnecessary processing of the triggering statement. For example: To prevent deletion on Sunday,Statement level before trigger is used on DELETEstatement. BEFORE triggers are used to derive specific column values before completing a triggering INSERT Or UPDATE statement. AFTER trigger executes the trigger action after the triggering statement is executed. AFTER triggers are used, when the triggering statement is set to completebefore executing the trigger action. For example: To perform cascade delete operation, it means that user deletes the record from one table, the corresponding records in other tables are also deleted automatically by a trigger which fired after the execution of delete statement issued by the user.

Statement Level

BEFORE option

Oracle fires the trigger only once, before executing the triggering statement.

AFTER option

Oracle fires the trigger only once, after executing the trigger statement.

Row Level

BEFORE option

Oracle fires the trigger before modifying each row affected by the triggering statement.

AFTER option

Oracle fires the trigger after modifying each row affected by the triggering statement.

Instead of triggers

Instead of triggers was first featured in Oracle 8. This was something new in the world of triggers. These are triggers that are defined on a view rather than on a table. Can use INSTEAD OF trigger to tell Oracle what to do instead of performing the actions that invoked the trigger.

Syntax of creating trigger

CREATE OR REPLACE TRIGGER trigger_name
BEFOREAFTER
DELETEINSERTUPDATE [OF column_name, ]
ON table_name
[REFERENCING {OLD AS old, NEW AS new}]
[FOR EACH ROW [WHEN condition]
DECLARE
variable declaration;
Constant declaration;
BEGIN
PL/SQL subprogram body;
EXCEPTON exception PL/SQL BLOCK;
END;



REFERENCING

specified correlation names. The user could use the Correlation names in the PL/SQL block and WHEN clause of a row trigger to refer specifically to old and new values of the current row. The default correlation names are OLD and NEW. If the row is associated with a table named OLD or NEW, this clause can be used to specify different names to avoid confusion between the table name and the
correlation name. WHEN Clausespecifies the trigger restriction. This condition has to be satisfied to fire the trigger. This condition can be specified for the ROW TRIGGER.

Example

To Create a trigger on the emp table, which store the empno and operation in tableauditor for each operation i.e. Insert, Update and Delete.

CREATE OR REPLACE TRIGGER EMP_AUDIT
AFTER INSERT OR UPDATE OR DELETE ON EMP FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO AUDITOR VALUES(:NEW.EMPNO,'INSERT');

ELSIF UPDATING THEN
INSERT INTO AUDITOR VALUES(:NEW.EMPNO,'UPDATE');

ELSIF DELETING THEN
INSERT INTO AUDITOR VALUES(:OLD.EMPNO,'DELETE');

END IF;

END;

Note: The same functionality can be achieved with the BEFORE type trigger also.

Enabling and Disabling Triggers

When a trigger is created it is automatically enabled and is triggered whenever the triggering command and the execution command is true. An enabled trigger executes the trigger body if the triggering statement is issued. To disable the execution use the ALTER TRIGGER Command with the DISABLE clause. A disable trigger does not execute the trigger body even if the triggeringstatement is issued. We can disable / enable the trigger by the following

syntax:ALTER TRIGGER DISABLE / ENABLE

Dropping Triggers

Triggers may be dropped via the drop trigger command. In order to drop a trigger, you musteither own the trigger or have the DROP ANY TRIGGER system privilege.

Syntax: DROP TRIGGER trigger_name;

Example:DROP TRIGGER rec_check;

Database Trigger V/s Procedure

Triggers don't accept parameters but procedures can accept parameters.
A trigger is automatically executed without any action required by the user. A store procedure on the other hand needs to be explicitly invoked.