MYSQL Trigger after insert

Question

I am writing a trigger to run whenever a new row is inserted into table_a. The trigger adds a new row into table_b, see insert statement.

CREATE TRIGGER `trigger` AFTER INSERT ON `table_a`
 FOR EACH ROW BEGIN     
    INSERT INTO table_b (added, email) VALUES (NOW(), NEW.email);
END

The above works fine but I want to make a slight change to the trigger.
I would like to prevent the insert statment from running if:

table_b.email = NEW.email AND submitted = 0

Basically I want to check first to see if the email exists in table_b AND submitted has the value 0. I only want to insert if this condition is false. I am struggling to see how this is done within a trigger.

Ergest Basha · Accepted Answer · 2025-09-22 14:51:04Z

1

You could use IF ( (SELECT .... ) = 0 ) THEN ,

CREATE TRIGGER `trigger` AFTER INSERT ON `table_a` 
 FOR EACH ROW
  BEGIN
   IF (( SELECT COUNT(*) 
         FROM table_b 
         WHERE email = NEW.email 
         AND submitted = 0
        ) = 0 -- OR you could use < 1 instead of = 0 
       ) 
    THEN INSERT INTO table_b (added, email) VALUES (NOW(), NEW.email);
    END IF;
END;

See example

answered Sep 22 at 14:51

Ergest Basha

9,3064 gold badges12 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

MYSQL Trigger after insert

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related