473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 1026

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

Similar topics

1
by: Dunc | last post by:
I'm new to Postgres, and getting nowhere with a PL/Perl trigger that I'm trying to write - hopefully, someone can give me some insight into what I'm doing wrong. My trigger is designed to reformat...
4
by: Hank | last post by:
I have two SQL Server 2000 machines (server_A and server_B). I've used sp_addlinkedserver to link them both, the link seems to behave fine. I can execute remote queries and do all types of neat...
1
by: BUSHII | last post by:
I have little problem and I dont have any idea how to make my trigger. I have table MyTable where I have many column with almost same name and same type (Grp1,Grp2,Grp3,Grp4...Grp50 char(1))....
4
by: JesusFreak | last post by:
From: us_traveller@yahoo.com (JesusFreak) Newsgroups: microsoft.public.scripting.jscript Subject: toolbar script problem NNTP-Posting-Host: 192.92.126.136 Recently, I downloaded the following...
2
by: gustavo_randich | last post by:
Hi :-) I'm porting a project from Oracle to DB2 and now I'm trying to avoid error SQL0746N in a trigger which reads the same table in which the trigger is defined. Below is Oracle's...
4
by: SUKRU | last post by:
Hello everybody. Unfortunately I am pretty new to sql-server 2000 I need some help with a Trigger I created. I created a trigger witch takes the id of the affected row and does a update on a...
2
by: mob1012 via DBMonster.com | last post by:
Hi All, I wrote last week about a trigger problem I was having. I want a trigger to produce a unique id to be used as a primary key for my table. I used the advice I received, but the trigger is...
2
by: dean.cochrane | last post by:
I have inherited a large application. I have a table which contains a hierarchy, like this CREATE TABLE sample_table( sample_id int NOT NULL parent_sample_id int NOT NULL ....lots of other...
3
by: anuja pokharel | last post by:
hello, i have a problem in trigger. My trigger is CREATE OR REPLACE TRIGGER duplicate_deptno BEFORE INSERT OR UPDATE OF deptno ON DEPT1 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW...
11
by: Ed Dror | last post by:
Hi there, I'm using ASP.NET 2.0 and SQL Server 2005 with VS 2005 Pro. I have a Price page (my website require login) with GridView with the following columns PriceID, Amount, Approved,...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.