Strange issue with trigger.

[EM1107]

Good morning.

I have a strange situation here and I hope that someone can help me to find out why it is happening.

I have created a trigger as shown bellow;

CREATE OR REPLACE TRIGGER "TRG_RO_TEST"
BEFORE INSERT OR UPDATE OF "APPLICATION", "CLASSIFIED", "CREATION_DATE",
"LAST_ACCESS_TIME", "LAST_EDIT_DATE", "OBJ_DATE",
"PD_OBJ_TYPE", "READONLY_DATE", "RIMSOFFICIAL", "STATUS"
ON "PROFILE"
FOR EACH ROW
WHEN (NEW.APPLICATION = 113517)
BEGIN
:NEW.READONLY_DATE := ld.CREATION_DATE;
:NEW.STATUS := 19;
:NEW.OBJ_DATE := SYSDATE;
:NEW.RIMSOFFICIAL := 1;
:NEW.CLASSIFIED := 1;
END;

Now the problem I have with that is when I create the trigger in the database. It remove the line
(NEW.APPLICATION = 113517) and tell me that trigger was created with success. Why is it removing my WHEN condition?

 

[Dagon]

How do you know it is removing it ?

 

[Beilstwh]

Also you trigger will fail on an insert, an insert does NOT have any OLD values. Do the following instead. I am assuming that you want to store sysdate in readonly_date on an insert. If you want anything else, change the code.

CREATE OR REPLACE TRIGGER TRG_RO_TEST
BEFORE INSERT OR UPDATE OF APPLICATION, CLASSIFIED, CREATION_DATE,
LAST_ACCESS_TIME, LAST_EDIT_DATE, OBJ_DATE,
PD_OBJ_TYPE, READONLY_DATE, RIMSOFFICIAL, STATUS
ON PROFILE
FOR EACH ROW
WHEN (NEW.APPLICATION = 113517)
BEGIN
if INSERTING THEN
  :NEW.READONLY_DATE := SYSDATE;
ELSE
  :NEW.READONLY_DATE := :old.CREATION_DATE;
END IF;
    :NEW.STATUS := 19;
    :NEW.OBJ_DATE := SYSDATE;
    :NEW.RIMSOFFICIAL := 1;
    :NEW.CLASSIFIED := 1;
END;

Bill
Oracle DBA/Developer
New York State, USA

 

[EM1107]

Dagon it is easy to see if it is not there.

all I do is edit the trigger in the database. It show the trigger there but not the When condition.

 

[Beilstwh]

How did you edit it? What tool? Run the following select.


SELECT WHEN_CLAUSE
FROM ALL_TRIGGERS
WHERE TRIGGER_NAME='TRG_RO_TEST';

Bill
Oracle DBA/Developer
New York State, USA