Monday, October 24, 2005

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

1 comment:

rpbouman said...

Hi Guiseppe, Nice One!

I know it's just coincidence. I thought of this little trick too in response to a question on one of the mysql forums. So, in case you like to check it out:

http://rpbouman.blogspot.com/2005/08/mysql-create-dirty-little-tricker-for.html

(yep, this is a link to a mysql blog, not the home furniture and dating services wich seem to be attracted to my blog)