CODEWITHSUNDEEP
mysqltriggers
batman
batman

Reputation: 5390

How to create a trigger that adds a new row to another table and a reference to the new row added to present table?

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

Answers (1)

wchiquito
wchiquito

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

Related Questions