Re: trigger without trigger call - Mailing list pgsql-general

From ChoonSoo Park
Subject Re: trigger without trigger call
Date
Msg-id CACgbiFsNtYgtr1JFio8TvQ+UrRAcpvMHjAjzM6WReJiTNTGm-g@mail.gmail.com
Whole thread Raw
In response to Re: trigger without trigger call  (Philipp Kraus <philipp.kraus@tu-clausthal.de>)
List pgsql-general

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

Am 14.10.2013 um 11:49 schrieb Alban Hertroys <haramrae@gmail.com>:

> On Oct 14, 2013, at 8:18, Philipp Kraus <philipp.kraus@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

pgsql-general by date:

Previous
From: Steve Crawford
Date:
Subject: Re: Forms for entering data into postgresql
Next
From: Tomas Vondra
Date:
Subject: Re: werid error"index row size 3040 exceeds btree maximum, 2712" occur randomly