Eric Mayfield Eric Mayfield - 18 days ago 5
MySQL Question

What is the correct MySQL syntax for a Trigger with a conditional?

Any help would be well received. :)

Scenario: I have a MySql table 'artworkjobs_proudction'. I'm trying to create a trigger in PHPMyAdmin to set a date once certain fields in the table have met specific conditions. Since it is multiple conditions it seems like an IF conditional is the way to go. Below is the trigger:

CREATE TRIGGER `createProductionScheduleDate`
AFTER UPDATE ON `artworkjobs_production`
FOR EACH ROW
IF ((OLD.ArtworkStatus = 'Approved' OR NEW.ArtworkStatus = 'Approved')
AND (OLD.isCompleted is NULL OR NEW.isCompleted is NULL)
AND (OLD.isCompleted = 'N' OR NEW.isCompleted = 'N')
AND (OLD.printDueDate <> '0000-00-00 00:00:00' OR NEW.printDueDate <> '0000-00-00 00:00:00')
AND (length(OLD.invoiceNumber) > 0) OR (length(NEW.invoiceNumber) > 0)
AND (OLD.importance > 0) OR NEW.importance > 0) AND (OLD.quantity > 0 OR NEW.quantity > 0)
THEN SET originalProductionDueDate = now()
END IF;


The error I'm receiving is:


One or more errors have occured while processing your request:
The following query has failed:
<SQL query as stated above>


MySQL said: #1193 - Unknown system variable
'originalProductionDueDate'



originalProductionDueDate
is a correct field however. :(

createProductionScheduleDate
is the name of the trigger, not of a table.
The table is
artworkjobs_production
. Which I defined here:

CREATE TRIGGER createProductionScheduleDate
AFTER UPDATE ON artworkjobs_production

Answer

If you want to change the value of a column in the row that spawned the trigger, use

SET NEW.originalProductionDueDate = now();

But you can't change NEW.<column> values in an AFTER trigger. The row has already been updated by then. You must do it in a BEFORE trigger.

Comments