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.
>

Yeah, that's one improvement I meant to make but just haven't gotten
around to it.. It is just the way this thing got written the first time
during and testing and the "oh! It worked!" realization.. ;-)

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

Yeah.. ;-)

>> 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.
>

It seems like you would have to do something programatically anyways in
order to say "Okay, now I want to disable the triggers -- go do something"....

>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...).
>

I think any of the solutions I've seen mentioned so far would present the
same problem..... :-(

>--
>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
>