MySQL Forums
Forum List  »  Triggers

Re: How can I create a trigger that increments on read?
Posted by: Roland Bouman
Date: August 19, 2005 02:55AM

What do you want to achieve exactly. I mean, what's the purpose of incrementing the value? I ask, because you can NOT create a trigger for SELECT.

There is a little trick that does seem to achieve what you describe. First, you should create a table and insert a row.

CREATE TABLE onerow (
num int unsigned NOT NULL default 0
) ENGINE=InnoDB;

insert into onerow values (0);

Now, you must ensure ordinary users can't insert, update or delete from that table or else everthing will fall apart.

Next, create this function.

delimiter $$
create function inc_onerow()
returns int unsigned
DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
begin
update onerow
set num = num + 1
;
return 1;
end;
$$

delimiter ;

and this view:

create or replace view v_onerow
as
select (select inc_onerow() + num - 1
from onerow
) as num
;

and let your users do:

select num from v_onerow
;


Now, each time they select from the view, num is incremented by one. You shoulnt have to lock anything yourself because the updates are occurring transaction safe (can anyone verify that this does indeed work as far as transactions are concerned?)

The reason for writing the view like this is to ensure that inc_onerow() is always called when the users selects from the view. When you do:

create or replace view v_onerow
as
select inc_onerow(), num
from onerow

it does not always work as expected. In particular, you would get different results for repeating this

select * from v_onerow

and this

select num from v_onerow

In the former case, inc_onerow is invoked, in the latter case, the invocation is optimized away.

A word of warning...I would not at all be surprised if this little trick was not intended by the MySQL developers. It could cease to work in a newer version (BTW im running this on 5.0.10)

Good luck, and let me know if it worked for you.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.