[Info-ingres] Triggers and locks

[Fabrizio Di Renzo]

Hi all,
we have over 2000 tables for which we want to register all insert, update and delete operations.
For this reason we have created rules that insert a row in a table that is used to store the operation performed.

These are the rules

create rule spider_insert_tab_01 AFTER INSERT ON tab_01 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 1);
create rule spider_insert_tab_01 AFTER DELETE ON tab_01 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 2);
create rule spider_insert_tab_01 AFTER UPDATE ON tab_01 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 3);

create rule spider_insert_tab_02 AFTER INSERT ON tab_02 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 1);
create rule spider_insert_tab_02 AFTER DELETE ON tab_02 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 2);
create rule spider_insert_tab_02 AFTER UPDATE ON tab_02 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 3);

This is the procedure

create procedure  prd_spider_update (
   cd_cliente VARCHAR(5) NOT NULL, 
   cd_doc VARCHAR(3) NOT NULL, 
   orario VARCHAR(2) NOT NULL, 
   nm_faldone INTEGER NOT NULL,
   nm_pratica INTEGER NOT NULL,
   gr_pratica INTEGER NOT NULL,
   tp_operazione INTEGER NOT NULL 
) AS 
begin
	 INSERT INTO spider (cd_cliente, cd_doc ,nm_faldone, nm_pratica, gr_pratica, tp_operazione, tm_archiviaz) 
	   SELECT :cd_cliente, :cd_doc, :nm_faldone, :nm_pratica, :gr_pratica, :tp_operazione, CURRENT_TIMESTAMP()
end

In this way, cuncurrency is generated on the table spider which causes locks on the database.
There is a way to force a row-level lock on the table spider?

Fabrizio