How to make trigger to check that time that user choose it is empty or not?

Question

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 

*but there is another problem that the user can insert time before the end time for the previous user has been ended , how can i solve it?***

Answer 1

Since you used Trigger I will answer in Trigger format. Use INSTEAD OF instead of after insert. In this case, you check the table before setting a record. Use 2 as a suggestion

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

Answer 2

Your trigger is just checking for duplicated values. You are better off with a unique constraint:

alter table Schedule add constraint unq_schedule_ddate_st_time_end_time
    unique (ddate, st_time, end_time);

Answer 3

1. Problem clarification

After clarifications by OP in comments it become clear that the main goal is checking overlapping time periods.

E.g.

• combination of periods (01:00:00-02:00:00, 02:01:00-03:00:00) - is allowed in the same day
• (01:00:00-05:00:00, 02:01:00-03:00:00) or (01:00:00-03:00:00, 02:00:00-04:00:00) - are disallowed

(I hope that I understood correctly)

Checking for overlapping periods is typical task and I even found existing answer on SO: Date range overlapping check constraint

2. Proposed solution

With a little modification I can propose this solution: http://sqlfiddle.com/#!18/e2146/2

CREATE TABLE Schedule (
  ID BIGINT IDENTITY PRIMARY KEY,
  DDate date,
  ST_TIME time,
  END_TIME time
);

-- UNIQUE CONSTRAINT
CREATE UNIQUE INDEX UI_DDATE_ST_TIME_END_TIME ON Schedule(DDATE, ST_TIME, END_TIME);

-- CONSTRAINT FOR OVERLAPPING PERIODS
CREATE FUNCTION [dbo].[fn_chkOverlapping]
(
  @DDate AS DATE,
  @ST_TIME AS TIME,
  @END_TIME time
)
RETURNS BIT
AS 
BEGIN
 RETURN (
  SELECT 
  CASE
     WHEN @ST_TIME>@END_TIME THEN 0
     WHEN EXISTS(
          SELECT 1 FROM [dbo].[Schedule]
            WHERE
                @DDATE = DDATE
                AND @END_TIME >= ST_TIME
                AND END_TIME >= @ST_TIME
     ) THEN 0
     ELSE 1
  END
 )
END;

ALTER TABLE [dbo].[Schedule]  WITH CHECK ADD CONSTRAINT [CK_Overlapping] CHECK  ([dbo].[fn_chkOverlapping]([DDate],[ST_TIME],[END_TIME])=0);

3. Alternatives

It's absolutely possible to do this using triggers.

I myself prefer to define constraints by CREATE CONSTRAINT than other object types.

It's a question of taste not of necessity.