public inbox for [email protected]  

help / color / mirror / Atom feed

From: Chris Tsongas <[email protected]>
To: [email protected]
Subject: Writing my first trigger
Date: Thu, 19 May 2022 11:18:48 -0700
Message-ID: <CAPhBOiiW8jk_2wcKvCik1eXNejS10qwfGuuovPk0Q=5kvHNxNw@mail.gmail.com> (raw)

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();
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fullName
INSTEAD OF INSERT OR UPDATE ON employee
    FOR EACH ROW EXECUTE FUNCTION update_employee();

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: <CAPhBOiiW8jk_2wcKvCik1eXNejS10qwfGuuovPk0Q=5kvHNxNw@mail.gmail.com>

* 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