List:General Discussion« Previous MessageNext Message »
From:Gavin Towey Date:November 4 2009 10:40pm
Subject:RE: trigger
View as plain text  
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.
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