Hello, I have 3 tables.
contact_coordinates
webquestions
and quotations
What I want the trigger to do is:
when the webquestion table has a row inserted where column value for 'quotation_requested' = yes, then the table 'quotations' should have a row added, where some columns are populated by what the trigger tells it to, namely the id of the webquestion and the lastname that comes from the contact_coordinates. The trigger formed without a problem but:
If I leave the "last_insert_id() there, it gives a foreign key contraint error, cannot add or update child row etc. If instead of that, I write the PK, that is, id_webquestion, then it does not give an error, but it adds me all of the existing PKs. What I want is to add the last one, so that each row of the Quotations table correlates to One row of the table Webquestions where request_quotation was 'yes'.
DELIMITER $$
CREATE TRIGGER request_quotation
AFTER INSERT ON webquestions
FOR EACH ROW
BEGIN
IF (new.quotation_requested = 'yes') THEN
INSERT INTO quotations (id_webquestion_fk, last_name, qquestion, offered_by, offer_is, made_at, quotations_left)
SELECT LAST_INSERT_ID(), lname, question, CURRENT_USER, 1200, SYSDATE(), 3
FROM webquestions wq
JOIN contact_coordinates cc
ON wq.patient_id_fk = cc.patient_id
JOIN quotations q
ON q.id_webquestion_fk = wq.id_webquestion
;
END IF;
END$$
Edited 3 time(s). Last edit at 06/18/2013 12:55AM by Jonathan O'Neal.