PL/SQL Trigger- Basics & Uses

PL/SQL Trigger is block of code attached to an event that may occur on a data table.   Trigger is a stored block with [Declare], Begin, End and Associated with a DML action of database table or view. It fires automatically when certain DML action is carried out on the table. You cannot execute a trigger explicitly.

Triggers are automatically fired before or after an event like INSERT, DELETE, and UPDATE. INSTEAD OF trigger work only on views

There are two kinds of triggers

• Statement level trigger fires once for all the rows being affected by  DML statement on table
• Row level trigger individually fired for each row affected by the DML statement

Syntax for creating a PL/SQL Trigger

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE|AFTER|INSTEAD OF} triggering_event [referencing_clause] ON {table_name | view_name}

[WHEN condition] [FOR EACH ROW] 

DECLARE

    Declaration statements

[BEGIN

    Executable statements

EXCEPTION

    Exception-handling statements]

END;

• Create or replace if the trigger exists then the new definition will replace the older one else trigger with new definition is created.
• {BEFORE|AFTER|INSTEAD OF} when the trigger is fired relative to the associated DML statement.
• Triggering_event  is INSERT, DELETE, UNDATE for tables and  INSTEAD OF for views
• ON {table_name | view_name}  name of the view or table o which an execution of DML will trigger the action
• [WHEN condition]- restrict the condition along with the triggering event.
• [FOR EACH ROW]  –required to make a row level trigger. By default a trigger is Statement Level  

Trigger naming rules are same as that of PL/SQL named blocks

• A trigger name must be unique in a schema and is a PL/SQL blocks with declarative, executable, and exception handling sections. 

Features of a PL/SQL Trigger

• A Trigger is an independent database object
• It cannot be part of a package
• It cannot be local to a PL/SQL block. 
• It is executed automatically with the action on table.

Uses of PL/SQL Triggers

1. Auditing
   • Storing information about (sensitive) data when some changes are made on a table into a audit table
   • Can store old and new values, user who did changes and timestamp.
2. Data Integrity
   • Implement checks on data against business rules
   • Compares value stored and new value being updated
3. Referential integrity
   • Allows to update foreign key value if primary key values is changed.
   • If Department ID (DeptID) is changed, appropriately update department id of employees belonging to those values as foreign key
4. Derived data
   • If a column contains value based on calculation on another column, it must be updated if base column value changes
   • Example- Net salary depends on deduction. If a deduction is recalculated the net salary must be updated automatically
5. Ensures Security
   • Logging of database access can be audited
   • By preserving date and time each user logs on or denying access to certain users or at certain times
6. Synchronizing multiple copies in a distributed database. Like Airline, Movie, railway or event booking system
7. Generating statistics on table access- when a table is accessed by a user to do specific activity.

admin