I’m going to pretend I never saw this hack. As usual, you are full of surprises!

Thanks,
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…. 😉