Hi,

I have a table used to store frequently updated datas. Only the last
data available must replace the other. This means that every time I add
some data, I must check if the date column is lower than the date of the
data's date i'm inserting.

Right now, I have a SELECT to get the date and a REPLACE to insert
data. But I must try to keep the lowest number of queries. So I would
like to do a CREATE TRIGGER which applies to "REPLACE INTO" queries.

I have no idea on how to do that, because creating TRIGGER seems to
apply on INSERT or DELETE but not directly REPLACE. But if there is a
INSERT query, that means that a DELETE one has already been made. But it
should have been made if the date to insert is lower than the date
already stored. Or is it managed in a transactionnal way (if we cancel
the INSERT, the DELETE is also cancelled).

But even if the knowledge of that, I have no id on how to say
"check if the date of the row that will be replaced is not newer".

Thank you for your answers

Florent

On Feb 20, 2:28 pm, Florent Clairambault
<Florent_ReMoVe_Th...@clairambault.fr> wrote:
> Hi,
>
> I have a table used to store frequently updated datas. Only the last
> data available must replace the other. This means that every time I add
> some data, I must check if the date column is lower than the date of the
> data's date i'm inserting.
>
> Right now, I have a SELECT to get the date and a REPLACE to insert
> data. But I must try to keep the lowest number of queries. So I would
> like to do a CREATE TRIGGER which applies to "REPLACE INTO" queries.
>
> I have no idea on how to do that, because creating TRIGGER seems to
> apply on INSERT or DELETE but not directly REPLACE. But if there is a
> INSERT query, that means that a DELETE one has already been made. But it
> should have been made if the date to insert is lower than the date
> already stored. Or is it managed in a transactionnal way (if we cancel
> the INSERT, the DELETE is also cancelled).
>
> But even if the knowledge of that, I have no id on how to say
> "check if the date of the row that will be replaced is not newer".
>
> Thank you for your answers
>
> Florent

I'm not at all sure of what it is you're trying to do. Perhaps an
example or two might illuminate your situation better?

However, it should be noted that if you run the queries independently,
or if the trigger runs the queries for you, you're not saving any
time. Triggers generally don't buy you performance, but rather enforce
the business rules at the database level.

E.g., if you need to do a SELECT then test for a condition then INSERT
if true, it won't matter whether you do it in the application or the
trigger does it. There's still the time used by SELECT, test, then
INSERT.

\:|

> I'm not at all sure of what it is you're trying to do. Perhaps an
> example or two might illuminate your situation better?

I have a table with :
key(uint PK), data(TEXT), date(DATETIME)

I want that every time I replace some data, by inserting (key, data)
with a REPLACE INTO it checks if the row that will be replaced has
an older date.
If it's not the case, I want him to cancel the transaction.

I'm sure it will be faster because the server which insert data isn't
really fast will have to make this check nearly every second in
production stage.

Florent

>
> However, it should be noted that if you run the queries independently,
> or if the trigger runs the queries for you, you're not saving any
> time. Triggers generally don't buy you performance, but rather enforce
> the business rules at the database level.
>
> E.g., if you need to do a SELECT then test for a condition then INSERT
> if true, it won't matter whether you do it in the application or the
> trigger does it. There's still the time used by SELECT, test, then
> INSERT.
>
> \:|

Florent Clairambault wrote:
>> I'm not at all sure of what it is you're trying to do. Perhaps an
>> example or two might illuminate your situation better?
>
> I have a table with :
> key(uint PK), data(TEXT), date(DATETIME)
>
> I want that every time I replace some data, by inserting (key, data)
> with a REPLACE INTO it checks if the row that will be replaced has
> an older date.
> If it's not the case, I want him to cancel the transaction.
>
> I'm sure it will be faster because the server which insert data isn't
> really fast will have to make this check nearly every second in
> production stage.
>
> Florent
>
>>
>> However, it should be noted that if you run the queries independently,
>> or if the trigger runs the queries for you, you're not saving any
>> time. Triggers generally don't buy you performance, but rather enforce
>> the business rules at the database level.
>>
>> E.g., if you need to do a SELECT then test for a condition then INSERT
>> if true, it won't matter whether you do it in the application or the
>> trigger does it. There's still the time used by SELECT, test, then
>> INSERT.
>>
>> \:|

Example:
mysql> create table b (a1 integer,a2 integer, primary key (a1));
Query OK, 0 rows affected (0.81 sec)

mysql> insert into b values (1,2),(2,2);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0


mysql> select * from b;
+----+------+
| a1 | a2 |
+----+------+
| 1 | 2 |
| 2 | 2 |
+----+------+
2 rows in set (0.00 sec)

mysql> insert into b (a1, a2) values(1,2) on duplicate key
-> update b.a2 = IF(VALUES(a2) < b.a2,b.a2,VALUES(a2));
Query OK, 2 rows affected (0.00 sec)

IF new value is less than old value use old value else use new value


mysql> select * from b;
+----+------+
| a1 | a2 |
+----+------+
| 1 | 2 |
| 2 | 2 |
+----+------+
2 rows in set (0.00 sec)

mysql> insert into b (a1, a2) values(1,3) on duplicate key
-> update b.a2 = IF(VALUES(a2) < b.a2,b.a2,VALUES(a2));
Query OK, 2 rows affected (0.00 sec)

mysql> select * from b;
+----+------+
| a1 | a2 |
+----+------+
| 1 | 3 |
| 2 | 2 |
+----+------+
2 rows in set (0.01 sec)