Chain of triggers - how to break it?

I have an application to capture and process timesheet information.
Put simply, employees clock in and out at various locations. Business
logic determines how to process these events.

The system allows an administrator (though it's not strictly relevant,
this is through an ASP.NET front end) to determine "rules" for
employees. A rule will consist of a Rostered Start Time (the time that
an employee or group of employees is expected to "clock on"), a
threshold allowance for early and late starts, and a Minimum and a
Maximum number of minutes that an employee is expected to work. This
table is called tblTSRule (see DDL below).

Another part of the system assigns a rule to one or more employees.
The table that stores this data is called tblTSEmpRules. The columns
in this table contain links to the tblTSRule table, the tblEmployee
table, the date for which this rule applies, and the two fields that
are at the heart of this question. These are fldLowerBound and
fldUpperBound.

All employee timesheet entries (which are via a barcode scan on their
id badge) are simply raw data - the system captures the EmployeeID, the
location and the time of the scan. It doesn't differentiate between a
"clock on", a "clock off" or a "sub duty" (where an employee has left
their normal place of work to do a rostered duty at another location).
These raw "scans" are processed en bloc at a later date. To
illustrate, the UI presents the user with a range of dates, and the
user can then "apply the rules" to a date or range of dates.

At this point the system needs to collate all the raw scans according
to the date that they wish to process. However, let's say that the
date is the 31st January, but some of the employees for that date are
working a late shift. Logically, therefore, some of the relevant scans
will actually occur on 1 February.

In order to get all the relevant scans for each employee, therefore,
the "rule" for an employee for any particular date will contain the
Lower and Upper bounds between which all raw scans should be processed.
We found that with a typical data load of around 1,200,000 records
that processing was very slow if the lower and upper bounds were
calculated "on the fly" in the SQL, so instead we decided to store this
calculated data in the table itself. Codd may not like it, but it's
expedient.

The two fields fldLowerBound and fldUpperBound are calculated or
recalculated EITHER when a row in the tblTSRule table is updated or
when rows in the tblTSEmpRules table are either inserted or updated.
This is done via INSERT and UPDATE triggers. There is no requirement
to have an INSERT trigger on the tblTSRule, since creating a new rule
automatically removes the possibility that there are any related rows
in the tblTSEmpRules table. The triggers are scripted below.

What we are finding is that if an update is made on the tblTSRule
table, it fires TWO triggers - first the UPDATE trigger on the
tblTSRule table (which updates the tblTSEmpRules table) and then the
UPDATE trigger on the tblTSEmpRules table. The two triggers are
virtually identical, but what can we do in terms of design to get
around this? I should point out that the ability exists in the
application to amend both rules and the assignment of rules to
employees.

Thank you for reading this far. DDL below.

Edward

========================

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSEmpRules_tblTSRules]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSEmpRules] DROP CONSTRAINT
FK_tblTSEmpRules_tblTSRules
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CreateEmpCaptureBound]') and OBJECTPROPERTY(id,
N'IsTrigger') = 1)
drop trigger [dbo].[CreateEmpCaptureBound]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CreateCaptureBound]') and OBJECTPROPERTY(id,
N'IsTrigger') = 1)
drop trigger [dbo].[CreateCaptureBound]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[UpdateCaptureBound]') and OBJECTPROPERTY(id,
N'IsTrigger') = 1)
drop trigger [dbo].[UpdateCaptureBound]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblTSEmpRules]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblTSEmpRules]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblTSRule]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblTSRule]
GO

