Reputation: 559
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
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