|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
April 4th, 2004, 04:40 AM
|
|||
|
|||
date arithmetics
i want to create a trigger for when software is uninstalled within 30 days, a refund is given to the owner.
i have the tables... installation(packid, tagnum, instdate, instcost) machine(tagnum, deptno) pc(tagnum, empnum) depart(deptno, balance) employee(empnum, balance) each machine is owned and can be found in a depart. if machine.deptno is null, then machine is assumed to reside at home and belongs to the employee. i want the trigger to update the balance of the corresponding depart or employee by the full instcost if software is uninstalled in 30 days. i've tried the following, but it keeps compiling with errors. i don't know what i'm doing wrong, can someone please help! perhaps there's a simpler way to do this... create or replace trigger t_refund before delete on installation for each row declare v_mdeptno machine.deptno%type; begin select (*) into v_mdeptno from machine where tagnum =  ld.tagnum;if datediff('dy',sysdate,instdate) < 30 and v_mdeptno = 0 then update employee set balance = balance - ld.instcostwhere empnum = (select empnum from pc where tagnum = ld.tagnum);else if datediff('dy',sysdate,instdate) < 30 and v_mdeptno >= 1 then update depart set balance = balance - ld.instcostwhere deptno = (select deptno from machine where tagnum = ld.tagnum);else null; end if; end;
|
|
| Viewing: Dev Articles Community Forums > Databases > General SQL Development > date arithmetics |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
