Trigger su SELECT

I trigger sono aggeggi da usare con cautela. Se abusati, possono portare la vostra applicazione in ginocchio. Tuttavia, sono anche fantastici strumenti per raffinare il controllo in situazioni difficili. Secondo lo standard SQL, i trigger possono essere associati solo alle operazioni di modifica (INSERT, DELETE, UPDATE). E i SELECT, allora? Non sarebbe bello se si potesse instaurare un trigger anche per ogni SELECT?

Supponiamo di avere una tabella books e che vogliamo creare una lista personalizzata degli accessi solo per quella tabella. Ecco come si può fare:

1. Create una tabella db_counter che conterrà il numero degli accessi, e una tabella db_log che conterrà i dettagli;
2. Create una funzione che contenga tutte le operazioni che volete eseguire per ogni SELECT. Questa funzione deve restituire un intero positivo;
3. Create una vista aggiornabile sulla vostra tabella, aggiungendo una clausola WHERE che usa la funzione appena creata.

Andiamo a incominciare:


-- ----------------------------------------------------
-- La tabella originale. Quella per cui vogliamo il log
-- La facciamo semplice per non appesantire l'esempio
-- ----------------------------------------------------
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;

-- ----------------------------------------------------
-- Il contatore
-- Contiene solo una riga.
-- ----------------------------------------------------
CREATE TABLE db_counter (
id int(11) NOT NULL default '0',
counter bigint(20) default '0',
PRIMARY KEY (id)
) ENGINE=MyISAM;

-- ----------------------------------------------------
-- L'elenco di dettaglio.
-- Ogni riga contiene informazioni sull'orario ,
-- sull'operazione fatta e l'utente.
-- ----------------------------------------------------
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;

Queste ultime due tabelle contengono le informazioni che ci servono. Prima di continuare, qualche ritocco per essere sicuri che la tabella del contatore possa contenere solo una riga:


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;


E ora alcuni trigger per registrare le operazioni di modifica nella tabella books. (Sarebbe buffo che registriamo gli accessi in lettura e trascuriamo quelli in scrittura!)


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');


Siamo pronti per creare la funzione:


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 ;


Infine, creiamo la vista aggiornabile per la tabella books.


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


Ora, usando mybooks invece di books, ogni accesso a books verrà registrato. Vediamo in azione il nostro meccanismo (notate che usiamo sempre la vista, non la tabella).


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)


Dopo due inserimenti, il contatore è a due, e la tabella dei dettagli ha due record. Proviamo con il nostro obiettivo principale, cioè una 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)


CVD