Обсуждение: before insert for each row trigger on upsert
I'm a little confused why the before insert trigger fires al all but since it does is there a way to know that an update will occur? Basically I don't want the trigger affect the row on update.
Ted
On 2/20/20 12:17 PM, Ted Toth wrote: > I'm a little confused why the before insert trigger fires al all but > since it does is there a way to know that an update will occur? Because ON CONFLICT DO UPDATE is part of an INSERT command. > Basically I don't want the trigger affect the row on update. Not sure that a BEFORE trigger will work for that anyway as it will not have submitted the data yet for ON CONFLICT resolution. > > Ted -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Feb 20, 2020 at 2:32 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 2/20/20 12:17 PM, Ted Toth wrote:
> I'm a little confused why the before insert trigger fires al all but
> since it does is there a way to know that an update will occur?
Because ON CONFLICT DO UPDATE is part of an INSERT command.
> Basically I don't want the trigger affect the row on update.
Not sure that a BEFORE trigger will work for that anyway as it will not
have submitted the data yet for ON CONFLICT resolution.
I think you are right in that the trigger doesn't change the row being updated (which is good) but it also updates another table and that's what I'd actually like to avoid on update.
>
> Ted
--
Adrian Klaver
adrian.klaver@aklaver.com
On 2/20/20 1:03 PM, Ted Toth wrote: > > > On Thu, Feb 20, 2020 at 2:32 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 2/20/20 12:17 PM, Ted Toth wrote: > > I'm a little confused why the before insert trigger fires al all but > > since it does is there a way to know that an update will occur? > > Because ON CONFLICT DO UPDATE is part of an INSERT command. > > > Basically I don't want the trigger affect the row on update. > > Not sure that a BEFORE trigger will work for that anyway as it will not > have submitted the data yet for ON CONFLICT resolution. > > > I think you are right in that the trigger doesn't change the row being > updated (which is good) but it also updates another table and that's > what I'd actually like to avoid on update. Hard to say what to do without knowing what the trigger function actually does? > > > > > > Ted > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
Ted Toth <txtoth@gmail.com> writes:
> I think you are right in that the trigger doesn't change the row being
> updated (which is good) but it also updates another table and that's what
> I'd actually like to avoid on update.
Probably need to put that in an AFTER trigger, then. By definition,
BEFORE triggers don't really know whether the insert is going to
happen. This doesn't even require use of upsert to cause a problem:
a later BEFORE trigger could prevent the insertion, or change what
gets inserted.
regards, tom lane