1.
DML Triggers Overview
• DML Triggers execute automatically when INSERT, UPDATE, or DELETE operations occur on a
table or view.
• There are two main types:
o BEFORE Triggers: Fire before the DML event occurs.
o AFTER Triggers: Fire after the DML event occurs.
o INSTEAD OF Triggers: Used with views to override DML operations.
Syntax for DML Triggers
sql
Copy code
CREATE OR REPLACE TRIGGER trigger_name
[BEFORE | AFTER | INSTEAD OF] [INSERT | UPDATE | DELETE]
ON table_name
[FOR EACH ROW] -- Used to trigger for each affected row
WHEN (condition) -- Optional condition
BEGIN
-- Trigger logic here
END;
1. BEFORE Trigger
Fires before the INSERT, UPDATE, or DELETE event. Use it to validate or modify data before changes
are applied to the database.
1.1 BEFORE INSERT Trigger
Example: Set a default value for a column if the user didn’t provide it.
sql
Copy code
CREATE OR REPLACE TRIGGER trg_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary IS NULL THEN
:NEW.salary := 30000; -- Set default salary
END IF;
END;
• Explanation: If the salary is not provided during insertion, it is automatically set to 30,000.
1.2 BEFORE UPDATE Trigger
Example: Prevent salary from being reduced during an update.
sql
Copy code
CREATE OR REPLACE TRIGGER trg_before_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < :OLD.salary THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be decreased!');
END IF;
END;
• Explanation: This trigger raises an error if the new salary is lower than the old one.
1.3 BEFORE DELETE Trigger
Example: Prevent deletion of employees with a specific job role (e.g., managers).
sql
Copy code
CREATE OR REPLACE TRIGGER trg_before_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
IF :OLD.job_title = 'Manager' THEN
RAISE_APPLICATION_ERROR(-20002, 'Cannot delete managers!');
END IF;
END;
• Explanation: The trigger blocks any attempt to delete employees who are managers.
2. AFTER Trigger
Fires after the DML operation is completed. Use it for actions that depend on the DML operation
being successful (like logging changes or updating audit tables).
2.1 AFTER INSERT Trigger
Example: Log every new employee insertion.
sql
Copy code
CREATE OR REPLACE TRIGGER trg_after_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit (emp_id, action, action_date)
VALUES (:NEW.emp_id, 'INSERT', SYSDATE);
END;
• Explanation: Every time a new employee is added, their details are recorded in the
employee_audit table.
2.2 AFTER UPDATE Trigger
Example: Log salary changes in the audit table.
sql
Copy code
CREATE OR REPLACE TRIGGER trg_after_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit (emp_id, action, action_date, old_salary, new_salary)
VALUES (:OLD.emp_id, 'UPDATE', SYSDATE, :OLD.salary, :NEW.salary);
END;
• Explanation: This trigger logs the old and new salary whenever an employee's salary is
updated.
2.3 AFTER DELETE Trigger
Example: Archive deleted employee records.
sql
Copy code
CREATE OR REPLACE TRIGGER trg_after_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO archived_employees (emp_id, name, job_title, archived_date)
VALUES (:OLD.emp_id, :OLD.name, :OLD.job_title, SYSDATE);
END;
• Explanation: When an employee is deleted, their data is moved to the archived_employees
table.
3. INSTEAD OF Trigger
• Used on views to perform actions instead of the DML operation. This is useful because views
don't directly support INSERT, UPDATE, or DELETE.
Example: INSTEAD OF INSERT Trigger on a View
Assume we have a view emp_view that joins multiple tables:
sql
Copy code
CREATE VIEW emp_view AS
SELECT emp_id, emp_name, dept_name
FROM employees e JOIN departments d ON e.dept_id = d.dept_id;
Since INSERT is not directly possible on the view, we use an INSTEAD OF Trigger.
sql
Copy code
CREATE OR REPLACE TRIGGER trg_instead_of_insert
INSTEAD OF INSERT ON emp_view
FOR EACH ROW
BEGIN
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (:NEW.emp_id, :NEW.emp_name, (SELECT dept_id FROM departments WHERE
dept_name = :NEW.dept_name));
END;
• Explanation: This trigger inserts the data into the employees table whenever an INSERT
operation is attempted on the emp_view.
Summary of DML Trigger Types
Trigger Type Event Fires Example Use Case
BEFORE INSERT INSERT Before insert Set default values
BEFORE UPDATE UPDATE Before update Prevent invalid updates
BEFORE DELETE DELETE Before delete Block deletion of certain rows
AFTER INSERT INSERT After insert Log new records
AFTER UPDATE UPDATE After update Track changes in audit table
AFTER DELETE DELETE After delete Archive deleted records
INSTEAD OF INSERT INSERT on Views Replaces insert Insert data into underlying table
