Using the now() function and executing triggers

Question

I am trying to create a trigger function in PostgreSQL that should check records with the same id (i.e. comparison by id with existing records) before inserting or updating the records. If the function finds records that have the same id, then that entry is set to be the time_dead. Let me explain with this example:

INSERT INTO persons (id, time_create, time_dead, name)
VALUES (1, 'now();', ' ', 'james');

I want to have a table like this:

 id  time_create  time-dead  name
 1   06:12                   henry   
 2   07:12                   muka

id 1 had a time_create 06.12 but the time_dead was NULL. This is the same as id 2 but next time I try to run the insert query with same id but different names I should get a table like this:

 id  time_create  time-dead  name
 1   06:12        14:35      henry   
 2   07:12                   muka
 1   14:35                   waks

henry and waks share the same id 1. After running an insert query henry's time_dead is equal to waks' time_create. If another entry was to made with id 1, lets say for james, the time entry for james will be equal to the time_dead for waks. And so on.

So far my function looks like this. But it's not working:

CREATE FUNCTION tr_function() RETURNS trigger AS '
BEGIN
  IF tg_op = ''UPDATE'' THEN
     UPDATE persons
     SET time_dead = NEW.time_create
     Where
         id = NEW.id
         AND time_dead IS NULL
         ;

  END IF;
  RETURN new;
END
' LANGUAGE plpgsql;

CREATE TRIGGER sofgr BEFORE INSERT OR UPDATE
        ON persons FOR each ROW
        EXECUTE PROCEDURE tr_function();

When I run this its say time_dead is not supposed to be null. Is there a way I can write a trigger function that will automatically enter the time upon inserting or updating but give me results like the above tables when I run a select query?

What am I doing wrong?

My two tables:

CREATE TABLE temporary_object
(
  id integer NOT NULL,
  time_create timestamp without time zone NOT NULL,
  time_dead timestamp without time zone,
  PRIMARY KEY (id, time_create)
);

CREATE TABLE persons
(
  name text
)
INHERITS (temporary_object);

The first problem is you can't update a table inside a trigger that fires on update of this same table. That would cause an infinite recursion. — Daniel Vérité, Dec 07 '12 at 15:47
@DanielVérité: Of course you *can* do that. It's your responsibility, though, to prevent infinite recursion. I added a clause to prevent that .. — Erwin Brandstetter, Dec 07 '12 at 20:08
I cleaned up the question and fixed contradictions in your setup to make the example work. — Erwin Brandstetter, Dec 07 '12 at 20:23

Erwin Brandstetter · Accepted Answer · 2023-04-15T00:50:48.167

Trigger function

CREATE FUNCTION tr_function()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   UPDATE persons p
   SET    time_dead = NEW.time_create
   WHERE  p.id = NEW.id
   AND    p.name <> NEW.name
   AND    p.time_dead IS NULL;

   RETURN NEW;
END
$func$;

You were missing the INSERT case in your trigger function (IF tg_op = ''UPDATE''). But there is no need for checking TG_OP to begin with, since the trigger only fires on INSERT OR UPDATE - assuming you don't use the same function in other triggers. So I removed the cruft.

You don't have to escape single quotes inside a dollar-quoted string.

Also added:

AND    p.name <> NEW.name

... to prevent INSERT's from terminating themselves instantly (and causing an infinite recursion). This assumes that a row can never succeed another row with the same name.

Aside: The setup is still not bullet-proof. UPDATEs could mess with your system. I could keep updating the id or a row, thereby terminating other rows but not leaving a successor. Consider disallowing updates on id. Of course, that would make the trigger ON UPDATE pointless. I doubt you need that to begin with.

`DEFAULT now()`

If you want to use now() as default for time_create just make it so. Read the manual about setting a column DEFAULT. Then skip time_create in INSERTs and it is filled automatically.

If you want to force it (prevent everyone from entering a different value) create a trigger ON INSERT or add the following at the top of your trigger:

IF TG_OP = 'INSERT' THEN
    NEW.time_create := now();   -- type timestamp or timestamptz!
    RETURN NEW;
END IF;

That forces the current timestamp for new rows unconditionally.
(Assuming your column "time_create" is actually type timestamp.)

my insert query is not runing if leave one timestamp value null — theuserkaps, Dec 07 '12 at 21:29
@theuserkaps: You need to be way more specific than that. Edit your question if it's more than a simple comment. — Erwin Brandstetter, Dec 07 '12 at 21:33
@theuserkaps: That's short for `persons AS p` and `p` is a table alias. Look for "alias" [in the manual here](http://www.postgresql.org/docs/current/interactive/sql-select.html). — Erwin Brandstetter, Dec 08 '12 at 19:34
ooh ya,one other thing i have been trying to modify the function above such that when i run insert query i should not put time_create value as now but when i just insert the values the time_create should always be automatically use the function now(); inside the function,is there a way i can do it??thanks — theuserkaps, Dec 10 '12 at 05:57
thanks you have really been helpful im very greatful i have learned alot from you thanks — theuserkaps, Dec 10 '12 at 13:57
sorry for troubling you but i think the query u added is not doing the what the previous one did its just putting the time automatically — theuserkaps, Dec 10 '12 at 17:44

Using the now() function and executing triggers

1 Answers1

Trigger function

`DEFAULT now()`

Linked