Sort of triggers on SELECT

Triggers are something that you should use with caution. If abused, they could bring your application to its knees. However, they are also a wonderful tool to fine tune your control on a difficult situation.

As for SQL standards, triggers can be set on modifying statements only (INSERT, DELETE, UPDATE).
What about SELECTs, though? Would it be nice if you could have a trigger for each SELECT statement?
Let's suppose that you have a table books and you want to create a customized access log to that table only. Here is how you can do it:

1. Create a table db_counter which will hold the number of accesses, and a table db_log that will get the access details;
2. Create a function containing all the statements you want to execute at each SELECT. Such function should always return a positive integer;
   
3. Create a updatable view for your table, adding a WHERE clause testing your function value for positiveness.
   

Let's go about it:

-- ----------------------------------------------------
-- our original table. The one for which we want a log
-- We'll make it simple for example's sake
-- ----------------------------------------------------
CREATE TABLE books (
book_id INT NOT NULL primary key,
title   VARCHAR(40),
pages   INT,
price   DECIMAL(5,2),
author_id INT NOT NULL,
KEY (author_id),
-- FOREIGN KEY (author_id) REFERENCES authors (author_id) ON DELETE CASCADE
) ENGINE = InnoDB;

-- ----------------------------------------------------
-- The counter.
-- It will hold one row only, with a running counter
-- ----------------------------------------------------
CREATE TABLE db_counter (
id int(11) NOT NULL default '0',
counter bigint(20) default '0',
PRIMARY KEY  (id)
) ENGINE=MyISAM;

-- ----------------------------------------------------
-- The detail log.
-- each row will have info about who accessed the table
-- and when.
-- ----------------------------------------------------
CREATE TABLE db_log (
ts timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
user varchar(20) NOT NULL,
operation enum('select','insert','update','delete') default 'select',
KEY user_ts (user,ts),
KEY ts (ts)
) ENGINE=MyISAM;

These last two tables will hold the access information we need.
Before we go on, some sintactic sugar to ensure taht our counter will have just one row:

CREATE TRIGGER counter_insert before INSERT on db_counter
for each row set new.id = 1;

CREATE TRIGGER counter_update before UPDATE on db_counter
for each row set new.id = 1;

And a few triggers to record the DML for books (it would be silly to record all
reads and to neglect the writes):

CREATE TRIGGER book_insert after INSERT on books
for each row set @BOOKCOUNT = get_counter('insert');

CREATE TRIGGER book_delete after DELETE on books
for each row set @BOOKCOUNT = get_counter('delete');

CREATE TRIGGER book_update after UPDATE on books
for each row set @BOOKCOUNT = get_counter('update');

We are ready to create our function.

delimiter //

drop function if exists get_counter //

create function get_counter( oper varchar(10) )
returns int
deterministic
begin
if ( ( select count(*) from db_counter ) > 0 ) then
update db_counter set counter = counter + 1;
else
insert into db_counter (counter) values (1);
end if;
insert into db_log (ts, user, operation) values (NULL, user(), oper);
return (select counter from db_counter where id = 1);
end //

delimiter ;

Finally, we create an updatable view for table books.

CREATE VIEW mybooks as SELECT * from books WHERE get_counter() > 0;

An that's it. Now using
mybooks instead of books every access to books will be recorded.

Let's see our machine in action (notice I am using always the view, not the table):

gmdesk [localhost] {gmax} (test1) > insert into mybooks values (1, 'MySQL', 1000,39.95,1);
Query OK, 1 row affected (0.02 sec)

gmdesk [localhost] {gmax} (test1) > insert into mybooks values (2, 'Programming Perl', 1095,39.95,2);
Query OK, 1 row affected (0.02 sec)

gmdesk [localhost] {gmax} (test1) > select * from db_counter;
+----+---------+
| id | counter |
+----+---------+
|  1 |       2 |
+----+---------+
1 row in set (0.00 sec)

gmdesk [localhost] {gmax} (test1) > select * from db_log;
+---------------------+----------------+-----------+
| ts                  | user           | operation |
+---------------------+----------------+-----------+
| 2005-10-24 11:14:22 | gmax@localhost | insert    |
| 2005-10-24 11:14:27 | gmax@localhost | insert    |
+---------------------+----------------+-----------+
2 rows in set (0.00 sec)

After two insertion statements, the counter is 2, and the log holds two records.
Let's try our main goal, i.e. a select:

gmdesk [localhost] {gmax} (test1) > select count(*) from mybooks;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

gmdesk [localhost] {gmax} (test1) > select * from db_log;
+---------------------+----------------+-----------+
| ts                  | user           | operation |
+---------------------+----------------+-----------+
| 2005-10-24 11:17:11 | gmax@localhost | insert    |
| 2005-10-24 11:17:23 | gmax@localhost | insert    |
| 2005-10-24 11:18:08 | gmax@localhost | select    |
+---------------------+----------------+-----------+
3 rows in set (0.01 sec)

gmdesk [localhost] {gmax} (test1) > select * from db_counter;
+----+---------+
| id | counter |
+----+---------+
|  1 |       3 |
+----+---------+
1 row in set (0.00 sec)

QED