时间戳

首页 > 解决方案 > 如何触发检查用户选择它的时间是否为空？

sql - 如何触发检查用户选择它的时间是否为空？

问题描述

ALTER TRIGGER [dbo].[Check_time] on [dbo].[Schedule] after insert
AS 

if exists ((select * from Schedule as S Join inserted as i on S.DDate=i.DDate AND S.ST_TIME=i.ST_TIME AND S.END_TIME=i.END_TIME))
BEGIN 
print('INVALID TIME')
ROLLBACK TRANSACTION 
return
END

*但是还有一个问题，用户可以在前一个用户的结束时间结束之前插入时间，我该如何解决？***

标签： sqlsql-servertriggers

解决方案

由于您使用了触发器，我将以触发器格式回答。使用 INSTEAD OF 而不是插入后。在这种情况下，您在设置记录之前检查表。使用 2 作为建议

ALTER TRIGGER [dbo].[Check_time]
   ON  [dbo].[Schedule]
   INSTEAD OF INSERT
AS 
BEGIN
    DECLARE @Count INT = 0
    SELECT @Count = COUNT(*) 
    FROM Schedule AS S 
    INNER JOIN inserted AS i ON S.DDate=i.DDate AND S.ST_TIME=i.ST_TIME AND S.END_TIME=i.END_TIME

    IF ISNULL(@Count , 0) = 0
    BEGIN 
        INSERT INTO [dbo].[Schedule] ([MID], [SID], [ST_TIME], [END_TIME], [DDate], [ST_ID])
        select [MID], [SID], [ST_TIME], [END_TIME], [DDate], [ST_ID] FROM inserted
    END 

END

推荐阅读