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@xxxxxxxxxxxxxxxxx W: http://www.magproductions.nl
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
