CASCADE ON INSERT

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) ON DELETE CASCADE ON UPDATE CASCADE

);

/*                                          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.