CREATE TABLE [dbo].[tblTSEmpRules] (
[fldEmpRuleID] [int] IDENTITY (1, 1) NOT NULL ,
[fldDate] [datetime] NULL ,
[fldEmployeeID] [int] NULL ,
[fldRuleID] [int] NULL ,
[fldLowerBound] [datetime] NOT NULL ,
[fldUpperBound] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTSRule] (
[fldRuleID] [int] IDENTITY (1, 1) NOT NULL ,
[fldCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[fldDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldRosteredStart] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[fldEarlyStartArb] [int] NULL ,
[fldLateStartArb] [int] NULL ,
[fldMinMins] [int] NULL ,
[fldMaxMins] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSEmpRules] WITH NOCHECK ADD
CONSTRAINT [PK_tblTSEmpRules] PRIMARY KEY CLUSTERED
(
[fldEmpRuleID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSRule] WITH NOCHECK ADD
CONSTRAINT [PK_tblTimesheetRules] PRIMARY KEY CLUSTERED
(
[fldRuleID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSEmpRules] WITH NOCHECK ADD
CONSTRAINT [DF_tblTSEmpRules_fldLowerBound] DEFAULT (getdate()) FOR
[fldLowerBound],
CONSTRAINT [DF_tblTSEmpRules_fldUpperBound] DEFAULT (getdate()) FOR
[fldUpperBound]
GO

ALTER TABLE [dbo].[tblTSRule] WITH NOCHECK ADD
CONSTRAINT [DF_tblTSRule_fldRosteredStart] DEFAULT ('00:00') FOR
[fldRosteredStart],
CONSTRAINT [DF_tblTSRule_fldMaxMins] DEFAULT (0) FOR [fldMaxMins],
CONSTRAINT [IX_tblTSRules_1] UNIQUE NONCLUSTERED
(
[fldCode],
[fldSubAreaCode]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE UNIQUE INDEX [IX_tblTSEmpRules] ON
[dbo].[tblTSEmpRules]([fldEmployeeID], [fldDate]) WITH FILLFACTOR = 90
ON [PRIMARY]
GO

CREATE INDEX [IX_tblTSEmpRules_1] ON [dbo].[tblTSEmpRules]([fldDate])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblTSRules] ON [dbo].[tblTSRule]([fldCode]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSEmpRules] ADD
CONSTRAINT [FK_tblTSEmpRules_tblEmployee1] FOREIGN KEY
(
[fldEmployeeID]
) REFERENCES [dbo].[tblEmployee] (
[fldEmployeeID]
),
CONSTRAINT [FK_tblTSEmpRules_tblTSRules] FOREIGN KEY
(
[fldRuleID]
) REFERENCES [dbo].[tblTSRule] (
[fldRuleID]
) NOT FOR REPLICATION
GO

ALTER TABLE [dbo].[tblTSRule] ADD
CONSTRAINT [FK_tblTSRules_tblSubArea] FOREIGN KEY
(
[fldSubAreaCode]
) REFERENCES [dbo].[tblSubArea] (
[fldSubAreaCode]
) NOT FOR REPLICATION
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER CreateEmpCaptureBound ON dbo.tblTSEmpRules

FOR INSERT,UPDATE

AS

-- Calculate the EARLIEST POSSIBLE clock in time for this
employee/rule/date record and write it to fldLowerBound.
-- Calculate the LATEST POSSIBLE clock out time for this employee/rule
record/date and write it to fldUpperBound.

-- Write data to ALL affected rows.

update tblTSEmpRules

set
fldLowerBound =
dateadd(
mi,
( tblTSRule.fldMaxMins - 1440 ) * 0.5,
dbo.fnGetDateFromDateAndVarCharTimeParts(-- Rostered start date &
time
inserted.fldDate, -- Date on which rule is to be applied
tblTSRule.fldRosteredStart)-- Time
),

fldUpperBound =
dateadd(mi,
( tblTSRule.fldMaxMins + 1440 ) * 0.5,
dbo.fnGetDateFromDateAndVarCharTimeParts(-- Rostered start date &
time
inserted.fldDate,-- Date on which rule is to be applied
tblTSRule.fldRosteredStart)-- Time
)

FROM inserted INNER JOIN
tblTSRule ON inserted.fldRuleID =
tblTSRule.fldRuleID

WHERE inserted.fldEmpRuleID=tblTSEmpRules.fldEmpRuleID

print 'Trigger for TSEmpRules fired...'

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER UpdateCaptureBound ON dbo.tblTSRule

FOR UPDATE

AS

update tblTSEmpRules

set
fldLowerBound =
dateadd(
mi,
( inserted.fldMaxMins - 1440 ) * 0.5,
dbo.fnGetDateFromDateAndVarCharTimeParts(-- Rostered start date &
time
tblTSEmpRules.fldDate, -- Date on which rule is to be applied
inserted.fldRosteredStart)-- Time
),

fldUpperBound =
dateadd(mi,
( inserted.fldMaxMins + 1440 ) * 0.5,
dbo.fnGetDateFromDateAndVarCharTimeParts(-- Rostered start date &
time
tblTSEmpRules.fldDate,-- Date on which rule is to be applied
inserted.fldRosteredStart)-- Time
)

FROM inserted

WHERE inserted.fldRuleID = tblTSEmpRules.fldRuleID

print 'update trigger for TSRule fired.....'

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO(teddysnips@.hotmail.com) writes:
> What we are finding is that if an update is made on the tblTSRule
> table, it fires TWO triggers - first the UPDATE trigger on the
> tblTSRule table (which updates the tblTSEmpRules table) and then the
> UPDATE trigger on the tblTSEmpRules table. The two triggers are
> virtually identical, but what can we do in terms of design to get
> around this? I should point out that the ability exists in the
> application to amend both rules and the assignment of rules to
> employees.

There are a couple of options.

1) Set the configuration option "nested triggers" to 0. But this is a
server-wide option. I recommend that you leave it on.

2) In the trigger tblTSEmpRules add:

IF NOT EXISTS (SELECT * FROM inserted WHERE
fldLowerBound IS NULL OR fldUpperBound IS NULL)
RETURN

3) In the trigger TSRules add:

CREATE TABLE #no$cascade(a int NOT NULL)

And in the other trigger add:

IF object_id('tempdb..#no$cascade') IS NOT NULL
RETURN

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> I have an application to capture and process timesheet information. Put simply, employees clock in and out at various locations. Business logic determines how to process these events... <<

Take a look at the job clock at http://www.exaktime.com. I designed
their database, and I did not need triggers.

I am writing questions for two exams do not have time to go thru your
code right (and clean out all the "tbl-" and 'fld-" prefixes, bring it
up to ISO-11179, remvoe IDENTITY columns, etc.) But based on a quick
scan, it looks like it could made much easier.