CODEWITHSUNDEEP
c#sqllinq
Elham Azadfar
Elham Azadfar

Reputation: 737

Prevent Insert Or Update data in C# or SQL

I have two table Header and Item, that has one to many Relation.

Header Table

Id      StartDate   EndDate 
--------------------------------------- 
1       1999/1/1    1999/5/1        
2       2000/1/1    2000/4/1
3       2000/1/1    2000/5/1

and Item Table 

Id      HeaderRef   SLRef
-------------------------------------
101     1           201
102     2           201

How to Prevent to add Item with HeaderRef=3 and SLRef=201 because it has same SLRef, and the Header rows that HeaderRef referd to it has StartDate and EndDate that another Item with same SLRef Refered to Header in that Range.

Upvotes: 1

Views: 452

Answers (2)

Yawar Murtaza
Yawar Murtaza

Reputation: 3885

Presuming you are using MS SQL Server there are two approaches to achive what you are looking for:

1) Use trigger as suggested by other users. Trigger would be for INSERT / UPDATE that will check the date ranges and will allow new values to be added or raise error.

2) You can use composite primary key in ItemTable:

CREATE TABLE [dbo].[ItemTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [HeaderRef] [int] NOT NULL,
    [SLRef] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [HeaderRef] ASC,
    [SLRef] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Now this will put a constraint on the ItemTable and sql server WILL NOT allow duplicate combination of headerRef and SLRef int values (keys).

Back you your HeaderTable, you can put the unique constraint to stop duplicating the range of start and end dates 

CREATE TABLE [dbo].[HeaderTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [STARTDATE] [datetime] not NULL,
    [ENDDATE] [datetime] not NULL,
 CONSTRAINT [PK_HeaderTable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Now create unique index on HeaderTable for start and end dates.

/****** Object:  Index [IX_HeaderTable]    Script Date: 03/13/2017 12:24:51 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_HeaderTable] ON [dbo].[HeaderTable] 
(
    [ENDDATE] ASC,
    [STARTDATE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

You can also put another constraint on HeaderTable that will check if start date cant be before end date.

ALTER TABLE [dbo].[HeaderTable]  WITH CHECK ADD  CONSTRAINT [CheckEndLaterThanStart] CHECK  (([ENDDATE]>=[STARTDATE]))
GO

ALTER TABLE [dbo].[HeaderTable] CHECK CONSTRAINT [CheckEndLaterThanStart]
GO

Hope this helps!

Upvotes: 2

Mostafa Mohamed Ahmed
Mostafa Mohamed Ahmed

Reputation: 649

You Are Looking For a DML Trigger , in simple terms it's some sort of a sql function or procedure that is called automatically when the user try to change the data inside a database by adding or removing , Also the body of the trigger can contain some validation logic so it won't insert the data unless it meets a certain condition

Upvotes: 1

Related Questions