Trigger to Prevent Deletion from Customers Table
Trigger to Prevent Deletion from Customers Table
Customer Table:
Query Explanation:
-
DELIMITER //
MySQL uses;to end a statement. But since we are writing multiple statements inside a trigger, we change the delimiter temporarily to//so MySQL doesn’t get confused. -
CREATE TRIGGER prevent_customer_deletion
We are creating a trigger namedprevent_customer_deletion. -
BEFORE DELETE ON Customers
This means the trigger will activate before any row is deleted from theCustomerstable. -
FOR EACH ROW
The trigger checks every row that’s about to be deleted. -
SIGNAL SQLSTATE '45000' ...
This line throws a custom error and stops the delete operation.
It will display the message:
Deletion from Customers table is not allowed. -
DELETE FROM Customers...
This is an example delete query. But when executed, the trigger will stop it and throw an error.
SQL Query:
-- Step 1: Change the delimiter to allow writing the full trigger body
DELIMITER //
-- Step 2: Create a trigger to prevent deletion from the Customers table
CREATE TRIGGER prevent_customer_deletion
BEFORE DELETE ON Customers
FOR EACH ROW
BEGIN
-- This will stop the deletion and show a custom error message
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Deletion from Customers table is not allowed.';
END;
//
-- Step 3: Reset the delimiter back to default
DELIMITER ;
-- Step 4: Try deleting a customer (this will be blocked by the trigger)
DELETE FROM Customers WHERE CustomerID = 1;
Output:
Error Code: 1644. Deletion from Customers table is not allowed.
