PLEASE HELP - Trigger for Time Difference 1

[nickty]

I wondered if anyone can help - I have been working on this trigger for 3 days now - with no luck.
I have two fields TIME_RESOLVED and TIME_STARTED - all I want to do is take one from the other and put the value in TIME_TAKEN field.
The trigger 'works' after a fashion - but will only insert 00.00 no matter what values I use. Can anyone PLEASE tell me where I am going wrong.
Thank you.

Code below (if this helps)



begin
if :new.TIME_TAKEN is null and
:new.DATE_RESOLVED is not null and
:new.TIME_RESOLVED is not null then
select to_date((sysdate) + mod((to_datenew.TIME_RESOLVED,'dd-mm-yyyy hh24.mi')
- to_datenew.TIME_STARTED,'dd-mm-yyyy hh24.mi')),1), 'dd-mm-yyyy hh24.mi')
into :new.TIME_TAKEN from dual;
end if;
end;

 

[sem]

What is your TIME_TAKEN data type? You may use number to work in Oracle fasion to measure time intervals in days. In this case your trigger will be:

begin
if :new.TIME_TAKEN is null and
:new.time_started is not null and
:new.TIME_RESOLVED is not null
then
:new.time_taken := :new.time_resolved - :new.time_started;
end if;
end;

assuming you TIME_RESOLVED and TIME_STARTED are both of DATE type.

The problem with your trigger is probably in using dates (sysdate, probably :new.TIME_STARTED..) within to_date, that implyies implicit type conversion (to_char(sysdate)).
Your "select .. from dual" is also seems to be a bit redundant.

If you use other data types, please specify the table structure

 

[nickty]

Hi
Thank you so much for your help - I am a step forward now.
TIME_STARTED and TIME_RESOLVED are both 'date' data types. TIME_TAKEN was - but I have changed it to 'number' as per your advice.
I have tried the trigger you suggested -but it is calculating 12.12 - 11.12 as 0.0416666666666666666666666666666666666667
and not 1 hour as it should be.
Any suggestions as to why this is??
As you may be able to tell - I am an pl/sql novice and need all the help I can get.

 

[sem]

It's definitely 1 hour: 1/24 of a day. To get the result in hours just multiply it by 24, in minutes - by 24*60 and so on

 

[nickty]

Thank you = it now works...although I have another problem (am I pushing my luck here?)..in my table I have to record the following details of a task:-
TIME_STARTED
DATE_STARTED
TIME_RESOLVED
DATE_RESOLVED
TIME_TAKEN
I have each of the following as a separate field (although I am aware that the whole date string is recorded each time).
I have set a default to display automatically the current date (sysdate) and a trigger to fill in the current time (sysdate) if the field is left blank by the user input.
This is fine - it inserts the correct date and the correct time to the table. However if the user inserts their own time - it always puts the time they have specified - but puts the date as 1st of every month (no matter what the date is).
This is a problem - if they accept the default time of start time (eg)2nd July 15.00 but then insert their own time of 15.30 as time resolved(this is recorded as 1st July 15.30) and the task is calculated to have taken -24 hours - which is not correct??

Any ideas at all would be a great help. Thank you.

 

[sem]

You may try to change the times in the pre-insert trigger to combine the time entered and your default (current) date.

For example, to sustitute the date with the current day:
ATE_STARTED :=
trunc(sysdate) + DATE_STARTED-truncDATE_STARTED)).

But why should you use separate fields for date and time? Isn't it much easier to have 1 field for both? Of course, Oracle stores complete date value including year-month-..-seconds.

 

[nickty]

You have been a great help - I am getting ambitious now - I am now trying to enter a trigger which when the TIME_TAKEN field is completed the status of the job is automatically 'closed'(just recording the string 'closed' to the database). I have a LOV which has a number of selections - the default is 'Open'-
I have tried :

begin
if :new.TIME_TAKEN is not null
then
:new.PROGRESS :='Closed';
end if;
end;

which compiles - but then doesn't work - am I on the right lines or way off beam?
Thank you.

 

[sem]

Check data type of progress field. Try to create trigger from sql*plus, for forms and procedure builder do not report some trigger errors.