如果不在sql server 2008中插入数据，应如何插入? [英] How to insert data if not in between in sql server 2008?

问题描述

如果我有这样的事件表:

If I have a table of events like this:

    event_name     begin_date(pk) end_date(pk)
    ------------------------------------------
    holiday        2014-11-01     2014-11-05
    holiday        2014-11-10     2014-11-12
    big sale       2014-11-18     2014-11-25
    monthly sale   2014-11-28     2014-11-30

如果插入的数据的 begin_date 或 end_date 处于任何事件期间，如何防止插入数据?

How can I prevent inserting data if begin_date or end_date of the inserted data is in the period of any events?

示例:
该数据将不会被插入:

Example:
This data won't be inserted:

    holiday        2014-11-03     2014-11-08

此数据将被插入:

    holiday        2014-11-06     2014-11-09

有人可以帮我解决这个问题吗?

Can anyone help me solve this problem?

推荐答案

我始终认为，如果可以在数据库中约束某些内容，则应该这样做.您永远不会知道哪个开发人员将禁用触发器，或者绕过应用程序代码并直接运行插入，因此尽管触发器和业务逻辑很好，但这并不是万无一失的.

I always think that if something can be constrained in the database, it should be. You never know which developer is going to disable a trigger, or bypass application code and run the insert directly, so while triggers and business logic is good, it is not fool proof.

我要做的第一件事是将begin_date限制在end_date之前:

The first thing I would do is constrain begin_date to be before end_date:

CREATE TABLE dbo.T
(
    ID INT IDENTITY(1, 1) NOT NULL,
    Event_name VARCHAR(50) NOT NULL,
    begin_date DATE NOT NULL,
    end_date DATE NOT NULL
);
ALTER TABLE dbo.T ADD CONSTRAINT CHK_T_ValidDates CHECK (Begin_date <= end_date);

然后(如果您还没有的话)可以创建日历表(

Then (if you don't already have one) you can create a calendar table (which are incredibly useful anyway):

CREATE TABLE dbo.Calendar 
(
    Date DATE NOT NULL
);
CREATE UNIQUE CLUSTERED INDEX UQ_Calendar_Date ON dbo.Calendar (Date);
GO
INSERT dbo.Calendar (Date)
SELECT  TOP (7305) DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, '20000101')
FROM    sys.all_objects a, sys.all_objects;
GO

最后，您可以创建索引视图，以确保表中没有重复的日期:

Finally you can create an indexed view, to ensure that no dates are duplicated in your table:

CREATE VIEW dbo.TCheck
WITH SCHEMABINDING
AS
    SELECT  c.Date
    FROM    dbo.T
            INNER JOIN dbo.Calendar AS c
                ON c.Date >= t.begin_date 
                AND c.Date <= t.end_date;
GO
CREATE UNIQUE CLUSTERED INDEX UQ_TCheck_ID ON dbo.TCheck (Date);

在我运行的测试中(与触发器相比)，索引视图的性能要比触发器好约50％，但效果都不佳.不幸的是，有时数据完整性需要付出代价.

In the tests I ran (comparing to a trigger) the indexed view performed about 50% better than the trigger, but neither performed well. Unfortunately, sometimes data integrity has a cost.

这篇关于如果不在sql server 2008中插入数据，应如何插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！