Solution: Triggers Exercise

One of the requirements of a database is that once a query is committed it cannot be undone.

• This can be a problem when a user deletes data, the query is committed, and then they realize that they deleted the incorrect data items.

In this exercise you will implement an undo function using a trigger and a stored procedure that allows an UNDO operation on the CUSTOMER table.

Trigger:

A DELETE trigger saves deleted data in a virtual table called OLD.

• OLD is a virtual table that is only present in a trigger – it has the same layout as the table the trigger is attached to.
• OLD contains the row (or rows) that were deleted (for a DELETE query) or updated (for an UPDATE query – pre-update.)
• OLD can only be referenced from within DELETE Trigger code to access the rows being deleted.
• The values in OLD are all read-only and cannot be updated.
• (There is also a virtual table called NEW that contains the row (or rows) that were inserted (for an INSERT query) or updated (for an UPDATE query – post-update)).
• Link

The following example shows the code for a trigger that saves the data from the NorthWind categories table to a table called categoriesBackup whenever an item is deleted from the categories table.

DELIMITER $$
CREATE TRIGGER tr_del_saveCategory BEFORE DELETE ON categories
FOR EACH ROW
BEGIN
INSERT INTO categoriesBackup(CategoryID, CategoryName, Description, Picture)
VALUES(OLD.CategoryID, OLD.CategoryName, OLD.Description, OLD.Picture);
END$$
DELIMITER ;

Before this trigger can be run, the categoriesBackup table must be created. See the table cloning explanation for an example.

• Note that a duplicate of a table can be created in MySQL using the CREATE TABLE <NEW_TABLE> LIKE <OLD_TABLE>, but this cannot be used in a trigger.
  • Creating a table involves an implicit commit.
  • A trigger invoked as part of a transaction, cannot perform a commit either implicitly or explicitly

Exercise:

Write a trigger named tr_del_saveCustomerInfo to save the deleted data from the NorthWind customers table into a backup table called backupCustomers.

• Do not forget to create the backupCustomers table per the reminder above.

Solution:

CREATE TABLE backupCustomers LIKE customers;

DELIMITER $$
CREATE TRIGGER tr_del_saveCustomerInfo BEFORE DELETE ON customers
FOR EACH ROW
BEGIN
INSERT INTO backupCustomers(CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
VALUES(OLD.CustomerID, OLD.CompanyName, OLD.ContactName, OLD.ContactTitle, OLD.Address, OLD.City, OLD.Region, OLD.PostalCode, OLD.Country, OLD.Phone, OLD.Fax);
END$$
DELIMITER ;

Testing:

In order to test this trigger, a customer must be deleted from the customers table and backed up to the backupCustomers table.

• However, if a customer with an outstanding order is deleted, the query will result in "a foreign key constraint fails" error and the trigger will not be fired.

Try it with a query like

DELETE FROM customers WHERE CustomerID = "WANDK";

Hence, you need to find a customer (or customers) with no outstanding orders.

• In order to accomplish, you need to use a LEFT JOIN.

SELECT customers.CustomerID FROM customers LEFT JOIN orders ON customers.CustomerID = orders.CustomerID;

One of the customers with no outstanding orders is Hungry Owl All-Night Grocers. To delete Hungry Owl All-Night Grocers, use the query:

DELETE FROM customers WHERE CustomerID = "HUNGO";

Now open the customer table and verify that Hungry Owl All-Night Grocers is no longer in the table (you may need to change the "Number of rows" option to All to verify its absence).

Next, open the backupCustomers table to verify that Hungry Owl All-Night Grocers is now in the table.

For a trigger tutorial with a great example, visit this link.