The previous two parts have looked at some solutions offered by triggers. Let’s look now at some wishful triggers solutions, which are currently unavailable because of triggers limitations.
Triggers Use Case Compilation, Part I
Triggers Use Case Compilation, Part II
Limitations and wishful features
Triggers are slow
The overhead of adding triggers is usually an even breaker. But I would like to believe speed will improve in time!
Triggers cannot act on the same table which activated them.
A thing I would like to do is have a rotating table. A log table is a perfect example: I only want to store logs up to 7 days back, or up to 1M rows. ON INSERT, (or once every 1000 inserts or so), I wish to remove oldest rows. This is not possible today since I can’t DELETE rows from the same table which caused the ON INSERT trigger to run. It can’t be hacked by calling on another table, then doing a circular trigger trick. MySQL will raise an error on run time, complaining about a loop.
Triggers cannot act on system tables.
Now why would I want to do that? Well, one of the first things I look at when reviewing a database is the users grants. I always find a list of users which is just too permissive, with far too many users than required. I once came upon a database with 273 users, where only 5 of them were actually in use. “When were these added?”, I asked – but nobody knew.
I would love to have an ON INSERT and an ON UPDATE trigger on the mysql.user table, which lists down the time of user creation and the invoking user (who would usually be ‘root’) and host, so it’s easier to track down who did what.
You cannot execute prepared statements from within a trigger.
Not much to add here. The possibilities are too many.
You can’t spawn an ANALYZE TABLE from a trigger
What I want to do is to run an ANALYZE TABLE once every 10K inserts or deletes, so the table takes care of itself. I’ve tried hacking this with prepared statements (you can’t use them); with cursors (you can only run a cursor on SELECT queries) or otherwise SQL hacks (none worked). If anyone finds a hack around it – please let me know!
You can’t have more than one trigger on the same event per table
This is more of a design issue. If I want to have two things BEFORE INSERT on City, I need to code both in the same trigger. This means adding functionality involves editing existing, tested, working code. It would be much nicer if two such triggers could play along.
A dirty workaround to problematic issues
There is a dirty workaround to some issues.
Take, for example, the rotating tables problem. Instead of the trigger executing the following query:
DELETE FROM logs WHERE time < DATE_ADD(NOW(), INTERVAL -7 DAY)
(as we’ve already noted was impossible), the triggers can write down the query as TEXT into some queries_to_run table. A cronjob can periodically check this table and execute whatever is in it, removing executed rows.
MySQL 5.1’s event scheduler can also be used for such statements which are invokable (like said DELETE).
Hi Shlomi!
“Triggers cannot act on the same table which activated them.”
Well -they can. Using the NEW and OLD pseudo records. But of course you meant: “you cannot use the triggered table in the FROM clause of a statement contained in the trigger”. Slightly different wording, big difference in effect.
“It can’t be hacked by calling on another table, then doing a circular trigger trick. ”
Actually, it can, just not in the way you think.
try this:
CREATE TABLE tgr_tab1 (
id int DEFAULT NULL,
name char(10) DEFAULT NULL
)
ENGINE=InnoDB;
CREATE TABLE tgr_tab2 (
id int DEFAULT NULL,
name char(10) DEFAULT NULL
)
ENGINE=FEDERATED
CONNECTION=’mysql://user:password@host:port/schema/tgr_tab1′;
CREATE TRIGGER air_tgr_tab1
AFTER INSERT
ON tgr_tab1
FOR EACH ROW
DELETE FROM tgr_tab2
WHERE id = new.id – 2
;
mysql> insert into tgr_tab1 values (1,’1′);
Query OK, 1 row affected (0.03 sec)
mysql> select * from tgr_tab1;
+——+——+
| id | name |
+——+——+
| 1 | 1 |
+——+——+
1 row in set (0.00 sec)
mysql> insert into tgr_tab1 values (2,’2′);
Query OK, 1 row affected (0.03 sec)
mysql> select * from tgr_tab1;
+——+——+
| id | name |
+——+——+
| 1 | 1 |
| 2 | 2 |
+——+——+
2 rows in set (0.00 sec)
mysql> insert into tgr_tab1 values (3,’3′);
Query OK, 1 row affected (0.05 sec)
mysql> select * from tgr_tab1;
+——+——+
| id | name |
+——+——+
| 2 | 2 |
| 3 | 3 |
+——+——+
2 rows in set (0.00 sec)
mysql> insert into tgr_tab1 values (4,’4′);
Query OK, 1 row affected (0.06 sec)
mysql> select * from tgr_tab1;
+——+——+
| id | name |
+——+——+
| 3 | 3 |
| 4 | 4 |
+——+——+
2 rows in set (0.00 sec)
(Don’t tell the MySQL devs, they’ll probably rip it out if they find out…. 😉
#2: Yikes!
I’m going to pretend I never saw this hack. As usual, you are full of surprises!
Thanks,
Shlomi