The title of this tutorial sounds very funny to so many, as it is a mythical feature people mistakenly talk about when talking of cascade constraints but later remember only update and delete can be cascaded across tables.
Cascading is a form of constraint where a deletion or update of a parent table causes all occurrences of child tables that have been tied to it by means of foreign key constraints to be affected in this same way as the parent. This improves your relations integrity but can also cause data loss in case of delete cascades.
But what if we want to log activities of one table in another i.e the child table contains same Primary elements as the parent table but different fields/columns and we don’t want to go through the stress of writing extra php/asp codes to log our transactions, or we have many application in different languages that have to use this table but a new policy requires logging of transaction as they are inserted.
“Yeah, you wish there’s an insert cascade constraint” but that won’t be to feasible due to unknown constraints on the affected child table after insert, which brings s to TRIGGERS.
You can cascade insertions using after event triggers, we choose after because in this instance the child table must be able to reference an existing column in its parent table before permitting insertion lest we get the “ERROR 1452: Cannot add or update a child row: a foreign key constraint fails “. The following paragraph will show us a step based guide to achieving CASCADE ON INSERT.
HOW TO CASCADE INSERT OPERATIONS
Suppose we have a super market with 3 tables, where Item table stores the items in the supermarket, alongside their price, name, stock left etc. a SALE table that shows the time of sale and quantity of each item bought, and a third table that shows items, and the total sales on each item.
create database supermarket;
use supermarket;
CREATE TABLE item
(
id INT(2) NOT null AUTO_INCREMENT,
item_name VARCHAR(20) not null,
price INT(5) NOT NULL,
stock INT(5) NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE sale
(
sale_id INT(2) NOT NULL AUTO_INCREMENT,
item_id INT(2) NOT NULL,
price INT(5) NOT NULL,
qty INT(5) NOT NULL,
sales_time TIMESTAMP,
PRIMARY KEY(sale_id),
FOREIGN KEY(item_id) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE
/*(NO) ON INSERT CASCADE ****** NO CASCADE ON INSERT */
);
CREATE TABLE itemLog
(
id INT(2) NOT NULL,
sale INT(11) NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(id) REFERENCES item(id)
);
/* TRIGGERS */
DELIMITER $$
CREATE TRIGGER itemlogger AFTER INSERT ON item FOR EACH ROW
BEGIN
INSERT INTO itemlog (id,sale) VALUES (NEW.id,0);
END$$
DELIMITER ;
/* FOR THE SALES TABLE */
DELIMITER $$
CREATE TRIGGER salesLogger AFTER INSERT ON sale FOR EACH ROW
BEGIN
UPDATE itemlog SET sale = sale + NEW.qty WHERE id = NEW.item_id;
END$$
DELIMITER ;
Now perform INSERT operation on the item table and watch the process spill into the itemLog table;
For example; After the query below
INSERT INTO item (id,item_name,price,stock) VALUES(1,'Game',254,12);
| Id | item_name | price | stock |
|---|---|---|---|
| 1 | Game | 254 | 12 |
The Item Log table becomes
| id | sale |
|---|---|
| 1 | 0 |
While the query;
INSERT INTO sale (sale_id,item_id,price,qty) VALUES ( 0, 1 ,212, 9);
INSERT INTO sale (sale_id,item_id,price,qty) VALUES ( 0, 1 ,212, 2);
| Sale_id | item_id | price | qty | sales_time |
|---|---|---|---|---|
| 1 | 1 | 212 | 9 | 2012-07-12 22:52:53 |
| 2 | 1 | 212 | 2 | 2012-07-12 22:54:40 |
While the ItemLog table becomes;
| id | sale |
|---|---|
| 1 | 11 |
Notice the increasing stock sale value in the item logger table. This example has shown how to CASCADE INSERT OPERATIONS.