Mysql 'statement level' Trigger … You Can Do It With Two Tricks

Introduction

I searched many times to find any trick or workaround to make "statement level" triggers in MySQL like the one in Oracle, but I didn't find one. So I came up with this trick and it works for me. Hope it helps someone.

Using the Code

Let's say we want to insert multiple rows and we want to do something for one time but we cannot avoid "FOR EACH ROW" in MySQL trigger.

`insert to table1 (sname,age) VALUES ('mariam',5),('jojo',3),('ahmed',29)`

• First, create a table statementidstable with two columns (ID, statementid).
• Second, you have to prepare a unique ID for your statement with your software.

Let's say it's '123456'.
Change your statement to:

INSERT INTO table1 (sname,age,uniqueid) VALUES ('mariam',5,123456),_
('jojo',3,123456),('ahmed',29,123456)

MySql Trigger:

CREATE TRIGGER
table1_after_insert
AFTER INSERT
ON
table1
FOR EACH ROW
BEGIN
     DECLARE isfired tinyint(1);
     SELECT COUNT(statementid) INTO isfired from _
     statementidstable where statementid=NEW.uniqeid LIMIT 1;
     IF isfired = 0 THEN
         'DO WHAT YOU WANT HERE
         'because this is the first time for this statement id
         'then insert the statementid to statementidstable
         INSERT INTO statementidstable (statementid) VALUES (NEW.uniqeid)
     ELSE
         'Nothing will happen because
         'you already have the statement id in statementidstable
     END IF;
 END;

Then delete the statementid from statementidstable after you are finished (handle this with your software).

** Another Trick

You can do it with no need for a statementid and also your trigger can fire once after inserting first row only or once after inserting last row only or both of them.
In this trick, you have to prepare your statement like this:

`insert to table1 (sname,age,rowid) VALUES ('mariam',5,1),('jojo',3,0),('xyz',3,0),('ahmed',29,-1)`

• First row has rowid=1 (not like any other row) makes your trigger know that it will fire something once after inserting first row.
• Last row has rowid=-1 (not like any other row) makes your trigger know that it will fire something once after inserting last row.
• Other rows have rowid=0 or any other value not in (1,-1).

CREATE TRIGGER
table1_after_insert
AFTER INSERT
ON
table1
FOR EACH ROW
BEGIN
	IF rowid = 1 THEN
		'DO WHAT YOU WANT HERE after inserting first row only
	ELSEIF rowid = -1
		'DO WHAT YOU WANT HERE after inserting last row only
	ELSE
		'NOTHING WILL HAPPEN
	END IF;
END;

Points of Interest

Every trick has its own benefit... and note that the first trick can handle 'insert statement' and 'update statement'. I am still working on 'delete statement'.

History

• 2^nd January, 2022: Initial version