CODEWITHSUNDEEP
mysqlmysql-workbench
Rgeek
Rgeek

Reputation: 449

Create trigger before insert

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?

Upvotes: 0

Views: 847

Answers (2)

Ganesh Chandrasekaran
Ganesh Chandrasekaran

Reputation: 1936

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.

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562310

... 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.

Upvotes: 0

Related Questions