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?

As an aside, having to select from status every time to get the ID for 'new' etc. is use of surrogates gone mad! — Tony Andrews
– Tony Andrews, Commented Jan 16, 2012 at 17:06
You can remove your outer begin ... end combo. There's no need for it. — Ben
– Ben, Commented Jan 16, 2012 at 17:19

Tony Andrews · Accepted Answer · 2012-01-16 23:14:36Z

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.

Collectives™ on Stack Overflow

UPDATE TRIGGER?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related