Groups | Search | Server Info | Login | Register

Groups > comp.databases.ingres > #3893

Triggers and locks

Show all headers | View raw

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

Back to comp.databases.ingres | Previous | Next — Next in thread | Find similar

Thread

Triggers and locks Fabrizio Di Renzo <fabrizio.direnzo@bucap.it> - 2023-09-12 02:40 -0700
  Re: Triggers and locks Roy Hann <specially@processed.almost.meat> - 2023-09-12 10:36 +0000
    Re: Triggers and locks G Jones <geraint.jones@ndph.ox.ac.uk> - 2023-09-12 07:41 -0700
  Re: Triggers and locks Paul White <shift7solutions@gmail.com> - 2023-09-12 22:03 -0700
    Re: Triggers and locks Fabrizio Di Renzo <fabrizio.direnzo@bucap.it> - 2023-09-12 23:53 -0700
      Re: Triggers and locks Roy Hann <specially@processed.almost.meat> - 2023-09-13 08:54 +0000

csiph-web