sql-server - 在 SQL Server 中创建比较日期的触发器

Question

我对触发器还很陌生，似乎无法理解它们。假设我这里有两个表:

CREATE TABLE Project 
(
    id INT NOT NULL IDENTITY(1, 1),
    startDate DATETIME,
    endDate DATETIME
);

CREATE TABLE Work 
(
    date DATETIME,
    projectId INT
);

我插入一些数据:

INSERT INTO Project VALUES ('2017-04-18', '2017-05-01'); /*id = 1*/
INSERT INTO Work VALUES ('2017-04-17', 1);

假设只有 1 个项目带有 id = 1 ，这应该进展顺利。但是，我的工作在项目开始前一天开始并没有多大意义(在这种情况下不是)。我将如何创建一个基本上说 date cannot be < startDate OR > endDate 的触发器？

Answer 1

像这样的东西应该可以工作:

CREATE TRIGGER t_CheckInterval ON dbo.Work
   AFTER UPDATE, INSERT
AS 

IF NOT EXISTS (
    --if no records are returned then work date lies outside the project
    -- (start, end) interval
    SELECT 1
    FROM inserted AS i
    JOIN Project AS p 
       ON p.Id = i.projectId AND i.[date] BETWEEN p.startDate AND p.endDate 
)
BEGIN
    RAISERROR ('Error: Your error message here.', 16, 1)
    ROLLBACK TRANSACTION
END

GO