trigger

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=gcdmg-mysql-2@m.gmane.org

Re: trigger

Well you might need to parenthesize the expression, but note that
simple integer math done on a DATE or DATETIME, the units are days.
you probably want

... where first_seen < (NOW() - 5);

michael dykman

On Wed, Nov 4, 2009 at 2:28 PM, Stefan Onken wrote:
> Hello,
>
> I am new to using triggers in mysql. I am using mysql 5.1.37 =A0and 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 =A0 =A0 =A0 =A0 | Type =A0 =A0 =A0 =A0 =A0| Null | Key | Default =
|
> +---------------+---------------+------+-----+---------+
> | id =A0 =A0 =A0 =A0 =A0 =A0| int(11) =A0 =A0 =A0 | NO =A0 | PRI | NULL =
=A0 =A0|
> | SenderIP =A0 =A0 =A0| varchar(15) =A0 | NO =A0 | MUL | NULL =A0 =A0|
> | SenderAddress | varchar(1024) | NO =A0 | MUL | NULL =A0 =A0|
> | first_seen =A0 =A0| int(11) =A0 =A0 =A0 | NO =A0 | =A0 =A0 | NULL =A0 =
=A0|
> +---------------+---------------+------+-----+---------+
> 4 rows in set (0,00 sec)
>
> I would like to archive that after every insert in the greylist table I a=
m
> 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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
- michael dykman
- mdykman@gmail.com

"May you live every day of your life."
Jonathan Swift

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: trigger

--0016e6dab0562cf51c047790cecd
Content-Type: text/plain; charset=ISO-8859-1

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 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=pchapman@nc.rr.com
>
>


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

--0016e6dab0562cf51c047790cecd--

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 fr=
om 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 mys=
ql event (if using 5.1)

Regards
Gavin Towey


-----Original Message-----
From: freedc.bok@gmail.com [mailto:freedc.bok@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 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 a=
m
> 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=3Dpchapman@nc.rr.co=
m
>
>


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

The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e 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=3Dgcdmg-mysql-2@m.gmane.o rg

RE: trigger

Oops, one more mistake:

NOW()-60*60*24*5 isn't the way to do date math. It should be: NOW() - INTE=
RVAL 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 fr=
om 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 mys=
ql event (if using 5.1)

Regards
Gavin Towey


-----Original Message-----
From: freedc.bok@gmail.com [mailto:freedc.bok@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 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 a=
m
> 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=3Dpchapman@nc.rr.co=
m
>
>


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

The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: trigger

--0016e6dab54d1e727b047793f799
Content-Type: text/plain; charset=ISO-8859-1

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 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@gmail.com [mailto:freedc.bok@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 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=pchapman@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=pchapman@nc.rr.com
>
>


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

--0016e6dab54d1e727b047793f799--

Re: trigger

Hello Gavin,

> 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)

thanks for pointing that out. I was not aware of this limitation. I am
using mysql 5.1.37 (shipped with Ubuntu Karmic), so I will have a look
at the "event" feature..

Thanks to you and the other users who helped me out.

Stonki

--
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=gcdmg-mysql-2@m.gmane.org