Re: trigger without trigger call

On Mon, Oct 14, 2013 at 6:02 AM, Philipp Kraus <
philipp(dot)kraus(at)tu-clausthal(dot)de> wrote:

>
> Am 14.10.2013 um 11:49 schrieb Alban Hertroys <haramrae(at)gmail(dot)com>:
>
> > On Oct 14, 2013, at 8:18, Philipp Kraus <philipp(dot)kraus(at)tu-clausthal(dot)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