What's wrong with my trigger

[jennypsion]

Hello,
I have just written a trigger, which changes a field in my table after a certain date was reached.

This is my trigger, including the error message:

mysql> create trigger closeAuction before insert on auction for each row begin update auction set closed = 'true' where current_date() > deadline end;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update auction set closed = 'true' where current_date() > deadline end' at line 1

I can't see what's wrong with my trigger.
Thanks for your help,
Jen

 

[jennypsion]

I have found the error, but now i get a new error.
closed is a field in table auction, so i don't understand why it can't find it.

mysql> CREATE TRIGGER updateAuction
-> BEFORE UPDATE ON auction
-> FOR EACH ROW
-> SET closed = 'true' WHERE current_date() > deadline;
ERROR 1193 (HY000): Unknown system variable 'closed'

 

[johnwm]

Looks like you're missing the UPDATE statement. Have a look at

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

which suggests you may need

mysql> CREATE TRIGGER updateAuction
-> BEFORE UPDATE ON auction
-> FOR EACH ROW BEGIN
UPDATE auction SET closed = 'true' WHERE current_date() > deadline;
END

If your field `closed` is Boolean then you shouldn't use primes to delimit it as it's a mySQL constant

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[towerbase]

I have never used triggers but I would have thought that the last line should be a valid SQL statement. Something like:

...
[b]UPDATE [i]tablename[/i][/b] SET closed='true' WHERE current_date() > deadline;

Andrew
Hampshire, UK

 

[swampBoogie]

if new.deadline > current_date then
   set new.closed = true;
end if;