MySQL Forums
Forum List  »  Triggers

Trigger jammed
Posted by: Jonathan O'Neal
Date: June 17, 2013 02:49PM

Hello, I have 3 tables.

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'.

CREATE TRIGGER request_quotation
    AFTER INSERT ON webquestions 
    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;

Edited 3 time(s). Last edit at 06/18/2013 12:55AM by Jonathan O'Neal.

Options: ReplyQuote

Written By
Trigger jammed
June 17, 2013 02:49PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.