Trigger in PL/SQL with Example - Part 1 | BEFORE Trigger

Trigger in PL/SQL

PL/SQL Triggers

Pranav Bakare ・ Sep 23

#sql #plsql #backend #database

BEFORE INSERT trigger

Here’s another simple example of a BEFORE INSERT trigger that deals with automatically formatting data before it gets inserted.

Scenario:

We have a employees table that stores employee details. One of the fields is employee_email, and we want to ensure that all email addresses are inserted in lowercase to maintain consistency. If someone tries to insert an email in uppercase or mixed case, the trigger will convert it to lowercase before the data is inserted.

---

Step 1: Create the employees Table

-- Create employees table
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(100),
    employee_email VARCHAR2(100)
);

---

Step 2: Create a PL/SQL BEFORE INSERT Trigger

We will create a BEFORE INSERT trigger that automatically converts the employee_email to lowercase if it is entered in uppercase or mixed case.

-- Create the BEFORE INSERT trigger
CREATE OR REPLACE TRIGGER format_email_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    -- Convert the employee_email to lowercase before inserting
    :NEW.employee_email := LOWER(:NEW.employee_email);
END;
/

Explanation:

1. Trigger Name: format_email_before_insert.
2. Trigger Type: This is a BEFORE INSERT trigger, meaning it runs before the row is inserted.
3. :NEW.employee_email: Refers to the email that is being inserted. We use the LOWER() function to convert the email to lowercase.

---

Step 3: Test the Trigger

Case 1: Insert an employee with an email in uppercase

-- Insert an employee with an email in uppercase
INSERT INTO employees (employee_id, employee_name, employee_email)
VALUES (1, 'John Doe', '[e-mail]');

-- Select from the employees table
SELECT * FROM employees;

Output:
The trigger automatically converts the email to lowercase.

Case 2: Insert an employee with an email in mixed case

-- Insert an employee with an email in mixed case
INSERT INTO employees (employee_id, employee_name, employee_email)
VALUES (2, 'Jane Smith', '[e-mail]');

-- Select from the employees table
SELECT * FROM employees;

Output:
The trigger automatically converts the email to lowercase.

---

Conclusion:

This is a simple but useful example of a BEFORE INSERT trigger. The trigger ensures that all email addresses inserted into the employees table are consistently stored in lowercase. This can be helpful in scenarios where case sensitivity should not matter for certain fields, such as email addresses.