PROGRAM - 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)
1. Create the CUSTOMERS Table
mysql> CREATE TABLE CUSTOMERS (
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(255),
AGE INT,
ADDRESS VARCHAR(255),
SALARY DECIMAL(10, 2)
);
Query OK, 0 rows affected (0.06 sec)
2. Create Trigger for INSERT Operation
mysql> DELIMITER //
mysql> CREATE TRIGGER after_insert_salary_difference
AFTER INSERT ON CUSTOMERS
FOR EACH ROW
BEGIN
SET @my_sal_diff = CONCAT('salary inserted is ', NEW.SALARY);
END//
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
3. Create Trigger for UPDATE Operation
mysql> DELIMITER //
mysql> CREATE TRIGGER after_update_salary_difference
AFTER UPDATE ON CUSTOMERS
FOR EACH ROW
BEGIN
DECLARE old_salary DECIMAL(10, 2);
DECLARE new_salary DECIMAL(10, 2);
SET old_salary = OLD.SALARY;
SET new_salary = NEW.SALARY;
SET @my_sal_diff = CONCAT('salary difference after update is ', NEW.SALARY - OLD.SALARY);
END//
Query OK, 0 rows affected (0.02 sec)
mysql> DELIMITER ;
4. Create Trigger for DELETE Operation
mysql> DELIMITER //
mysql> CREATE TRIGGER after_delete_salary_difference
AFTER DELETE ON CUSTOMERS
FOR EACH ROW
BEGIN
SET @my_sal_diff = CONCAT('salary deleted is ', OLD.SALARY);
END//
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
5. Testing the Trigger
mysql> INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY)
VALUES ('Shankara', 35, '123 Main St', 50000.00);
Query OK, 1 row affected (0.03 sec)
mysql> SELECT @my_sal_diff AS SAL_DIFF;
+-----------------------------+
| SAL_DIFF |
+-----------------------------+
| salary inserted is 50000.00 |
+-----------------------------+
1 row in set (0.00 sec)
mysql> UPDATE CUSTOMERS
SET SALARY = 55000.00
WHERE ID = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT @my_sal_diff AS SAL_DIFF;
+-------------------------------------------+
| SAL_DIFF |
+-------------------------------------------+
| salary difference after update is 5000.00 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> DELETE FROM CUSTOMERS
WHERE ID = 1;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT @my_sal_diff AS SAL_DIFF;
+----------------------------+
| SAL_DIFF |
+----------------------------+
| salary deleted is 55000.00 |
+----------------------------+
1 row in set (0.00 sec)