STORE PROCEDURE HELP
-
Hello All,
I am new to this forum and I hope some experts can able to solve my problem.
I have a table which has got five fields
ID int
PRODUCTIONNO varchar(20)
STARTTIME datetime
ENDTIME datetime
ELAPSETIME float
There will be many jobs for one productionnumber and my question is if the user enters the productionno for the firsttime then it will be the start time and the next time he enters I want the time to go the endtime in the first row and calculates the elapse time and enter a new row with the starttime. For eg. if there are 10 functions for one productionreportno the user always enters one time but it has to go & update in the endtime in the previous record as well as insert a new row with the starttime.
If the user deletes one row the time has to be adjusted in other rows based on the production no For eg. if there are three rows which has 9.00am starttime 10.00am endtime in the first row & the second row has 10.00am and 1.00pm and the third row has 1.00pm and 4.00pm with the same productionno. the user deletes the second record so I want the two rows to be updated automatically like this. 9.00am starttime 1.00pm endtime in the first row and 1.00pm and 4.00pm in the second row.
I am really breaking my head to solve this issue anybody please give me some solutions and if anyone needs more info I can explain it more.
Thanks
-
It sounds like you want a stored procedure that will go throgh and snyc your start/end/elapsed times in your table. You could accomplish this real-time using instead of triggers. Will you only have inserts and deletes from this table, or will you also have updates?
-
Yes you are right John but I need an idea of how the triggers should be since I am new to SQL and there is no seperate SQL guy in our development team.I need to have updates as well.
Thanks
-
you ONLY need a delete trigger to fire...
assume:
ID is pk or unique,
(PRODUCTIONNO, STARTTIME) also a unique key.
trigger as follows:
CREATE TRIGGER table_name_dt
ON table_name
AFTER DELETE
AS
BEGIN
declare @d_starttime datetime
, @d_endtime datetime
, @d_productionno varchar(20)
, @u_id int
select @d_productionno = PRODUCTIONNO
, @d_startime = STARTTIME
, @d_endtime = ENDTIME
from deleted
select @u_id = t.id
from table_name t
where t.PRODUCTIONNO = @d_productionno
and t.STARTTIME = (select max(STARTTIME)
from table_name
where PRODUCTIONNO = t.PRODUCTIONNO
and STARTTIME < @d_startime
)
if @u_id is not NULL
BEGIN
update table_name
set ENDTIME = @d_endtime
where ID = @u_id
END
END
GO
-
He needs more than just a delete trigger. It states in his first example that when new records are inserted, the previous records need to be updated with an end time and a newly calculated elapsed time. That is why I asked if he allows updates as well. An update would require that all other rows for that batch may need sync'd.
-
Yes I need both update and insert trigger as well thats where I am stuck and I dont know how to go and do the batch updates for that particular production report no.
-
I haven't put too much time into this but it should be a starting point for you.
You will need to write a user defined function to calculate the elapsed time based on a start and end time but even a beginner should be able to work that out.
CREATE TRIGGER table_name_it
ON table_name
AFTER INSERT
AS
BEGIN
UPDATE
table_name
SET
ENDTIME = i.STARTTIME
, ELAPSETIME = udf_CalculateElapsedTime(t.STARTTIME, i.STARTTIME)
FROM
table_name t
INNER JOIN
inserted i
ON
i.PRODUCTIONNO = t.PRODUCTIONNO
WHERE
t.ENDTIME IS NULL
END
GO
CREATE TRIGGER table_name_ut
ON table_name
AFTER UPDATE
AS
BEGIN
UPDATE
table_name
SET
ENDTIME = i.STARTTIME
, ELAPSETIME = udf_CalculateElapsedTime(t.STARTTIME, i.STARTTIME)
FROM
table_name t
INNER JOIN
inserted i
ON
i.PRODUCTIONNO = t.PRODUCTIONNO
INNER JOIN
deleted d
ON
d.PRODUCTIONNO = t.PRODUCTIONNO
AND
d.STARTTIME = t.ENDTIME
END
GO
Hopefully this gives you a starting point.
-
Thanks a lot Steve this should give me a good start I am going to try it tommorow morning and see how it goes keeping my fingers crossed.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply