How to write a trigger for a table based on the value of a column in another table?
I have a table "A" with a column "flag" as below:
candidate_id | name | flag ----------------------------- 0001 | ABC | f | 0002 | DEF | t | 0003 | GHI | t | and a table "B" with a column "status" as below:
candidate_id | status ----------------------------- 0001 | Applied | 0002 | Applied | 0003 | Applied | I want to write a trigger with the following logic:
- When an entry happens in table "B", I want to change the "status" value to ‘Rejected’ only if the last inserted "candidate_id" in table "B", which refers "candidate_id" in table "A", has a "flag=t".
I’ve written a trigger as follows, but I’m not able to find the syntax in MySql to fetch the value of "flag" for a particular candidate_id:
CREATE TRIGGER update_status_to_rejected Before INSERT ON db.B FOR EACH ROW BEGIN DECLARE candidate_id INTEGER; candidate_id = NEW.candidate_id; // Im stuck from here with the MySQL syntax get the last_inserted candidate_id, check in table A whether this candidate_id flag=='t' then: set NEW.status = 'Rejected' Can you please guide me here? I’ve searched many solutions but not able to find when another table needs to be checked for a value. I would also want to know how to insert a new row with status=’Rejected’, instead of updating the last inserted row. Thank you in advance.
Here’s a solution I tested:
CREATE TRIGGER update_status_to_rejected Before INSERT ON B FOR EACH ROW BEGIN DECLARE _flag CHAR(1); SELECT flag INTO _flag FROM A WHERE candidate_id = NEW.candidate_id; IF _flag = 't' THEN SET NEW.status = 'Rejected'; ELSE SET NEW.status = 'Applied'; END IF; END Test:
mysql> insert into B set candidate_id = '0001'; mysql> insert into B set candidate_id = '0002'; mysql> insert into B set candidate_id = '0003'; Result:
mysql> select * from B; +--------------+----------+ | candidate_id | status | +--------------+----------+ | 0001 | Applied | | 0002 | Rejected | | 0003 | Rejected | +--------------+----------+ 
