I want to create a trigger to concatenate my column with event "before insert", but the query didn't work and i got an error:
SQL error:
ERROR: syntax error at or near "SET" LINE 4: SET new.fullname = CONCAT(new.first_name, '', new.mid_name, ...
In statement:
CREATE TRIGGER insert_trigger
BEFORE INSERT ON t_employees
FOR EACH ROW
SET new.fullname = CONCAT(new.first_name, '', new.mid_name, '', new.last_name);
Here's a working solution:
CREATE TEMPORARY TABLE t_employees (
first_name TEXT,
mid_name TEXT,
last_name TEXT,
fullname TEXT
);
CREATE OR REPLACE FUNCTION set_fullname()
RETURNS TRIGGER AS $$
BEGIN
NEW.fullname = NEW.first_name || ' ' || NEW.mid_name || ' ' || NEW.last_name;
RETURN NEW;
END;
$$ language 'plpgsql';
CREATE TRIGGER set_fullname_trigger
BEFORE INSERT OR UPDATE ON t_employees
FOR EACH ROW
EXECUTE PROCEDURE set_fullname();
SET client_min_messages TO 'debug';
INSERT INTO t_employees VALUES ('fname1', 'mname1', 'lname1');
SELECT * FROM t_employees;
UPDATE t_employees SET first_name = 'updated-first-name';
SELECT * FROM t_employees;
Well, i finally did it. According your suggestions and postgresql docs i've made the trigger like what i want. Here is the syntax:
create the function:
CREATE FUNCTION insert_funct() RETURN TRIGGER AS
$$
BEGIN
SELECT new.fullname := CONCAT(new.first_name, '', new.mid_name, '', new.last_name);
END;
$$
LANGUAGE plpgsql;
then create the trigger:
CREATE TRIGGER insert_trigger
BEFORE INSERT ON t_employees
FOR EACH ROW
EXECUTE PROCEDURE insert_funct();
update at all. A single line with new.fullname := concat(...); is all you need. See the examples in the manual: postgresql.org/docs/current/static/…
create trigger, but this is just used to execute a procedure (which is really a function). Your syntax looks more like MySQL.