Reputation: 5390
I have this relation (A):
ID | B_ID
This relation (B):
ID | FOO
I want to add a trigger to A
which will, on insertion of a new row into A
(with B_ID
always set as NULL
), add a row in B
with FOO
set as NULL
and set the B_ID
attribute in the new row of A
to reference the newly added B
row.
Example:
Right after insertion into
A
:
1 | NULL
After trigger action:
A
:
1 | 555
B
:
555 | NULL
Is this possible?
Upvotes: 0
Views: 42
Reputation: 16569
You can try something like the following:
/* Trigger structure for table `a` */
DELIMITER $$
CREATE TRIGGER `trg_a_bi` BEFORE INSERT ON `a`
FOR EACH ROW
BEGIN
INSERT INTO `b` (`foo`) VALUES (NULL);
SET NEW.`b_id` := LAST_INSERT_ID();
END$$
DELIMITER ;
Here a SQL Fiddle.
Upvotes: 1