MySQL Forums
Forum List  »  Triggers

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

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Trigger jammed
2093
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.