Re: Triggers and transactions

On 28/01/13 18:54, Craig James wrote:
> If I drop and then recreate a trigger inside of a single transaction,
> how does it affect other processes trying to use the same table?  Can
> they just merrily go along their way using the table, or will they be
> blocked by an exclusive lock?
>
I *think* it blocks, but in any case, read on...

> We have a trigger that detects illegal drugs and dangerous chemicals
> (such as explosives and flammable compounds that can't be shipped by air).

<pedantry mode="full">detects a reference to illegal... (unless you've
hooked your RDBMS up to some sort of x-ray scanner, in which case I
salute you sir)</pedantry>

> Unfortunately, the trigger's function is necessarily "heavyweight" and
> slow.
>
> The drop-and-restore-trigger operation is needed when we're copying
> data one server to another.

Run the copy as a different user than ordinary applications (a good idea
anyway). Then the function can just check current_user and exit for the
copy.

--
   Richard Huxton
   Archonet Ltd