mysql trigger after insert with 3 tables

Question

I have 3 tables: the first one gets all records to the t1 table, the second one is where my whitelist entries are, and the third one is for making some decision in the future regarding to t2. I need to create after insert trigger an update via my third table. the steps :

some value come into the tables called "t1"
need to check if the value is in "t2"
if the value is in t2 so go to t3 and do something, if the value is not in t2 so go to t3 and do something else.
but the value will always need to enter t1.

This is my code and it does not work, I don't know how to apply it I've tried many options already.

CREATE TRIGGER check_whitelist AFTER INSERT ON t1
FOR EACH ROW
SELECT
IF(IFNULL(new.name=(
    select name From t2 where (t2.name = new.name),
    UPDATE t3 SET option1 =0 ,
    UPDATE t3 SET option2 =1
)))

Please help me with writing this code.

Barmar · Accepted Answer · 2018-11-19 10:09:20Z

1

You can't put an UPDATE query inside a SELECT query. Use an IF() expression in the value that you're assigning to option1.

FOR EACH ROW
UPDATE t3 SET option1 = IF(new.name = (SELECT name FROM t2 WHERE t2.plate = new.name),
                           0, 1)

answered Nov 19, 2018 at 10:09

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

1 Comment

Madhur Bhaiya Over a year ago

@a.artur Please see: How to accept an answer for closure. You get points for it as well. Thanks :)

Collectives™ on Stack Overflow

mysql trigger after insert with 3 tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related