CODEWITHSUNDEEP
sqlsql-server
Liam Björkman
Liam Björkman

Reputation: 37

Compare insert data to existing data in SQL

The title might be misleading, I just can't wrap my head around this.

I have 3 tables; Employee, EmployeeSchedule and LocalStore. In the Employee table there is a column of data type bit called "IsManager", in EmployeeSchedule the days are simply labeled "Monday" to "Sunday" and each store has a unique StoreID: enter image description here

The rule I want to implement is the following:

A store cannot have have more than 1 manager per day.

If I try to add another manager to that store on the same day, I want the table to throw a error message.

I am thinking of something like this, how can I check that a manager is not already assigned to that store at the same day?

USE [StoreSystem]
GO
/****** Object:  Trigger [dbo].[CheckManagerAndDay]    Script Date: 2020-12-14 10:56:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[CheckManagerAndDay]
     ON [dbo].[EmployeeSchedule]
     AFTER INSERT, UPDATE
 AS
 BEGIN
     SET NOCOUNT ON;

     IF EXISTS (
         SELECT *
         FROM EmployeeSchedule
         JOIN Employee ON Employee.ID = EmployeeID
         JOIN LocalStore ON LocalStore.ID = StoreID
         WHERE IsManager = 1 and StoreID = ?? and Day = ??
     )
     BEGIN
         THROW 50000, 'Cannot have more than one manager on the same day.', 1;
     END
 END

Upvotes: 0

Views: 115

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I find this a bit painful to implement in SQL Server. Why? Because SQL Server triggers are set based. They are intended handle multiple rows. That means that duplicates can appear in two different ways:

  • There is an existing manager in the store and the new data inserts a second one.
  • There are no existing managers but the new data has two.

Ouch.

There is an alternative way -- that doesn't use triggers. But there is a complication. The isManager flag is redundantly stored in EmployeeSchedule. Then we use a foreign key to be sure it is correct and an unique index for the property you want:

alter table Employee add constraint unq_isManager_id
    unique (isManager, Id);

alter table EmployeeSchedule add constraint fk_employeeschedule_isManager_EmployeeId
    foreign key (isManager, EmployeeId) references Employee(isManager, EmployeeId);

And then the unique constraint that does what you want:

create unique index unq_employeeschedule_day_manager
    on employeeschedule(day)
    where isManager = 1;

Voila! No triggers!

You should also fix your data model so day is stored as a date rather than a string. Storing date/time values is strings is a really bad habit to get into.

Upvotes: 2

Related Questions