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'

  • Shouldn't parent row be inserted if not exists? – McNets Feb 10 '18 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? – Gerard H. Pille Feb 10 '18 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 '18 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 '18 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 '18 at 0:47
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;
  • 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 '18 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 ;
  • No! If someone else slips in, they can get your last_id. – Rick James Feb 18 '18 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 '18 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 '18 at 16:14
  • Is it possible to use something like this LAST_INSERT_ID()++? – Dzintars Feb 21 '18 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 '18 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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