Eric Mayfield Eric Mayfield - 1 year ago 61
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`
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()

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

is a correct field however. :(

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

CREATE TRIGGER createProductionScheduleDate
AFTER UPDATE ON artworkjobs_production

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download