Re: Writing my first trigger

On Thu, 19 May 2022 11:24:41 -0700
Chris Tsongas <chris.tsongas@gmail.com> 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
> <chris.tsongas@gmail.com> 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