public inbox for [email protected]  

help / color / mirror / Atom feed

From: Bzzzz <[email protected]>
To: [email protected]
Subject: Re: Writing my first trigger
Date: Thu, 19 May 2022 21:07:16 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPhBOijuJPntrK0fcYFWO4fhnLccpvtMffbx_n0rOGu10qvxuQ@mail.gmail.com>
References: <CAPhBOiiW8jk_2wcKvCik1eXNejS10qwfGuuovPk0Q=5kvHNxNw@mail.gmail.com>
	<CAPhBOijuJPntrK0fcYFWO4fhnLccpvtMffbx_n0rOGu10qvxuQ@mail.gmail.com>

On Thu, 19 May 2022 11:24:41 -0700
Chris Tsongas <[email protected]> wrote:

> Upon looking at my own code, I realized there's no reason for me to be
> looking at the OLD values, I only care about the NEW:
>
> CREATE OR REPLACE FUNCTION update_employee() RETURNS TRIGGER AS $$
>   BEGIN
>     IF (NEW."preferredFirstName" IS NOT NULL) THEN
>       NEW."fullName" = NEW."preferredFirstName" || ' ' ||
> NEW."lastName"; ELSE
>       NEW."fullName" = NEW."firstName" || ' ' || NEW."lastName";
>     END IF;
>     NEW."updatedAt" = now();
>     RETURN NEW;
>   END;
> $$ LANGUAGE plpgsql;
>
> Any other feedback appreciated!
>
> On Thu, May 19, 2022 at 11:18 AM Chris Tsongas
> <[email protected]> wrote:
> >
> > Working on my first trigger to create a fullName value from
> > firstName, optional preferredFirstName, and lastName fields, where
> > the full name uses the optional preferred first name if it exists,
> > otherwise it uses the first name and of course the required last
> > name.
> >
> > Would be great to get feedback on the following code before I try
> > running it (note I already have an employee table, just including
> > the CREATE TABLE statement for clarity):
> >
> > CREATE TABLE employee (
> >   firstName           text NOT NULL,
> >   preferredFirstName  text,
> >   lastName            text NOT NULL,
> >   fullName            text,
> > );
> >
> > CREATE OR REPLACE FUNCTION update_employee() RETURNS TRIGGER AS $$
> >   BEGIN
> >     IF (OLD."preferredFirstName" IS NOT NULL) THEN
> >       NEW."fullName" = OLD."preferredFirstName" || ' ' ||
> > OLD."lastName"; ELSE
> >       NEW."fullName" = OLD."firstName" || ' ' || OLD."lastName";
> >     END IF;
> >     NEW."updatedAt" = now();

You may want to switch to : clock_timestamp() that record the time of
writing (now() stays stuck on the calling time)

> >     RETURN NEW;
> >   END;
> > $$ LANGUAGE plpgsql;
> >
> > CREATE TRIGGER fullName
> > INSTEAD OF INSERT OR UPDATE ON employee
> >     FOR EACH ROW EXECUTE FUNCTION update_employee();

May be something closer to :
CREATE TRIGGER triggername
BEFORE UPDATE ON myschema.mytable
FOR EACH ROW EXECUTE PROCEDURE mytriggerproc

Jean-Yves

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Writing my first trigger
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox