CODEWITHSUNDEEP
mysqltriggers
Bullfinch
Bullfinch

Reputation: 255

Mysql trigger on insert, if row not exist

My trigger will not work.

Basically when a user_briefcase is created i want to also create a row in user_complementary_info if it not already exist, where user_complementay.user_id = the newly created user_briefcase.user_id

My trigger

DROP TRIGGER IF EXISTS complementary_user_info
DELIMITER $$
CREATE TRIGGER complementary_user_info AFTER INSERT user_briefcases FOR EACH ROW
BEGIN
IF (SELECT COUNT(*) user_complementary WHERE user_id=NEW.user_briefcases.user_id) = 0 THEN
    INSERT INTO user_complementary (user_id)
    VALUES (NEW.user_briefcases.user_id)

END $$
DELIMITER ;

Upvotes: 6

Views: 12044

Answers (1)

fancyPants
fancyPants

Reputation: 51868

DROP TRIGGER IF EXISTS complementary_user_info;
DELIMITER $$
CREATE TRIGGER complementary_user_info AFTER INSERT user_briefcases FOR EACH ROW
BEGIN
IF NOT EXISTS (SELECT 1 FROM user_complementary_info WHERE user_id = NEW.user_id) THEN
    INSERT INTO user_complementary (user_id)
    VALUES (NEW.user_briefcases.user_id);
END IF;
END $$
DELIMITER ;

Upvotes: 9

Related Questions