Triggers

Trigger making

-- Trigger to update the LastModified column after a salary update
DELIMITER $$
CREATE TRIGGER update_last_modified
BEFORE UPDATE ON Employees
FOR EACH ROW
BEGIN
IF NEW.Salary <> OLD. Salary THEN SET NEW.LastModified = CURRENT_TIMESTAMP;
END IF;
END$$
DELIMITER;
--
Update salary to trigger the LastModified update
UPDATE Employees SET Salary = 22000 WHERE EmployeeID = 1;
Check if LastModified was updated SELECT \* FROM Employees;

Auditing triggers

-- Trigger to log deletions from Orders into AuditLog
DELIMITER $$
CREATE TRIGGER log_order_deletion
AFTER DELETE ON Orders
FOR EACH ROW
BEGIN
INSERT INTO AuditLog (OrderID, DeletedBy, DeletedOn) VALUES (OLD.OrderID, 'Admin', CURRENT TIMESTAMP);
END$$
DELIMITER;
-- Delete an order to trigger the audit log
DELETE FROM Orders WHERE OrderID = 102;
Check if the deletion was logged in AuditLog SELECT * FROM AuditLog;

Views

View creation

CREATE VIEW Details View AS
SELECT eno, ENAME, Job type
FROM employee WHERE eno < 60;

View updating

CREATE OR REPLACE VIEW Details View AS
SELECT eno, ENAME, Job type
FROM employee WHERE eno < 70;