I am new on mysql and I am try to create a before delete trigger on mysql.
I have a table call "persons" and I need delete a record on another table called "ldap_entries"
the table "persons" have a ID, unique and this id it is on "ldap_entries" but the columns it is called "keyval".
so I am try to do like this: but not working
CREATE TRIGGER `before_delete`
BEFORE DELETE ON `persons`
FOR EACH ROW
BEGIN
DELETE FROM ldap_entries WHERE ldap_entries.keyval = OLD.id
END $$
Here is my describe output:
mysql> describe persons;
+------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | UNI | NULL | |
| surname | varchar(255) | NO | | NULL | |
| package | varchar(64) | NO | | NULL | |
| macaddress | varchar(21) | NO | | NULL | |
| customer | varchar(255) | NO | | NULL | |
| address | varchar(140) | NO | | NULL | |
| phone | varchar(9) | NO | | NULL | |
| email | varchar(50) | NO | | NULL | |
| created | date | NO | | NULL | |
| updated | timestamp | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------+
11 rows in set (0.00 sec)
mysql> describe ldap_entries
-> ;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| dn | varchar(255) | NO | UNI | NULL | |
| oc_map_id | int(10) unsigned | NO | MUL | NULL | |
| parent | int(11) | NO | | NULL | |
| keyval | int(11) | NO | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
Also in response to the comments:
- I don't know if InnoDB is being used.
- There isn't a FK relationship between the tables.
Best Answer
You need to change the delimter to
$$
and then back to;
: