KEMBAR78
Lab Program4 | PDF
Scribd
Upload
36 views2 pages

Lab Program4

The document outlines the creation of a row-level trigger for the CUSTOMERS table in a COMPANY04 database, which activates on INSERT, UPDATE, or DELETE operations. It includes specific triggers to display salary information: the inserted salary, the difference in salary after an update, and the deleted salary. Additionally, it provides test cases for each trigger to demonstrate their functionality.

Uploaded by

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

Lab Program4

The document outlines the creation of a row-level trigger for the CUSTOMERS table in a COMPANY04 database, which activates on INSERT, UPDATE, or DELETE operations. It includes specific triggers to display salary information: the inserted salary, the difference in salary after an update, and the deleted salary. Additionally, it provides test cases for each trigger to demonstrate their functionality.

Uploaded by

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

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)

CREATE DATABASE COMPANY04;

USE COMPANY04;

CREATE TABLE CUSTOMERS ( ID INT PRIMARY KEY, NAME VARCHAR(255),

AGE INT, ADDRESS VARCHAR(255), SALARY DECIMAL(10, 2) );

-- INSERT TRIGGER

DELIMITER //

CREATE TRIGGER after_insert

AFTER INSERT ON CUSTOMERS

FOR EACH ROW

BEGIN

SET @ins_sal = CONCAT('Inserted salary is ', NEW.SALARY);

END; //

DELIMITER ;

-- UPDATE TRIGGER

CREATE TRIGGER after_update

AFTER UPDATE ON CUSTOMERS

FOR EACH ROW

BEGIN

SET @upd_sal = CONCAT('After updating salary difference is ', NEW.SALARY -


OLD.SALARY);

END;
-- DELETE TRIGGER

CREATE TRIGGER after_delete

AFTER DELETE ON CUSTOMERS

FOR EACH ROW

BEGIN

SET @del_sal = CONCAT('Deleted salary is ', OLD.SALARY);

END;

-- test INSERT TRIGGER

INSERT INTO CUSTOMERS VALUES (101, 'Shankara', 35, '123 Main St', 50000.00);

SELECT @ins_sal;

-- test UPDATE TRIGGER

UPDATE CUSTOMERS SET SALARY = 65000.00 WHERE ID = 101;

SELECT @upd_sal;

-- test DELETE TRIGGER

DELETE FROM CUSTOMERS WHERE ID = 101;

SELECT @del_sal;

drop trigger after_delete;

You might also like