469,291 Members | 1,742 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,291 developers. It's quick & easy.

Problem with trigger

Hi

I have a problem with a trigger. The trigger is used to calculate and store a total of minutes worked in a week. When the timeWorked or shiftConfirmedOrCancelled columns are updated the minutesWorked over the week are calculated and an aggregate is saved in a separate table (either update or insert). The times are calculated from a four inner join of Inserted, DefaultTimes, RequestedTimes, AgreedWorkedTimes

It works fine on a single processor low end server. On a four thread Server, Cancelling certain shifts fails and an ADO error bubbles up:

Number: 3219
Source: ADODB.Recordset
Description: Operation is not allowed in this context.

CREATE TRIGGER [dbo].at_UpdateAgreedTimesEmployeeHours
ON AgreedTimes
FOR INSERT, UPDATE--, DELETE

AS

BEGIN

DECLARE @TempTable TABLE
(
StartOfWeek datetime null
, EmployeeID int null
, MinutesSheduled int null
, Shifts smallint null
)

IF EXISTS
(
SELECT
*
-- ColID
-- , (((Colid -1) / 8) + 1) UpdatedBlock
-- , (((Colid -1) % 8)) UpdatedPower
-- , POWER(2, (((Colid -1) % 8))) [Power]
-- , Syscolumns.name
-- , SUBSTRING( columns_updated(),, (((Colid -1) / 8) + 1), 1) ColsUpdated

FROM
syscolumns
INNER JOIN
sysobjects
ON syscolumns.id = sysobjects.id
WHERE
sysobjects.name = 'AgreedTimes'
AND Syscolumns.name IN (
'ConfirmedStatus'
,'ActualStart'
,'ActualEnd'
,'ActualBreak'
,'ConfirmedStart'
,'ConfirmedEnd'
,'ConfirmedBreak'
)
AND SUBSTRING( columns_updated(),(((Colid -1) / 8) + 1), 1) & POWER(2, (((Colid -1) % 8))) > 0
)
BEGIN

INSERT INTO @TempTable
(
StartOfWeek
, EmployeeID
, MinutesSheduled
, Shifts
)
SELECT
StartOfWeek
, FillAvailableMemberID EmployeeID
, SUM(
CASE
WHEN CurrentEnd - CurrentBreak > CurrentStart THEN CurrentEnd - CurrentBreak - CurrentStart
ELSE CurrentEnd - CurrentBreak - CurrentStart +1440
END
* Multiplier
)
MinutesSheduled
, SUM(Multiplier) Shifts
FROM
(
SELECT
CAST(COALESCE(A.ActualStart, A.ConfirmedStart, A.ShiftStart, T.StartMin) as int) CurrentStart
, CAST(COALESCE(A.ActualEnd, A.ConfirmedEnd, A.ShiftEnd, T.EndMin) as int) CurrentEnd
, CAST(COALESCE(A.ActualBreak, A.ConfirmedBreak, T.BreakMin ) as int) CurrentBreak
, Multiplier
, FillAvailableMemberID
, StartOfWeek
FROM
DefaultTimes T
RIGHT JOIN
(
SELECT
C.ActualStart
, C.ConfirmedStart
, C.ActualEnd
, C.ConfirmedEnd
, C.ActualBreak
, C.ConfirmedBreak
, CASE
WHEN C.ConfirmedStatus is null THEN CAST(1 as SmallInt)
WHEN C.ConfirmedStatus = 0 THEN CAST(1 as SmallInt)
WHEN C.ConfirmedStatus = 1 THEN CAST(1 as SmallInt)
WHEN C.ConfirmedStatus = 2 THEN CAST(-1 as SmallInt)
END Multiplier
, R.ShiftStart
, R.ShiftEnd
, R.DefaultTimesID
, Modified.StartOfWeek
, Modified.FillAvailableMemberID
FROM
AgreedTimes C
INNER JOIN
RequestedTimes R
INNER JOIN
(
SELECT
FillAvailableMemberID
, dbo.af_StartOfWeekDate(ShiftDate) StartOfWeek
FROM
RequestedTimes
WHERE
Id = ANY (
SELECT TOP 1
RequirementRecordID
FROM
Inserted
WHERE
COALESCE(FillAvailableMemberID, 0) > 0
)
AND COALESCE(FillAvailableMemberID, 0) > 0
) Modified
ON R.FillAvailableMemberID = Modified.FillAvailableMemberID
AND R.ShiftDate BETWEEN Modified.StartOfWeek AND DATEADD(Day, 6, Modified.StartOfWeek)
ON C.RequirementRecordID = R.ID
WHERE
COALESCE(C.ConfirmedStatus, 0) >= 0
AND R.ShiftDate BETWEEN Modified.StartOfWeek AND DATEADD(Day, 6, Modified.StartOfWeek)
) A
ON T.ID = A.DefaultTimesID
) Shifts
GROUP BY
StartOfWeek
, FillAvailableMemberID
OPTION (MAXDOP 1) --Extra option added here

--if exists update
UPDATE
EmployeeWeek
SET
InternalMinutes = T.MinutesSheduled
, Shifts = T.Shifts
FROM
@TempTable T
, EmployeeWeek
WHERE
T.StartOfWeek = EmployeeWeek.[Week]
AND T.EmployeeID = EmployeeWeek.EmployeeID


--if not exists insert
INSERT INTO
EmployeeWeek
(
[Week]
, EmployeeID
, InternalMinutes
, Shifts
)
SELECT
T.StartOfWeek
, T.EmployeeID
, T.MinutesSheduled
, T.Shifts
FROM
@TempTable T
LEFT JOIN
dbo.EmployeeWeek
ON
T.StartOfWeek = EmployeeWeek.[Week]
AND T.EmployeeID = EmployeeWeek.EmployeeID
WHERE
EmployeeWeek.EmployeeWeekKey is null


END
END


Any thoughts , similar experiences please.
Mar 6 '07 #1
0 926

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Dunc | last post: by
1 post views Thread by BUSHII | last post: by
4 posts views Thread by JesusFreak | last post: by
2 posts views Thread by gustavo_randich | last post: by
2 posts views Thread by mob1012 via DBMonster.com | last post: by
2 posts views Thread by dean.cochrane | last post: by
11 posts views Thread by Ed Dror | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.