In-depth Notes on Database Triggers and PL/SQL

Objectives

• Describe database triggers and their uses.
• Define a database trigger.
• Recognize the difference between a database trigger and an application trigger.
• List two or more guidelines for using triggers.
• Compare and contrast database triggers and stored procedures.

Purpose of Triggers

• Triggers allow specific actions to perform automatically within the database without additional application code.
• They increase both database and application power.
• Triggers facilitate automated data handling, such as recording changes or enforcing rules.

Need for a Trigger

• Example scenario: Business rule requires that any employee's salary change be recorded in a logging table.
• Without a trigger, two procedures would be needed: UPD_EMP_SAL for updating the salary and LOG_SAL_CHANGE for logging changes.
• A trigger automates this process, eliminating the need for any explicit procedure invocation.

Example of a Simple Trigger

• A simple trigger automatically logs salary changes:

  CREATE OR REPLACE TRIGGER log_sal_change_trigg
  AFTER UPDATE OF salary ON employees
  BEGIN
      INSERT INTO log_table (user_id, logon_date) VALUES (USER, SYSDATE);
  END;

• The trigger activates when a specified event (updating a salary) occurs.

What is a Trigger?

• A database trigger is:
  • A PL/SQL block linked to a specific action (event).
  • Executes automatically upon the occurrence of the associated action.
  • Stored in the database.
• Example: Trigger associated with UPDATE OF salary ON employees.
• Triggers do not receive parameters and continuously monitor database events.

Database Triggers vs. Application Triggers

• Database Triggers:

  • Automatically execute on data or system events (e.g., DML, DDL).
  • Created and stored in the database.

• Application Triggers:

  • Execute on events within an application.
  • Not part of the database.

Events Causing Triggers to Fire

• Database triggers can fire due to:
  • DML operations on tables.
  • DML operations on views (with INSTEAD OF triggers).
  • DDL statements (e.g., CREATE, ALTER).
  • Database system events (e.g., user logon, database shutdown).

Types of Triggers

• Row-Level Triggers: Fire once for each affected row by the triggering statement.
• Statement-Level Triggers: Fire once for the entire statement, regardless of the number of affected rows.

Possible Uses for Triggers

• Enhance security rules.
• Automatically create auditing records.
• Enforce data integrity rules.
• Prevent accidental table drops or invalid DML transactions.
• Automatically generate derived column values.
• Maintain synchronous table replication.

Example of Creating Logging Records Automatically

• To record user logons automatically:

  CREATE TABLE log_table ( user_id VARCHAR2(30), logon_date DATE);
  CREATE OR REPLACE TRIGGER logon_trigg
  AFTER LOGON ON DATABASE
  BEGIN
      INSERT INTO log_table (user_id, logon_date) VALUES (USER, SYSDATE);
  END;

Example of Enforcing Data Integrity Rules

• Ensures no employee's job can revert to a previously held job:

  CREATE OR REPLACE TRIGGER check_sal_trigg
  BEFORE UPDATE OF job_id ON employees
  FOR EACH ROW
  DECLARE
      v_job_count INTEGER;
  BEGIN
      SELECT COUNT(*) INTO v_job_count
      FROM job_history
      WHERE employee_id = :OLD.employee_id
        AND job_id = :NEW.job_id;
      IF v_job_count > 0 THEN
          RAISE_APPLICATION_ERROR(-20201, 'This employee has already done this job');
      END IF;
  END;

Guidelines for Using Triggers

• Avoid using triggers for actions easily defined by other means (e.g., simple integrity rules should use constraints).
• Excessive use of triggers may lead to slower processing and maintainability issues.
• Use triggers judiciously and be mindful of recursion and cascading effects.
• Keep trigger logic simple; for complex logic, consider using stored or packaged procedures.

Comparison of Database Triggers and Stored Procedures

Terminology

• Application triggers: Specific to application events.
• Database triggers: Linked to database events and operations.
• Triggers: PL/SQL blocks responding to defined events.