Tough little trigger

[rkrueger]

Given the following 3 Tables:

CREATE TABLE [Company] (
[CompanyID] [int] NOT NULL ,
[DateTimeCreated] [datetime] NOT NULL CONSTRAINT
[DF_Company_DateTimeCreated] DEFAULT (getdate()),
[DateTimeModified] [datetime] NULL ,
CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[CompanyID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [CompanyOffice] (
[CompanyID] [int] NOT NULL ,
[OfficeID] [int] NOT NULL ,
[IsActive] [bit] NOT NULL ,
CONSTRAINT [PK_CompanyOffice] PRIMARY KEY CLUSTERED
(
[CompanyID],
[OfficeID]
) ON [PRIMARY] ,
CONSTRAINT [FK_CompanyOffice_Company] FOREIGN KEY
(
[CompanyID]
) REFERENCES [Company] (
[CompanyID]
),
CONSTRAINT [FK_CompanyOffice_Office] FOREIGN KEY
(
[OfficeID]
) REFERENCES [Office] (
[OfficeID]
)
) ON [PRIMARY]
GO

CREATE TABLE [Office] (
[OfficeID] [int] NOT NULL ,
[DateTimeCreated] [datetime] NOT NULL CONSTRAINT
[DF_Office_DateTimeCreated] DEFAULT (getdate()),
[DateTimeModified] [datetime] NULL ,
[FullOfficeName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
CONSTRAINT [PK_Office] PRIMARY KEY CLUSTERED
(
[OfficeID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

The CompanyOffice.dbo.IsActive bit field is supposed to be marked
"true" for 1 record per a given Office (i.e. there can only be a single
"Active" Company for any given Office). I decided the best way to
enforce is through a trigger...

My initial thoughts were a toggling effect (similar to the behavior
that a radio button exhibits)... which would work like a champ for a
Single Row Insert or Update but for a Multi Row Insert/Update not that
staight forward... I fooled around a little with some complicated
sub-queries that did not pan out. The only other way to do this is to
utilize a cursor (at least that I can think of). Because of the
overhead with a cursor, I find this incredibly undesirable.

My secondary thought was to just restrict an Insert or Update Statement
that leaves the Table in an "error" state (2 or 0 Active Companies per
an Office). Then I realized that if the "Toggling Trigger" did not
exist from above, it will often be the case that the Table would have
to be left in an "error" state for a short while, until a second update
statement is run. (example, I insert a new active Company in the
CompanyOffice table for an Office, then I go to the other active
Company record for this Office and set the IsActive flag to false...
for that short period of time between the 2 statement the DB is an
"error" state, because there are 2 Active Companies for that single
Office.) That makes this solution very undesirable.

Any suggestions?

Thanks in Advance --
Rich

[Hugo Kornelis]

On 25 Mar 2005 10:33:54 -0800, rk******@gmail.com wrote:

(snip DDL - thanks for posting it!)

The CompanyOffice.dbo.IsActive bit field is supposed to be marked
"true" for 1 record per a given Office (i.e. there can only be a single
"Active" Company for any given Office). I decided the best way to
enforce is through a trigger...
Hi Rich,

Well, there's another design possiblity as well: instead of an IsActive
column, you could add a column to the Office table: ActiveCompany. Then
set up a foreign key from (ActiveCompany, OfficeId) in the Office table
to (CompanyId, OfficeId) in CompanyOffice, to ensure that it's
impossible to active a company that the office isn't related to.

In case you have good reasons to stick to your original design, I'll try
to address the rest of your message.

My initial thoughts were a toggling effect (similar to the behavior
that a radio button exhibits)... which would work like a champ for a
Single Row Insert or Update
Only in part. I can see you'd automatically deactivate the previous
choice if you activate a new choice - but what would you do if I set the
IsActive bit in a specific row to 0 without simultaneously setting it to
1 in another row for the same office. Would you just randomly pick one
of the office's companies and activate it? Or would you abort the
transaction and raise an error? If it were my project, I'd choose the
latter.
but for a Multi Row Insert/Update not that
staight forward... I fooled around a little with some complicated
sub-queries that did not pan out. The only other way to do this is to
utilize a cursor (at least that I can think of). Because of the
overhead with a cursor, I find this incredibly undesirable.

(snip)

The multi-row insert/update does need a bit more thought, but there's no
need to use a cursor (there seldom is). Let's first investigate the
possibilities:

1. The insert/update leaves one or more offices without any active
company. As indicated above, I'll assume this should result in an error
condition.

2. One or more offices that are affected by the insert/update have one
active company. Great; that's exactly what wew want. No action needed
for these offices.

3. One or more offices that are affected by the insert/update now have
more than one active company, BUT only one of them as a direct result of
the insert/update (the other already was active). In this case, the
toggle can be applied.

4. One or more offices that are affected by the insert/update now have
more than one active company, AND more than one of them as a direct
result of the insert/update. Though you could choose to pick one at
rando to retain as active and silently deactivate all others, I'd say
that this should really result in an error.

Note that all four situations can be present in the same multi-row
insert or update, so the trigger should handle them all. For efficiency,
I'll check the two error conditions first, then handle the toggling.
Situation 2 needs no handling, of course!

CREATE TRIGGER MyTrigger
ON CompanyOffice
AFTER INSERT, UPDATE
AS
-- Bail out if no processing needed
IF @@ROWCOUNT = 0 RETURN
IF NOT UPDATE(IsActive) RETURN
-- Prevent recursive execution
IF TRIGGER_NESTLEVEL(object_id('MyTrigger')) > 1 RETURN

-- Situation 4: 2 companies for same office activated
IF EXISTS (SELECT *
FROM inserted
WHERE IsActive = 1
GROUP BY OfficeID
HAVING COUNT(*) > 1)
BEGIN
RAISERROR ('Situation 4', 16, 1)
ROLLBACK TRANSACTION
RETURN
END

-- Situation 1: no companies activated for an office
IF EXISTS (SELECT *
FROM CompanyOffice
WHERE OfficeID IN (SELECT OfficeID FROM inserted)
GROUP BY OfficeID
HAVING MAX(IsActive) = 0)
BEGIN
RAISERROR ('Situation 4', 16, 1)
ROLLBACK TRANSACTION
RETURN
END

-- Situation 3: switch active company for office
-- no need to check for >1 new active company for an office;
-- that situation has already been handled above.
UPDATE CompanyOffice
SET IsActive = 0
WHERE IsActive = 1
AND EXISTS (SELECT *
FROM inserted AS i
WHERE i.OfficeID = CompanyOffice.OfficeID
AND i.CompanyID <> CompanyOffice.CompanyID
AND i.IsActive = 1)
go

(Note: the above code is untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[John Bell]

Hi

You will need to create business rules so that you know what to do if a
active office is deleted or if you try to change the status. Although
re-reading your post seems to indicate that the following is not really what
you wanted you should be able to use it to get what you require, this
assumes that there is one active office per company and that is always the
minumum office id for that given company. The IsActive flag is automatically
assigned regardless of the value inserted and updates are not allowed to
change IsActive.

CREATE TABLE [Company] (
[CompanyID] [int] NOT NULL ,
[DateTimeCreated] [datetime] NOT NULL CONSTRAINT
[DF_Company_DateTimeCreated] DEFAULT (getdate()),
[DateTimeModified] [datetime] NULL ,
CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[CompanyID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [Office] (
[OfficeID] [int] NOT NULL ,
[DateTimeCreated] [datetime] NOT NULL CONSTRAINT
[DF_Office_DateTimeCreated] DEFAULT (getdate()),
[DateTimeModified] [datetime] NULL ,
[FullOfficeName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
CONSTRAINT [PK_Office] PRIMARY KEY CLUSTERED
(
[OfficeID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

DROP TABLE [CompanyOffice]

CREATE TABLE [CompanyOffice] (
[CompanyID] [int] NOT NULL ,
[OfficeID] [int] NOT NULL ,
[IsActive] [bit] NOT NULL CONSTRAINT DF_IsActive DEFAULT (0),
CONSTRAINT [PK_CompanyOffice] PRIMARY KEY CLUSTERED
(
[CompanyID],
[OfficeID]
) ON [PRIMARY] ,
CONSTRAINT [FK_CompanyOffice_Company] FOREIGN KEY
(
[CompanyID]
) REFERENCES [Company] (
[CompanyID]
),
CONSTRAINT [FK_CompanyOffice_Office] FOREIGN KEY
(
[OfficeID]
) REFERENCES [Office] (
[OfficeID]
)ON DELETE CASCADE
) ON [PRIMARY]
GO

CREATE TRIGGER trg_Company_Office_Insert ON [CompanyOffice]
INSTEAD OF INSERT AS
BEGIN
IF @@ROWCOUNT = 0 RETURN
SET NOCOUNT ON

INSERT INTO [CompanyOffice] ( [CompanyID], [OfficeID], [IsActive] )
SELECT n.[CompanyID], n.[OfficeID],
CASE WHEN NOT EXISTS ( SELECT 1
FROM [CompanyOffice]o
WHERE n.[CompanyID] = o.[CompanyID]
AND o.[IsActive] = 1 )
AND NOT EXISTS ( SELECT 1
FROM INSERTED i
WHERE n.[CompanyID] = i.[CompanyID]
AND n.[OfficeID] > i.[OfficeID] )
THEN 1
ELSE 0 END
FROM INSERTED n
END
GO

CREATE TRIGGER trg_Company_Office_Delete ON [CompanyOffice]
FOR DELETE AS
BEGIN
IF @@ROWCOUNT = 0 RETURN
SET NOCOUNT ON

UPDATE c
SET [IsActive] = 1
FROM [CompanyOffice] c
JOIN DELETED d ON c.[CompanyID] = d.[CompanyID] AND c.[OfficeID] = ( SELECT
MIN(o.[OfficeID]) FROM [CompanyOffice] o WHERE c.[CompanyID] = o.[CompanyID]
AND c.[OfficeID] > d.[OfficeID] )
WHERE d.[IsActive] = 1
END

CREATE TRIGGER trg_Company_Office_Update ON [CompanyOffice]
INSTEAD OF UPDATE AS
BEGIN
IF @@ROWCOUNT = 0 RETURN
SET NOCOUNT ON

IF UPDATE ([IsActive])
BEGIN

IF EXISTS ( SELECT 1
FROM INSERTED i
JOIN DELETED d ON i.[CompanyID] = d.[CompanyID] AND i.[OfficeID] =
d.[OfficeID] AND i.[IsActive] = 0 AND d.[IsActive] = 1 )
OR EXISTS ( SELECT 1
FROM INSERTED i
JOIN DELETED d ON i.[CompanyID] = d.[CompanyID] AND i.[OfficeID] =
d.[OfficeID] AND i.[IsActive] = 1 AND d.[IsActive] = 0 )
RAISERROR ('Can not change IsActive', 16, 1)
END
END
GO

INSERT INTO [Company] ( [CompanyID] , [DateTimeCreated],
[DateTimeModified] )
SELECT 1, getdate(), getdate()
UNION ALL SELECT 2, getdate(), getdate()
UNION ALL SELECT 3, getdate(), getdate()
UNION ALL SELECT 4, getdate(), getdate()
UNION ALL SELECT 5, getdate(), getdate()

INSERT INTO [Office] ( [OfficeID], [DateTimeCreated], [DateTimeModified],
[FullOfficeName] )
SELECT 1, getdate(), getdate(), 'Company 1 Office 1'
UNION ALL SELECT 2, getdate(), getdate(), 'Company 2 Office 2'
UNION ALL SELECT 3, getdate(), getdate(), 'Company 3 Office 3'
UNION ALL SELECT 4, getdate(), getdate(), 'Company 1 Office 4'
UNION ALL SELECT 5, getdate(), getdate(), 'Company 1 Office 5'
UNION ALL SELECT 6, getdate(), getdate(), 'Company 2 Office 6'
UNION ALL SELECT 7, getdate(), getdate(), 'Company 2 Office 7'
UNION ALL SELECT 8, getdate(), getdate(), 'Company 4 Office 8'
UNION ALL SELECT 9, getdate(), getdate(), 'Company 2 Office 9'
UNION ALL SELECT 10, getdate(), getdate(), 'Company 5 Office 10'
UNION ALL SELECT 11, getdate(), getdate(), 'Company 4 Office 11'
INSERT INTO [CompanyOffice] ([CompanyID], [OfficeID] )
SELECT 1, 1
UNION ALL SELECT 2, 2
UNION ALL SELECT 3, 3
UNION ALL SELECT 1, 4
UNION ALL SELECT 1, 5
UNION ALL SELECT 2, 6
UNION ALL SELECT 2, 7
UNION ALL SELECT 4, 8
UNION ALL SELECT 2, 9
UNION ALL SELECT 5, 10
UNION ALL SELECT 4, 11
SELECT * FROM [CompanyOffice]
ORDER BY [CompanyID] , [OfficeID]

DELETE FROM [CompanyOffice]
WHERE [CompanyID] = 2
AND [OfficeID] = 2

SELECT * FROM [CompanyOffice]
ORDER BY [CompanyID] , [OfficeID]

DELETE FROM [CompanyOffice]
WHERE [CompanyID] = 2
AND [OfficeID] = 6

SELECT * FROM [CompanyOffice]
ORDER BY [CompanyID] , [OfficeID]

DELETE FROM [CompanyOffice]
WHERE [CompanyID] = 2
AND [OfficeID] = 9

SELECT * FROM [CompanyOffice]
ORDER BY [CompanyID] , [OfficeID]

UPDATE [CompanyOffice]
SET [IsActive] = 0
WHERE [CompanyID] = 1
AND [OfficeID] = 1

SELECT * FROM [CompanyOffice]
ORDER BY [CompanyID] , [OfficeID]

UPDATE [CompanyOffice]
SET [IsActive] = 1
WHERE [CompanyID] = 1
AND [OfficeID] = 4

SELECT * FROM [CompanyOffice]
ORDER BY [CompanyID] , [OfficeID]

UPDATE [CompanyOffice]
SET [IsActive] = 1
WHERE [CompanyID] = 1
AND [OfficeID] = 1

SELECT * FROM [CompanyOffice]
ORDER BY [CompanyID] , [OfficeID]

INSERT INTO [Office] ( [OfficeID], [DateTimeCreated], [DateTimeModified],
[FullOfficeName] )
SELECT 12, getdate(), getdate(), 'Company 5 Office 12'

INSERT INTO [CompanyOffice]( [CompanyID], [OfficeID], [IsActive] )
SELECT 5, 12, 1

SELECT * FROM [CompanyOffice]
ORDER BY [CompanyID] , [OfficeID]

John

<rk******@gmail.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...

The CompanyOffice.dbo.IsActive bit field is supposed to be marked
"true" for 1 record per a given Office (i.e. there can only be a single
"Active" Company for any given Office). I decided the best way to
enforce is through a trigger...

My initial thoughts were a toggling effect (similar to the behavior
that a radio button exhibits)... which would work like a champ for a
Single Row Insert or Update but for a Multi Row Insert/Update not that
staight forward... I fooled around a little with some complicated
sub-queries that did not pan out. The only other way to do this is to
utilize a cursor (at least that I can think of). Because of the
overhead with a cursor, I find this incredibly undesirable.

My secondary thought was to just restrict an Insert or Update Statement
that leaves the Table in an "error" state (2 or 0 Active Companies per
an Office). Then I realized that if the "Toggling Trigger" did not
exist from above, it will often be the case that the Table would have
to be left in an "error" state for a short while, until a second update
statement is run. (example, I insert a new active Company in the
CompanyOffice table for an Office, then I go to the other active
Company record for this Office and set the IsActive flag to false...
for that short period of time between the 2 statement the DB is an
"error" state, because there are 2 Active Companies for that single
Office.) That makes this solution very undesirable.

Any suggestions?

Thanks in Advance --
Rich

[--CELKO--]

My first question is can you give me a company with a 100 character
name? Why not use the USPS rule that an address line is 35 characters?
If we re-do your schema, we do not need triggers:
CREATE TABLE Companies
(company_id INTEGER NOT NULL PRIMARY KEY,
company_name VARCHAR(35) NOT NULL,
.. );

Can I assume that an office must belong to a company?

CREATE TABLE CompanyOffices
(company_id INTEGER NOT NULL
REFERENCES Companies(company_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
office_id INTEGER NOT NULL,
office_name VARCHAR(35) NOT NULL,
priority_nbr INTEGER NOT NULL
CHECK (priority_nbr > 0),
PRIMARY KEY(company_id, office_id);

Now create a VIEW that always has an office showing:

CREATE VIEW ActiveOffices (company_id, office_id, office_name
AS
SELECT O1.company_id, O1.office_id, C1.company_name, O1.office_name
FROM CompanyOffices AS O1, Companies AS C1
WHERE C1.company_id = O1.company_id
AND O1.priority_nbr
= (SELECT MIN(O2.priority_nbr)
FROM CompanyOffices AS O2
WHERE O1.company_id = O2.company_id);
You can now assign the offices within company a priority ordering. If
you wish you can also close up gaps in the priority numbers:

UPDATE CompanyOffices
SET priority_nbr
= (SELECT COUNT(*)
FROM CompanyOffices AS O1
WHERE O1.company_id = CompanyOffices.company_id
AND O1.priority_nbr <= CompanyOffices.priority_nbr);

Why did you put audit information into the base tables? There are tools
for that which will not mess up the data model or slwo down the
applications.

[--CELKO--]

Opps! I forgot to put in my lecture on why uses BIT flags is a bad
programming practice in SQL. But then you saw how it lead to thinking
in procedural code instead of relational, declarative code. Even Hugo
and John fell into that mode of operation!

You might want to go thru your other code, look for a BIT and then find
ways to remove the procedural code used to set it. They are almost
always computed columns with redundant data.

[Hugo Kornelis]

On 27 Mar 2005 07:18:51 -0800, --CELKO-- wrote:

Opps! I forgot to put in my lecture on why uses BIT flags is a bad
programming practice in SQL. But then you saw how it lead to thinking
in procedural code instead of relational, declarative code. Even Hugo
and John fell into that mode of operation!

Hi Joe,

Missed my first paragraph, eh?

Anyway, I do like your solution with the priority column. I can't tell
if it fits in the OP's situation (since he didn't disclose the nature of
his real-world problem), bit I do like it.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[--CELKO--]

>> Anyway, I do like your solution with the priority column. I can't
tell if it fits in the OP's situation (since he didn't disclose the
nature of his real-world problem), bit I do like it. <<

Another trick is to restrict the companies to one and only one active
office:

CREATE TABLE CompanyOffices
(company_id INTEGER NOT NULL
REFERENCES Companies(company_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
office_id INTEGER NOT NULL,
office_name VARCHAR(35) NOT NULL,
priority_nbr INTEGER NOT NULL
CHECK (priority_nbr > 0),
UNIQUE (company_id, priority_nbr),
PRIMARY KEY(company_id, office_id);

[--CELKO--]

>> Anyway, I do like your solution with the priority column. I can't
tell if it fits in the OP's situation (since he didn't disclose the
nature of his real-world problem), bit I do like it. <<

Another trick is to restrict the companies to one and only one active
office:

CREATE TABLE CompanyOffices
(company_id INTEGER NOT NULL
REFERENCES Companies(company_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
office_id INTEGER NOT NULL,
office_name VARCHAR(35) NOT NULL,
priority_nbr INTEGER NOT NULL
CHECK (priority_nbr > 0),
UNIQUE (company_id, priority_nbr),
PRIMARY KEY(company_id, office_id));