1

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

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
Edy
  • 79
  • 5
  • You didn't want this to work anyway for the following reasons: 1) The `INSERT` and `UPDATE` actions are not constrained to a particular record, they will `UPDATE` all records that match the `WHERE`. 2) The `INSERT` portion will not do what you want as the record you want to `UPDATE` does not exist yet. The error you are getting is because of 1), you are getting a circular loop where the `UPDATE` drives another `UPDATE` which drives another `UPDATE` and so on. – Adrian Klaver Dec 13 '20 at 17:06

1 Answers1

3

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;
$$