Re: How can I create a trigger that increments on read?
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.