Create a trigger that finds string value and copies ID to insert to new table-CodePudding

I am trying to write an MYSQL statement that will copy the patient_id where they have a value of "Dose: Second" and paste it to my table vaccinatedpatients.

Also, I wanted to implement this within a trigger to automatically update itself after each new record on the table.

Any help with this will be great!

CREATE DEFINER=`root`@`localhost` TRIGGER `patient_AFTER_INSERT` AFTER INSERT ON `patient` FOR EACH ROW BEGIN


SELECT @variable = patient_id from patient where vaccine_Dose = 'Dose: Second';
INSERT INTO vaccinatedpatients VALUES(variable);

END

CodePudding user response：

You can use an IF cLause, to determines if the patient gets the second dose

DELIMITER &&
CREATE DEFINER=`root`@`localhost` TRIGGER `patient_AFTER_INSERT` AFTER INSERT ON `patient` FOR EACH ROW 
BEGIN
IF NEW.vaccine_Dose = 'Dose: Second' THEN
    INSERT INTO vaccinatedpatients  VALUES (NEW.patinet_id);
END IF;
END&&
DELIMITER ;

The DELIMITER may not be needed.