CODEWITHSUNDEEP
postgresqltriggersplpgsql
Edy
Edy

Reputation: 79

Create Trigger to update a row on same table when insert or update happens using postgresql

I am new to plpgsql and trying the below scenario. It would be helpful if someone could help on this issue.

I have a table named emp_table and whenever an record is inserted, I called a trigger to update a column record_status with 'U' and when insert operation is happened on table, I need to update the record-status column to 'I'

Table :

CREATE TABLE emp_data (
name text,
age integer,
designation text,
salary integer,
last_login TIMESTAMP,
record_status varchar
);

CREATE OR REPLACE FUNCTION em_batch_update()
  RETURNS trigger 
  LANGUAGE PLPGSQL
  AS 
$$
BEGIN

          IF (TG_OP = 'UPDATE') THEN
            UPDATE emp_data SET record_status = 'U' WHERE record_status is not null; 
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            UPDATE emp_data SET record_status = 'I' WHERE record_status is NULL;
            RETURN NEW;
        end if;
END;
$$

CREATE TRIGGER em_sem_batch
BEFORE INSERT OR UPDATE ON emp_data
FOR EACH ROW
EXECUTE PROCEDURE em_batch_update();

I have inserted below new record, but the record_status is not getting updated.

insert into emp_data(name,age,designation,salary,last_login) values ('test1234',3,'test1143',1332224,current_timestamp);

I get below error when I either update or insert,

PL/pgSQL function em_batch_update1() line 5 at SQL statement SQL statement "UPDATE emp_data SET record_status = 'U' WHERE record_status is not null"
PL/pgSQL function em_batch_update1() line 5 at SQL statement SQL statement "UPDATE emp_data SET record_status = 'I' WHERE record_status is NULL"
PL/pgSQL function em_batch_update1() line 8 at SQL statement SQL state: 54001

can someone help with this

Upvotes: 1

Views: 1488

Answers (1)

user330315
user330315

Reputation:

No need for an UPDATE. In a row-level BEFORE trigger you can simply assign the values:

CREATE OR REPLACE FUNCTION em_batch_update()
  RETURNS trigger 
  LANGUAGE PLPGSQL
  AS 
$$
BEGIN
  IF (TG_OP = 'UPDATE') THEN
    new.record_status := 'U'; 
  ELSIF (TG_OP = 'INSERT') THEN
    new.record_status := 'I';
  end if;
  RETURN NEW;
END;
$$

Or even simpler:

CREATE OR REPLACE FUNCTION em_batch_update()
  RETURNS trigger 
  LANGUAGE PLPGSQL
  AS 
$$
BEGIN
  new.record_status := left(TG_OP, 1); 
  RETURN NEW;
END;
$$

Upvotes: 3

Related Questions