I want to create relationships between parent and child when booth doesn't exist and when new child is created. This somehow can be done with Triggers but i don't know how to work with SQL.
After some googling i came up with Trigger concept bellow.
- Create child
- Create parent
- Get last parent id
- Update CURRENT child record with parent_id
Could somebody help me to achieve this?
DELIMITER $$
CREATE
TRIGGER `database_name`.`trigger_name` AFTER INSERT
ON `database_name`.`child_table`
FOR EACH ROW BEGIN
/*Step 1 - Create Parent record.*/
INSERT INTO `database_name`.`parent_table`(id) VALUES (``);
/*Step 2 - Get last inserted ID of just created Parent record
and assign to the variable*/
DECLARE last_id int;
SELECT LAST_INSERT_ID INTO last_id;
FROM information_schema.tables
WHERE table_name = `parent_table`
AND table_schema = `database_name`;
/*Step 3 - Update current record child_table.parent_id with last_id variable*/
UPDATE NEW.parent_id = last_id;
END$$
DELIMITER ;
Or something like this, but it has errors.
DELIMITER $$
DROP TRIGGER IF EXISTS create_new_child; $$
CREATE
TRIGGER create_new_child BEFORE INSERT
ON child
FOR EACH ROW BEGIN
DECLARE last_id INT;
SELECT AUTO_INCREMENT INTO last_id
FROM information_schema.TABLES
WHERE TABLE_NAME = parent
AND TABLE_SCHEMA = DATABASE();
INSERT INTO parent (title) VALUES (`TEST`);
SET NEW.parent_id = last_id;
END$$
DELIMITER ;
Unknown column 'parent' in 'where clause'
Best Answer
I think you have to perform this trigger before insert child row and there is a lot of errors in your code.