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