Triggers in PL/SQL


In the context of PL/SQL (Procedural Language/Structured Query Language), triggers are a type of stored procedure that automatically execute (or "fire") when a specified event occurs. Triggers can be used to enforce business rules, maintain audit trails, and synchronize tables.

Types of Triggers
  • Row-level triggers: These are executed for each row affected by the triggering event.
  • Statement-level triggers: These are executed once for the entire triggering event.
Trigger Timing
Triggers can be set to fire:
  • Before the triggering event.
  • After the triggering event.
  • Instead of the triggering event (mostly used in views).
Creating Triggers
Basic Syntax

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} triggering_event
ON table_name
[FOR EACH ROW]
DECLARE
    -- variable declarations
BEGIN
    -- trigger logic
END;

Example 1: Row-Level Trigger

Suppose you have an employees table, and you want to log changes to the salary column.

Create the logging table:

CREATE TABLE salary_log (
    emp_id NUMBER,
    old_salary NUMBER,
    new_salary NUMBER,
    change_date DATE
);

Create the trigger:
CREATE OR REPLACE TRIGGER salary_change_trigger
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_log (emp_id, old_salary, new_salary, change_date)
VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);
END;

  • :OLD refers to the old value before the update.
  • :NEW refers to the new value after the update.
Example 2: Statement-Level Trigger

Suppose you want to track when the employees table is modified.
Create the audit table:
CREATE TABLE audit_log (
    table_name VARCHAR2(50),
    action VARCHAR2(50),
    action_date DATE
);

Create the trigger
CREATE OR REPLACE TRIGGER employees_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
BEGIN
    INSERT INTO audit_log (table_name, action, action_date)
    VALUES ('employees', 'DML operation', SYSDATE);
END;


Example 3: Before Insert Trigger

You want to ensure that a value is always uppercase before inserting it into a table.

Create the table:
CREATE TABLE departments (
    dept_id NUMBER,
    dept_name VARCHAR2(50)
);

Create the trigger:
CREATE OR REPLACE TRIGGER dept_name_uppercase_trigger
BEFORE INSERT ON departments
FOR EACH ROW
BEGIN
    :NEW.dept_name := UPPER(:NEW.dept_name);
END;


In this example, :NEW.dept_name is converted to uppercase before the row is inserted into the departments table.

Best Practices
  • Use row-level triggers for operations that need to check or modify each row's values.
  • Use statement-level triggers for operations that need to enforce rules at the table level.
  • Keep triggers simple to avoid complex interdependencies and performance issues.
  • Use triggers judiciously to maintain database integrity and ensure that business rules are consistently applied.
Triggers are powerful tools in PL/SQL for managing and maintaining data integrity, and understanding how to create and use them effectively can greatly enhance your database management capabilities.

Real Time Example:
We will create an employees table and a trigger that logs changes to the salary column into a salary_log table.

-- Create employees table
CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(100),
    salary NUMBER
);

-- Create salary_log table
CREATE TABLE salary_log (
    log_id NUMBER PRIMARY KEY,
    emp_id NUMBER,
    old_salary NUMBER,
    new_salary NUMBER,
    change_date DATE,
    CONSTRAINT fk_emp FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);

-- Create sequence for salary_log table
CREATE SEQUENCE salary_log_seq
START WITH 1
INCREMENT BY 1;

-- Create the trigger
CREATE OR REPLACE TRIGGER salary_change_trigger
AFTER UPDATE OF salary ON employees
FOR EACH ROW
DECLARE
    log_id_seq NUMBER;
BEGIN
    -- Generate a unique log_id for the salary_log table
    SELECT salary_log_seq.NEXTVAL INTO log_id_seq FROM dual;
    
    INSERT INTO salary_log (log_id, emp_id, old_salary, new_salary, change_date)
    VALUES (log_id_seq, :OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

-- Insert an employee
INSERT INTO employees (emp_id, emp_name, salary) VALUES (1, 'John Doe', 50000);

-- Update the employee's salary
UPDATE employees SET salary = 55000 WHERE emp_id = 1;

-- Check the salary_log table
SELECT * FROM salary_log;

Example:Let's create a complete example of a statement-level trigger. We'll use an orders table and create a statement-level trigger to log any insert, update, or delete operations performed on this table. The logs will be stored in an audit_log table.

-- Create orders table
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    order_date DATE,
    total_amount NUMBER
);

-- Create audit_log table
CREATE TABLE audit_log (
    log_id NUMBER PRIMARY KEY,
    table_name VARCHAR2(50),
    operation VARCHAR2(50),
    operation_date DATE
);

-- Create sequence for audit_log table
CREATE SEQUENCE audit_log_seq
START WITH 1
INCREMENT BY 1;

-- Create the trigger
CREATE OR REPLACE TRIGGER orders_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON orders
DECLARE
    log_id_seq NUMBER;
BEGIN
    -- Generate a unique log_id for the audit_log table
    SELECT audit_log_seq.NEXTVAL INTO log_id_seq FROM dual;
    
    INSERT INTO audit_log (log_id, table_name, operation, operation_date)
    VALUES (log_id_seq, 'orders', CASE
                                     WHEN INSERTING THEN 'INSERT'
                                     WHEN UPDATING THEN 'UPDATE'
                                     WHEN DELETING THEN 'DELETE'
                                  END, SYSDATE);
END;
/

-- Insert an order
INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES (1, 101, SYSDATE, 1500);

-- Update the order
UPDATE orders SET total_amount = 1600 WHERE order_id = 1;

-- Delete the order
DELETE FROM orders WHERE order_id = 1;

-- Check the audit_log table
SELECT * FROM audit_log;


Example:This example demonstrates how to create a trigger that ensures a column value is always stored in uppercase before inserting or updating a row in the table.
-- Create departments table
CREATE TABLE departments (
    dept_id NUMBER PRIMARY KEY,
    dept_name VARCHAR2(50)
);

-- Create the trigger
CREATE OR REPLACE TRIGGER dept_name_uppercase_trigger
BEFORE INSERT OR UPDATE ON departments
FOR EACH ROW
BEGIN
    :NEW.dept_name := UPPER(:NEW.dept_name);
END;
/

-- Insert a department with a lowercase name
INSERT INTO departments (dept_id, dept_name) VALUES (1, 'marketing');

-- Update the department name to lowercase
UPDATE departments SET dept_name = 'finance' WHERE dept_id = 1;

-- Check the departments table
SELECT * FROM departments;

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more