Create trigger before insert

Question

I want to create a trigger in Mysql. Before Insert and before Update , to only insert the values if the ID is present in another table.

Here is my trigger (before insert) which does not work:

DELIMITER $$
CREATE TRIGGER 
    `before_insert_id`
BEFORE INSERT ON
    `table2`
FOR EACH ROW
    BEGIN
        DECLARE msg VARCHAR(255);
        IF NEW.id = 
            ( SELECT id
              FROM table2
              WHERE NEW.id not in (select id from table1)
            )
        THEN 
            SET msg = 'id not in table1';
            SIGNAL SQLSTATE '45002' SET message_text = msg ;
        END IF ;
    END ;

$$
DELIMITER ;

Also should we insert values in table2 inside after if statement passes?or is it just for checking only?

//to only insert the values if the ID is present in another table.// you mention you want to insert if ID is present in another table.. but using NOT IN — Ganesh Chandrasekaran
– Ganesh Chandrasekaran, Commented May 13, 2019 at 17:40
IF NOT EXISTS (select * from table1 where id = new.id) then set msg = 'id not in table1' signal... end if; If it exists then data gets inserted automatically. — Ganesh Chandrasekaran
– Ganesh Chandrasekaran, Commented May 13, 2019 at 17:41
Not sure how to mark the comment as a solution.If you can write it in the answer.I will do so. — Rgeek
– Rgeek, Commented May 13, 2019 at 18:15

Ganesh Chandrasekaran · Accepted Answer · 2019-05-13 18:33:37Z

2

IF NOT EXISTS (select * from table1 where id = new.id) then set msg = 'id not in table1' signal... end if; If it exists then data gets inserted automatically.

answered May 13, 2019 at 18:33

Ganesh Chandrasekaran

1,96614 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bill Karwin · Accepted Answer · 2019-05-13 17:40:23Z

0

... only insert the values if the ID is present in another table.

This sound like you just need a foreign key constraint, not a trigger.

ALTER TABLE table2 ADD FOREIGN KEY (id) REFERENCES table1(id);

That will throw an error if you try to insert a row with an id that is not present in table1.

No trigger required.

answered May 13, 2019 at 17:40

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

2 Comments

Rgeek Over a year ago

The values in the "id" column are not unique.For each id, there are multiple rows.I tried it now but not working.

Bill Karwin Over a year ago

InnoDB doesn't require the referenced column to be unique, but it must be the first column in some index. This is a little bit different from the traditional type of foreign key, but it might be useful in your case.

Collectives™ on Stack Overflow

Create trigger before insert

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related