Hi all,

Sorry if this seems like a rookie kind of question, but I tend to get
edgy when I have to do something that can put my neck at risk. :-)

I'm populating a table to control the replication of seven tables and
I would like to know if I am doing something that will end up kicking
me back in the privates. Initial tests seems ok, but with a production
database being heavily used (several thousand updates/inserts a day)
things can get in a wrong direction very quickly...

The scenario:
FB: 1.5.5.4926
OS: Windows xp sp3

Tables involved in replication: 7 (total tables in the DB 175)

These tables are master-detail...etc connected and use the same 3
columns for the primary keys that I use to control the replication per
se (I replicate all the 7 tables if necessary).

Each Table calls a Trigger, being essentially the same and updating
the same control table, only updating one of the operations columns
RPLATNUPD, RPLATNINS, RPLATNDEL (for Updates, Insert and deletes):

CREATE TRIGGER IAT_UPD_REPL FOR THE_TABLE
active before update position 0
as
declare variable totalregs integer;
begin
IF( USER <> 'REPLIC' ) THEN
BEGIN

select count(IDAT) from RPL_CONTROL
where idat = new.idat and
idfil = new.idfil and
ano = new.ano
into :totalregs;

if (totalregs > 0 ) then
begin
update RPL_CONTROL set RPLATNUPD = 1
where idat = new.idat and
idfil = new.idfil and
ano = new.ano;
end
else
begin
insert into RPL_CONTROL (IDAT,
IDFIL,
ANO,
RPLATNUPD)
values (new.idat,
new.idfil,
new.ano,
1);
end
end
end
^

This inserts a row if needed to the table rpl_at that has this DDL:

CREATE TABLE RPL_CONTROL (
IDAT INTEGER NOT NULL CHECK (value > 0),
IDFIL SMALLINT NOT NULL CHECK (value > 0),
ANO SMALLINT NOT NULL CHECK (value > 0),
RPLATNUPD SMALLINT DEFAULT 0,
RPLATNINS SMALLINT DEFAULT 0,
RPLATNDEL SMALLINT DEFAULT 0 );

ALTER TABLE RPL_CONTROL ADD CONSTRAINT PK_RPL_AT PRIMARY KEY (IDAT,
IDFIL, ANO);

Now, my questions:

1 - As all the tables are inserting/updating the same control table,
could the triggers end up deadlocking?

2- Would it be better to use a Stored Procedure to do this and use the
triggers to call the SP?

3- Should I do a separate control for each table?

Thanks for the input.
Andrew