0

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.

  1. Create child
  2. Create parent
  3. Get last parent id
  4. 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'

5
  • Shouldn't parent row be inserted if not exists?
    – McNets
    Feb 10, 2018 at 14:42
  • Can you shed some light on the problem you're trying to solve here? What's the use of having a parent without a single known fact, apart from having a child record? Feb 10, 2018 at 17:50
  • Hi. Parent SHOULD be created when CHILD is inserted. After that i need to get PARENT_ID and to SET in my CHILD.PARENT_ID. IDs = PK with auto-increment. PARENT_ID = FK to PARENT.ID and NULL is allowed. So i can insert CHILD records without PARENT_ID and update it after.
    – Dzintars
    Feb 11, 2018 at 12:55
  • Parents does not exist at moment of creation child. There is 1:1 relation. Parents currently are used just as internal middleman but they are important in future development. So i cant have child records without a parent.
    – Dzintars
    Feb 11, 2018 at 13:08
  • A newbie should not work with Triggers. Instead, build a transaction: BEGIN; 2 inserts in the right order; COMMIT;
    – Rick James
    Feb 18, 2018 at 0:47

2 Answers 2

0

I think you have to perform this trigger before insert child row and there is a lot of errors in your code.

  1. You must declare variables in the beginning of the script.
  2. You give values to variables with SET
  3. Only LAST_INSERT_ID() function is necessary and if you perform the trigger before update, you only have to set NEW value, and that's it.

DELIMITER $$
DROP TRIGGER `database_name `.`trigger_name` $$
CREATE
    TRIGGER `database_name `.`trigger_name` BEFORE INSERT
    ON `database_name `.`child_table`
    FOR EACH ROW BEGIN
    DECLARE last_id int;
    INSERT INTO  `database_name`.`parent_table`(id) VALUES (0);

    SET NEW.parent_id = LAST_INSERT_ID();
END $$
DELIMITER;
1
  • Hi, Yes, i am not programmer. Trying to google this stuff so sorry for mistakes. Your example didn't worked for me.
    – Dzintars
    Feb 11, 2018 at 12:43
-1

Finally got it working!

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 ;
5
  • No! If someone else slips in, they can get your last_id.
    – Rick James
    Feb 18, 2018 at 0:44
  • What do You mean? This is a BEFORE INSERT trigger. Execution is in some NS. You mean that there is practical chance that 2 different transactions can get the same last_id? Then i will have some kind of error because of constraints.
    – Dzintars
    Feb 20, 2018 at 15:16
  • information_schema is probably not transactionally locked such that the id can be safely grabbed. It would be much better to somehow use the approved last_insert_id() to get the value.
    – Rick James
    Feb 20, 2018 at 16:14
  • Is it possible to use something like this LAST_INSERT_ID()++?
    – Dzintars
    Feb 21, 2018 at 11:06
  • There is no ++ operator. This expression should work: LAST_INSERT_ID() + 1. But NOT in this context. That 'function' only has a value after an insert involving AUTO_INCREMENT -- which is not your case.
    – Rick James
    Feb 21, 2018 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.