Re: Disabling triggers in a transaction

Net Virtual Mailing Lists wrote:
> All I did was added an extra column to my table (I called it
> "batch_process"). Then in
> the trigger do something like (in whichever function you are calling):
>
> IF NEW.batch_update IS NOT NULL AND NEW.batch_update = ''t'' THEN
> NEW.batch_process := NULL;
> RETURN NULL;
> END IF;
> .. whatever the rest of transaction is

Why don't you just set it to false instead of NULL? Wouldn't that reduce
the condition to just "IF NEW.batch_update THEN ..."? In that case you
should default the column to false of course, or the condition will
always fail (the value being NULL).
Personally, I would use a more descriptive name for the column,
'disable_triggers' or something like that.

Also, I find it more convenient to use "true" and "false" instead of
having to escape "'t'" and "'f'" all the time ;)

> Then when doing an insert, just:
>
> INSERT INTO table (..., batch_process) VALUES (..., 't') when you want the
> trigger not to fire...
>
> Or an update:
>
> UPDATE TABLE table SET ...., batch_process = 't' ....
>
>
> I'm not sure sure how to make it work on a function called from a delete
> trigger though.. ;-(

The drawbacks of this method are that you'll have to modify all your
queries when you want to disable triggers (though that can usually be
solved programatically), and that only the triggers that "support" this
method of disabling will be actually disabled.

If you work at the same project with multiple people who all write
triggers from time to time, or when you have to deal with legacy code
from an older database, I think you'll run into trouble with the above
quicker than you'd like. However, if you manage to get this into the
design fase of a project it'll probably work just fine (the delete
problem you mentioned aside...).

--
Alban Hertroys
MAG Productions

T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban(at)magproductions(dot)nl
W: http://www.magproductions.nl