CODEWITHSUNDEEP
postgresqltriggers
GeoGyro
GeoGyro

Reputation: 559

PostgreSQL - Trigger on INSERT or UPDATE

I wan't to add a trigger in a PostgreSQL database. This trigger is used to concatenate values of 2 columns to update a 3rd one. I wan't to run it when a row is inserted or updated in the table.

Table

CREATE TABLE IF NOT EXISTS FILE(ID INT NOT NULL PRIMARY KEY, FOLDER TEXT, NAME TEXT, URL TEXT);

Function

CREATE OR REPLACE FUNCTION LINK() RETURNS trigger AS 
$$
BEGIN
  IF (TG_OP = 'UPDATE') THEN
    UPDATE FILE
    SET URL = CONCAT(FOLDER, NAME)
    WHERE ID = OLD.ID;
  ELSIF (TG_OP = 'INSERT') THEN
    UPDATE FILE   
    SET URL = CONCAT(FOLDER, NAME)
    WHERE ID = NEW.ID;
  END IF;
  RETURN NULL;
END
$$
LANGUAGE PLPGSQL;

Trigger

CREATE TRIGGER TRIGGER_LINK
BEFORE INSERT OR UPDATE 
ON FILE
FOR EACH ROW 
EXECUTE PROCEDURE LINK();

When I insert a value in table like

INSERT INTO FILE VALUES (1, 'C:\', 'doc.pdf');

I have an error message list index out of range because ID number is not yet created and UPDATE query on INSERT can't execute. But if I make an AFTER UPDATE it will run infinitely.

How to run a trigger function on INSERT or UPDATE with a WHERE clause on ID to target only inserted or updated row ? I'm using PostgreSQL 10.14.

Upvotes: 1

Views: 3016

Answers (1)

user330315
user330315

Reputation:

Don't use UPDATE to do that. Just assign the value. Also, a BEFORE trigger should not return null because that will abort the operation.

CREATE OR REPLACE FUNCTION LINK() RETURNS trigger AS 
$$
BEGIN
  new.url := CONCAT(new.FOLDER, new.NAME);
  RETURN new; --<< important!
END
$$
LANGUAGE PLPGSQL;

Upvotes: 5

Related Questions