CODEWITHSUNDEEP
oracle-databasetriggersoracle-sqldeveloper
Apuranic
Apuranic

Reputation: 39

IF-ELSE statement inside trigger in oracle

Here are the columns in my table, TB_KELUHAN 

"IDKELUHAN" NUMBER(20,0) NOT NULL ENABLE, 
"NAMA" VARCHAR2(10 BYTE), 
"IDUNIT" NUMBER(10,0), 
"TGL_KELUHAN" DATE DEFAULT sysdate, 
"KELUHAN" VARCHAR2(200 BYTE), 
"STATUS" VARCHAR2(10 BYTE), 
"IDPEGAWAI" NUMBER(10,0), 
"TGL_SELESAI" DATE DEFAULT sysdate, 
"ID_JENISKELUHAN" NUMBER(5,0), 
 CONSTRAINT "TB_KELUHAN_PK" PRIMARY KEY ("IDKELUHAN")

I want a trigger that will update a row's TGL_SELESAI column to SYSDATE, when a row's STATUS becomes 'SELESAI'. Here is the text of the trigger I've tried:

TRIGGER SELESAI
AFTER UPDATE OF STATUS ON TB_KELUHAN
FOR EACH ROW
DECLARE 
   TGL_SELESAI DATE;
BEGIN
   IF :new.STATUS = 'SELESAI'
   THEN 
      TGL_SELESAI:=SYSDATE;
   END IF;
END;

When I change the value of STATUS to "SELESAI", the corresponding TGL_SELESAI did not change. Why?

Upvotes: 0

Views: 3436

Answers (1)

Jeff Holt
Jeff Holt

Reputation: 3190

Your original code was setting a local PL/SQL variable, not the row's column and you were creating an AFTER UPDATE trigger, not a BEFORE UPDATE trigger. Try this:

CREATE OR REPLACE TRIGGER SELESAI
BEFORE UPDATE OF STATUS ON TB_KELUHAN
FOR EACH ROW
BEGIN
   IF :new.STATUS = 'SELESAI'
   THEN 
      :new.TGL_SELESAI := SYSDATE;
   END IF;
END;

Upvotes: 1

Related Questions