On Mon, Oct 14, 2013 at 6:02 AM, Philipp Kraus < philipp.kr...@tu-clausthal.de> wrote:
> > Am 14.10.2013 um 11:49 schrieb Alban Hertroys <haram...@gmail.com>: > > > On Oct 14, 2013, at 8:18, Philipp Kraus <philipp.kr...@tu-clausthal.de> > wrote: > > > >> Hello, > >> > >> I have written a update & delete trigger of a table. > >> My delete trigger runs an update statement but this create a (semantic) > problem. > >> How can I disable the update trigger for only this update call within > the delete trigger? > >> So my delete trigger need not call the update trigger > > > > > > You obviously don't want to disable the trigger entirely for all > sessions, as other sessions may be updating records as well and those > should trigger the update trigger normally. I think Pavel's suggestion, if > executed from your delete trigger function, would disable the trigger for > that session only, so that would work. Don't forget to re-enable it again > after the update finishes. > > > > Another solution is to make your update trigger smarter about when it > needs to do its thing. > > For example, you could add a "fake" boolean column to the table and set > that to one value when updated from your delete trigger, while you normally > leave it at the other value (easiest by means of a column DEFAULT value). > In your update trigger you can then test for the value of that column and > escape out of the trigger function (RETURN NEW, for example). > > > > Nowadays you can put triggers on views even, in which case you could do > the above on a view over the table, omitting the need to actually store the > fake column value. > > I have got a plsql function like that: > > begin > > if (TG_OP = 'DELETE') then > update simulation.source set parent=old.parent where parent= > old.id; * > return old; > > elseif (TG_OP = 'UPDATE') then > > do something > > end if; > > end > > > This function is called on the before delete & update call, so the * call > creates a problem. On this call the > elseif (TG_OP = 'UPDATE') then need not run at any time. I think the > boolean field can be helpful, but > is there another idea to disable on the * call the update trigger? > > Thanks > > Phil > > What about using UPDATE OF? CREATE TRIGGER test_simulation_trigger BEFORE DELETE OR UPDATE OF C1,C2,C3 on simulation FOR EACH ROW EXECUTE PROCEDURE trigger_function(); You can include all columns to be used for update trigger and omit parent column. -Luis
