UPDATE TRIGGER?

Question

I am trying to execute e trigger, it executes but it doesn't do what i want.

ok these are tables two tables that i need for it

create table Properties(  
        idProperties number(20) NOT NULL,
        Typee varchar2(20) NOT NULL,
        SquareMeters varchar2(20) NOT NULL,
        Rooms number(20) NOT NULL,
        ConstructionDate date NOT NULL,
        FloorLocation varchar(20),
        Price number(20) NOT NULL,
        CityView varchar2(20),
        DateOfInsert date NOT NULL,
        DateOfExiration date NOT NULL,
        Address_FK number(20),
        Service_FK number(20),
        OwnerAgent_FK number(20),
        Status_FK number(20),
        PropertyService_FK number(20))

create table Status(
         idStatus number(20) NOT NULL,
         statustype varchar2(20))

this is the trigger

CREATE OR REPLACE TRIGGER Property_Update 

AFTER UPDATE ON Properties REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 


BEGIN
DECLARE
val_new VARCHAR2(20);
val_app VARCHAR2(20);
BEGIN
    select idstatus into val_new from status where STATUSTYPE='New';
    select idstatus into val_app from status where STATUSTYPE='Approved';        

IF :OLD.status_fk=val_new AND :NEW.status_fk=val_app THEN
   UPDATE Properties SET DateOfExiration=(sysdate+90) WHERE  
idProperties= :NEW.idProperties;


END IF; 


END;
END;

I want to update sysdate + 90 when statustype is changed from 'new' to 'approved'

I am updating it

update properties set status_fk = 2 where idproperties = 12;

it changes the the forieng key status_fk on table properties but it doesnt update the date of expiration to sysdate + 90?

any idea why this is happening?

Answer 1

You need to do this in a BEFORE UPDATE trigger, and the code you need is:

IF :OLD.status_fk=val_new AND :NEW.status_fk=val_app THEN
   :NEW.DateOfExiration  := (sysdate+90);
END IF;

i.e. you assign the value in the trigger, you don't do another UPDATE.

It has to be a BEFORE trigger because you cannot modify values in an AFTER trigger.