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.

PL/SQL Trigger

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.

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *