0

I have this function built using PostgreSQL:

CREATE OR REPLACE FUNCTION "db"."t_pencabutan_likuidasi_after_update"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
BEGIN
    IF(NEW.status <> OLD.status) THEN
        INSERT INTO 
             t_pencabutan_likuidasi_log(id_pencabutan, id_profile, nama_petugas, nip_petugas, status, catatan)
        VALUES(
             NEW.id_pencabutan, NEW.id_profile, NEW.nama_petugas, NEW.nip_petugas, NEW.status, NEW.catatan);
    END IF;
        RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100

Above is a function that will triggered BEFORE UPDATE of the table liquidator.

The problem is some of the fill on the INSERT were not mandatory inserted, like nama_petugas and nip_petugas which is a temporary value. Sometimes it is inserted, but sometimes it isn't. It gives an error like this:

ERROR:  record "new" has no field "nama_petugas"

How could I leave it blank when there is no value/variable that is inserted for nama_petugas and nip_petugas?

Here is my trigger function;

CREATE TRIGGER 
"t_pencabutan_likuidasi_after_update" 
AFTER UPDATE OF "status" 
ON "db"."liquidator"
FOR EACH ROW
EXECUTE PROCEDURE 
"db"."t_pencabutan_likuidasi_after_update"();
1
  • 1
    The error message indicates that the table for which the trigger is defined has no column with the name "nama_petugas". Please edit your question and add the create table for the table in question. And create trigger statement might be helpful as well Commented Mar 26, 2019 at 20:15

1 Answer 1

1

The variable NEW.nama_petugas exists if and only if the table on which the trigger is defined has a column of that name.

You cannot use this trigger with different tables if some of them don't have a column of that name.

Either write several trigger functions or ose a trick like in this answer to use dynamic SQL.

Sign up to request clarification or add additional context in comments.

4 Comments

yes, the updated table liquidator` had nama_petugas column, but when i tried to update the liquidator table for certain value only, i didnt include nama_petugas column on that UPDATE query for table liquidator...
okay i have edited my question, so i have to do like this ? IF NEW.nama_petugas = "" THEN nama_petugas = '' ? is it right ?
eh, sorry, there is no column nama_petugas in table liquidator
Right, and that is the problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.