SQL Server 2012每当插入新记录时，将DATEDIFF插入列触发器 [英] SQL Server 2012 Insert DATEDIFF into column trigger whenever a new record is inserted

问题描述

表

CREATE TABLE CurrentApplication
(
    StartDate       datetime        NOT NULL,
    EndDate         datetime        NOT NULL,
    NoOfDays        integer,
    StaffID         integer         NOT NULL,
    AppStatus       varchar(30)     NOT NULL    DEFAULT 'PENDING'
)

触发

CREATE TRIGGER InsertNoOfDays ON CurrentApplication
AFTER INSERT
AS
BEGIN
    DECLARE @temp INT

    SELECT @temp = DATEDIFF(day, EndDate, StartDate)
    FROM inserted

    INSERT INTO CurrentApplication(NoOfDays) VALUES (@temp)

    --SELECT StaffID = inserted.StaffID
    --FROM inserted

 --   INSERT INTO CurrentApplication(NoOfDays)
 --       SELECT Datediff(day, EndDate, StartDate)
 --       FROM inserted;
END

错误消息：

消息515，级别16，状态2，过程InsertNoOfDays，第10行

无法将值NULL插入表'b $ b'StaffPortalDB.dbo'的StartDate'列中.CurrentApplication';列不允许为空。
INSERT失败。该语句已终止。

Msg 515, Level 16, State 2, Procedure InsertNoOfDays, Line 10
Cannot insert the value NULL into column 'StartDate', table 'StaffPortalDB.dbo.CurrentApplication'; column does not allow nulls. INSERT fails. The statement has been terminated.

我要做的是我有一个表 CurrentApplication ，我希望每当用户插入新行时，都会自动填充 NoOfDays 列，并以开始日期和结束日期的日期差为准。

What I'm trying to do is I have a table CurrentApplication and I want the NoOfDays column to automatically be populated whenever a user inserts a new row, with the date difference of start day and end day.

推荐答案

IF Sql服务器
尝试插入一些默认值或虚拟值，因为其不为空列

类似这样的东西：

CREATE TRIGGER InsertNoOfDays ON CurrentApplication
AFTER INSERT
AS
BEGIN
    DECLARE @temp INT

    SELECT @temp = coalesce(DATEDIFF(day, EndDate, StartDate),0) --Default 0
    FROM inserted

    INSERT INTO CurrentApplication(NoOfDays) VALUES (@temp)

    --SELECT StaffID = inserted.StaffID
    --FROM inserted

 --   INSERT INTO CurrentApplication(NoOfDays)
 --       SELECT Datediff(day, EndDate, StartDate)
 --       FROM inserted;
END

这篇关于SQL Server 2012每当插入新记录时，将DATEDIFF插入列触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！