Subject RE: [ib-support] HELP ! Adding a foreign key SLOWS down query
Author Helen Borrie
At 10:01 AM 08-08-02 -0500, you wrote:
>Thanks for the solution and explanation - I tried the option of using
>triggers to achieve the linking between the
>two tables, and heres a NEW problem :
>The referencing tables, traf_org, traf_ter, etc can be created or dropped at
>user request, so
>I have to add and drop the triggers on the config table bscmap when I
>create/drop the referencing
>The problem is, when I am dropping a table - I first have to drop the
>trigger for that table.
>After I drop the trigger, when I try to drop the table, I get an error
>saying the object <table_name> is
>in use !!
>Why cant I delete a table right after I delete the trigger referencing the
>table ?

Oh, several reasons that all boil down to - the system won't drop any
object that is involved in a transaction or has a dependency relationship
with an object that is involved in a transaction. It will defer the
metadata change until all possible dependencies are out of use. If your
multi-user system has a tendency to accumulate long-running or unresolved
transaction, this could mean quite a long time before metadata changes take

I guess I feel bound to beat the drum again on the subject of deleting and
recreating metadata items in transaction-governed database whilst the
database is under active operation. Please don't. Please (please, please)
find a way to disable the rows you would wish to have out of the way, but
keep the tables and their triggers intact. You can purge the unwanted rows
out periodically. Restrict metadata changes to SYSDBA and to times and
conditions where exclusive access can be obtained (via gfix -shut)

Please would you trim your messages and avoid unnecessary requoting...


All for Open and Open for All
Firebird Open SQL Database · ·