List:General Discussion« Previous MessageNext Message »
From:Phil Date:November 4 2009 11:28pm
Subject:Re: trigger
View as plain text  
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 <gtowey@stripped> 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.bok@stripped [mailto:freedc.bok@stripped] 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 <support@stripped> 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=1
> >
> >
>
>
> --
> 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=1
>
>


-- 
Distributed Computing stats
http://stats.free-dc.org

Thread
triggerStefan Onken4 Nov
  • Re: triggerMichael Dykman4 Nov
  • Re: triggerPhil4 Nov
    • RE: triggerGavin Towey4 Nov
      • Re: triggerStefan Onken5 Nov
  • RE: triggerGavin Towey4 Nov
    • Re: triggerPhil5 Nov