public inbox for [email protected]  

help / color / mirror / Atom feed

From: Andreas Kretschmer <[email protected]>
To: [email protected]
Subject: Re: Writing my first trigger
Date: Fri, 20 May 2022 17:06:11 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPhBOiiW8jk_2wcKvCik1eXNejS10qwfGuuovPk0Q=5kvHNxNw@mail.gmail.com>
References: <CAPhBOiiW8jk_2wcKvCik1eXNejS10qwfGuuovPk0Q=5kvHNxNw@mail.gmail.com>



Am 19.05.22 um 20:18 schrieb Chris Tsongas:
> 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();
>

your table doesn't contain the field "updatedA". I would suggest to 
calculate the fullName at select-time and not via TRIGGER.

Andreas

-- 
2ndQuadrant, an EDB company
www.2ndQuadrant.com / www.enterprisedb.com

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