Re: Writing my first trigger

On Thu, 19 May 2022 11:24:41 -0700
Chris Tsongas <chris(dot)tsongas(at)gmail(dot)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(dot)tsongas(at)gmail(dot)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