sql - Reduce the value of a vehicle every month -

sql - Reduce the value of a vehicle every month -

what trying cut down value of every vehicle in 'vehicle' table when date reaches 1 month past 'lastupdate' value 'value' column reduced 2.5%. trigger should run after logon on database. problem is, dba may not logon database every day, if has been 6 months since lastly logon, trigger loop through, cut down value 2.5% , add_months(lastupdate, 1).

my code function calculate value is:

reate or replace function fn_vehicle_value (vehiclenumber in number, vehiclevalue in out number) homecoming numeric begin vehiclevalue := vehiclevalue - (vehiclevalue * 0.025); homecoming vehiclevalue; end; /

this effort @ creating scheme trigger:

create or replace trigger tg_vehicledepreciate after logon on database cursor vehicle_cur select "value", lastupdate vehicle; begin vehicle_rec in vehicle_cur loop while lastupdate < sysdate loop lastupdate."value" := fn_vehicle_update("value"); update vehicle set lastupdate := add_months(lastupdate, 1) vehicle# = vehicle_cur.vehicle#; end loop; exit when vehicle_cur%notfound; end loop end; /

from can tell, function right. trigger not compile , produces next error report:

error study - ora-04079: invalid trigger specification 04079. 00000 - "invalid trigger specification" *cause: create trigger statement invalid. *action: check statement right syntax.

i guessing syntax error, there may logical error cannot work out.

show errors after compiling trigger reveal errors.

here few.. 1) in update, set somecolumn = somevalue 2) oracle implicitly opens cursor , closes to, when used loop. 3) after using loop,the fetched results should used like.. forloopvariable.column-name 4) added vehicle# cursor. 5) added v_date logic.

create or replace trigger tg_vehicledepreciate after logon on database cursor vehicle_cur select "value", lastupdate ,vehicle# vehicle; v_date date; begin vehicle_rec in vehicle_cur loop v_date := vehicle_rec.lastupdate; while v_date < sysdate loop /* trying here? */ ---vehicle_rec."value" := fn_vehicle_update("value"); update vehicle set lastupdate = add_months(lastupdate, 1) vehicle# = vehicle_rec.vehicle#; v_date := add_months( v_date,1); end loop; end loop end; /

sql oracle function plsql triggers