Scribd
Upload
9 views2 pages

10exp Implementation of Triggers in SQL

The document provides a guide on implementing triggers in SQL, including syntax and examples for creating triggers for various actions such as INSERT, UPDATE, and DELETE. It also includes an example of a log table to record actions performed on the employee table. Additionally, it explains how to drop a trigger when no longer needed.

Uploaded by

gaurav.nullbyte
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
9 views2 pages

10exp Implementation of Triggers in SQL

The document provides a guide on implementing triggers in SQL, including syntax and examples for creating triggers for various actions such as INSERT, UPDATE, and DELETE. It also includes an example of a log table to record actions performed on the employee table. Additionally, it explains how to drop a trigger when no longer needed.

Uploaded by

gaurav.nullbyte
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

Exp 10

Implementation of Triggers in SQL.


CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
-- SQL statements
END;

Example: Create a Log Table First


CREATE TABLE emp_log (
log_id INT AUTO_INCREMENT PRIMARY KEY,
empid INT,
action_type VARCHAR(20),
action_time DATETIME
);

Example 1: Trigger After Insert on employee


CREATE TRIGGER after_employee_insert
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
INSERT INTO emp_log (empid, action_type, action_time)
VALUES (NEW.empid, 'INSERT', NOW())
END;

Example 2: Trigger Before Update (e.g., log salary change)


CREATE TRIGGER before_salary_update
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
Exp 10
IF OLD.salary != NEW.salary THEN
INSERT INTO emp_log (empid, action_type, action_time)
VALUES (OLD.empid, 'SALARY UPDATE', NOW());
END IF;
END;

Example 3: Trigger After Delete


CREATE TRIGGER after_employee_delete
AFTER DELETE ON employee
FOR EACH ROW
BEGIN
INSERT INTO emp_log (empid, action_type, action_time)
VALUES (OLD.empid, 'DELETE', NOW());
END;

To Drop a Trigger
DROP TRIGGER trigger_name;

You might also like