TRIGGER SQL

 

EX :4

Create a row level trigger for the customers table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old & new Salary.

 CUSTOMERS(ID,NAME,AGE,ADDRESS,SALARY) 

STEP Table 1:   Create Database , Use DB , Create Table(salary_log)

CREATE TABLE salary_log (

    log_id INT AUTO_INCREMENT PRIMARY KEY,

    customer_id INT,

    old_salary DECIMAL(10,2),

    new_salary DECIMAL(10,2),

    salary_difference DECIMAL(10,2),

    action_type VARCHAR(10),

    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

 STEP Table 2: Create CUSTOMERS Table

CREATE TABLE CUSTOMERS (

    ID INT PRIMARY KEY,

    NAME VARCHAR(50),

    AGE INT,

    ADDRESS VARCHAR(100),

    SALARY DECIMAL(10,2)

);

 INSERT Trigger

DELIMITER $$

CREATE TRIGGER customers_after_insert

AFTER INSERT ON CUSTOMERS

FOR EACH ROW

BEGIN

    INSERT INTO salary_log(customer_id, old_salary, new_salary, salary_difference, action_type)

    VALUES(NEW.ID, NULL, NEW.SALARY, NEW.SALARY, 'INSERT');

END$$

DELIMITER ;

UPDATE Trigger

DELIMITER $$

CREATE TRIGGER customers_after_update

AFTER UPDATE ON CUSTOMERS

FOR EACH ROW

BEGIN

    INSERT INTO salary_log(customer_id, old_salary, new_salary, salary_difference, action_type)

    VALUES(NEW.ID, OLD.SALARY, NEW.SALARY, NEW.SALARY - OLD.SALARY, 'UPDATE');

END$$

DELIMITER ;

 

DELETE Trigger

DELIMITER $$

CREATE TRIGGER customers_after_delete

AFTER DELETE ON CUSTOMERS

FOR EACH ROW

BEGIN

    INSERT INTO salary_log(customer_id, old_salary, new_salary, salary_difference, action_type)

    VALUES(OLD.ID, OLD.SALARY, NULL, -OLD.SALARY, 'DELETE');

END$$

DELIMITER ;

Test the Triggers

 Insert Data:

INSERT INTO CUSTOMERS VALUES (1, 'Jo', 30, 'New York', 40000);

 Update Salary:

UPDATE CUSTOMERS 

SET SALARY = 50000 

WHERE ID = 1;

Delete Record:

DELETE FROM CUSTOMERS WHERE ID = 1;

Check Salary Difference:

SELECT * FROM salary_log;

note:

You will see:

• Old salary

• New salary

• Salary difference

• Action type (INSERT / UPDATE / DELETE)