n881 February 2016

MySQL trigger to insert date to row with unique matching ID

I need to write an SQL trigger to insert the date into a row of another table after an insert.

The data comes in as such:

A form is submitted to _OnsiteTable which includes a JobID, this JobID matches a current entry containing a JobID value in _JobTable. Upon insert of the data into _OnsiteTable I need a trigger that will insert the current date into a column called JobClosedDate in JobTable.

It needs to insert the date into the row that matches the JobID value entered in the form on _OnsiteTable.

I have this so far but I don't know how to compare the JobID values between tables and insert into the specific row.

CREATE TRIGGER UpdateClosedDate
AFTER INSERT on _OnsiteTable

BEGIN

Declare ClosedDate DATETIME;
SET ClosedDate = CURDATE();

CASE WHEN (ClosedDate) = ?
    THEN INSERT INTO `_JobTable`( `JobClosedDate`)
    VALUES
    ( ClosedDate);  

END CASE;

END

Any help is greatly appreciated!

Complete query now working, thank you! As below:

CREATE TRIGGER UpdateClosedDate
AFTER INSERT on _OnsiteTable

BEGIN

Declare ClosedDate DATETIME;
SET ClosedDate = CURDATE();

UPDATE `_JobTable` SET `JobClosedDate` = ClosedDate WHERE `JobID` = new.JobID;

END

Answers


Jorge Campos February 2016

Since the problem was solved with the given comment I will add it here to reference for others with similar issues:

Since you want to Update a column ("insert a value into a column...") You just need to fix your trigger changing your current case statement to an update command as follow:

UPDATE `_JobTable` 
   SET `JobClosedDate` = ClosedDate 
 WHERE `JobID` = NEW.JobID;

Post Status

Asked in February 2016
Viewed 1,659 times
Voted 11
Answered 1 times

Search




Leave an answer