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.