Oops, one more mistake: NOW()-60*60*24*5 isn't the way to do date math. It should be: NOW() - INTERVAL 5 DAY
-----Original Message----- From: Gavin Towey Sent: Wednesday, November 04, 2009 2:33 PM To: 'Phil'; Mysql; 'Stefan Onken' Subject: RE: trigger 1. Triggers must have FOR EACH ROW -- it's described in the manual: http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html So the correct syntax would be: CREATE TRIGGER greylist_ins AFTER INSERT on greylist FOR EACH ROW delete from greylist where first_seen < NOW()-60*60*24*5; BEGIN/END and DELIMITER are not needed for single statement triggers 2. However you still can't do that. You can't update the table used in the trigger. What you really want is either a separate cron process, or a mysql event (if using 5.1) Regards Gavin Towey -----Original Message----- From: freedc....@gmail.com [mailto:freedc....@gmail.com] On Behalf Of Phil Sent: Wednesday, November 04, 2009 11:42 AM To: Mysql Subject: Re: trigger You are missing a BEGIN in the trigger delimiter | CREATE TRIGGER greylist AFTER INSERT on greylist BEGIN delete from greylist where first_seen < NOW()-60*60*24*5; END; | delimiter ; Phil On Wed, Nov 4, 2009 at 2:28 PM, Stefan Onken <supp...@stonki.de> wrote: > Hello, > > I am new to using triggers in mysql. I am using mysql 5.1.37 and would > like to setup a trigger like: > > CREATE TRIGGER greylist AFTER INSERT on greylist > delete from greylist where first_seen < NOW()-60*60*24*5; > END; > > When typing this into mysql I am getting an error. Where is my mistake? > > > mysql> show fields from greylist; > +---------------+---------------+------+-----+---------+ > | Field | Type | Null | Key | Default | > +---------------+---------------+------+-----+---------+ > | id | int(11) | NO | PRI | NULL | > | SenderIP | varchar(15) | NO | MUL | NULL | > | SenderAddress | varchar(1024) | NO | MUL | NULL | > | first_seen | int(11) | NO | | NULL | > +---------------+---------------+------+-----+---------+ > 4 rows in set (0,00 sec) > > I would like to archive that after every insert in the greylist table I am > purging the oldest xx records. > > Stefan > > > > www.stonki.de : My, myself and I > www.kbarcode.net : barcode solution for KDE > www.krename.net : renamer for KDE > www.proftpd.de : a FTP server... > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=pchap...@nc.rr.com > > -- Distributed Computing stats http://stats.free-dc.org The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
