ah, yes I'd missed the 'for each row' when I posted. But for the date math part, look at the column, it's an int() not a date. Puzzled me a little at the time so I tried it..
mysql> select now()-60*60*24*5 from dual; +-----------------------+ | now()-60*60*24*5 | +-----------------------+ | 20091103730524.000000 | +-----------------------+ 1 row in set (0.00 sec) Does give back a number at least rather than a date, so I assumed on the side of the poster that he was storing his value in last_seen as a number..(which remains to be seen :)) Not the way I would do it, but each to their own! Phil On Wed, Nov 4, 2009 at 5:40 PM, Gavin Towey <gto...@ffn.com> wrote: > 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=pchap...@nc.rr.com > > -- Distributed Computing stats http://stats.free-dc.org
