Home » SQL & PL/SQL » SQL & PL/SQL » Update a 2nd table using trigger on the first (Oracle 9i, windows)
Update a 2nd table using trigger on the first [message #432088] Sat, 21 November 2009 00:48 Go to next message
namb
Messages: 35
Registered: September 2009
Member
I am trying to update the table atten_det fields whenever a value is inserted in the musterdata table and the diference between in time and out time is equal to 8. Can someone please let me know where i am wrong here, trigger is created succesfully but when fired it deletes the corresponding column in atten_det instead of updating the desired fields

Many thanks in advance

CREATE or REPLACE TRIGGER emp_det_tgr
AFTER INSERT OR UPDATE
ON musterdata
REFERENCING NEW AS NEW OLD AS OLD    
FOR EACH ROW
BEGIN
if inserting then	
        if ( to_number(Trim(REPLACE(:new.OUT_TIME,':','.')))-
        to_number(Trim(REPLACE(:new.IN_TIME,':','.'))) = 8 )then
        update atten_det set                                    
vc_first_leave='P',vc_second_leave='P',
        num_time_in=:new.in_time, num_time_out=:new.out_time, 
        vc_shift_code=:new.vc_shift_code, extra_hrs_woff=:new.ot, 
        dt_atten=:new.musterdate where vc_emp_code=:old.eid;
	end if;
end if;
end emp_det_tgr;

/
show errors;




[Updated on: Sat, 21 November 2009 09:43] by Moderator

Report message to a moderator

Re: Update a 2nd table using trigger on the first [message #432100 is a reply to message #432088] Sat, 21 November 2009 03:00 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
namb wrote on Sat, 21 November 2009 07:48
I am trying to update the table atten_det fields whenever a value is inserted in the musterdata table and the diference between in time and out time is equal to 8. Can someone please let me know where i am wrong here, trigger is created succesfully but when fired it deletes the corresponding column in atten_det instead of updating the desired fields

Hard to tell, as this description is very vague (what does "deletes the correspondent column" mean? - there are 7 columns updated) and does not match the given code. Maybe there is another trigger causing this?

A few comments to the code: it updates multiple columns in row(s) where VC_EMP_CODE equal to given value. As it is taken from :OLD value, it is probably NULL for inserting. So, the condition is never true and no rows are updated.
By the way, how did you find out that the trigger was fired? What did you exactly do and what was its result?
Re: Update a 2nd table using trigger on the first [message #432131 is a reply to message #432088] Sat, 21 November 2009 09:46 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The trigger does what you say it to do, there is nothing wrong in what you posted.
If there is something wrong, show us.

Regards
Michel
Re: Update a 2nd table using trigger on the first [message #432154 is a reply to message #432088] Sun, 22 November 2009 04:38 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
namb wrote on Sat, 21 November 2009 07:48
        if ( to_number(Trim(REPLACE(:new.OUT_TIME,':','.')))-
        to_number(Trim(REPLACE(:new.IN_TIME,':','.'))) = 8 )then

This is not the way to do math on times.
Also, are you sure you only want this to return true if the difference between out_time and in_time is EXACTLY 8 (up till the minute)?
Previous Topic: basic PL/SQL question about recursive procedure..
Next Topic: Why the index isn't used?
Goto Forum:
  


Current Time: Fri Sep 30 02:13:02 CDT 2016

Total time taken to generate the page: 0.11311 seconds