CODEWITHSUNDEEP
sql-server
vicnoob
vicnoob

Reputation: 1183

Trigger to compare date SQL Server

I am new in SQL Server and doing a homework.

I have a table Booking_Details(Booking_ID, Date_From, Date_To, Room_Number, Hotel_ID)

I want to create a trigger to compare Date_From and Date_to before inserting or updating on database. I am trying a trigger like this

CREATE TRIGGER check_date ON Booking_Details
FOR UPDATE, INSERT
AS 
IF (UPDATE(Date_To) OR UPDATE(Date_From))
BEGIN
DECLARE @Date_From DATE, @Date_To DATE
SET @Date_From=(SELECT Date_From FROM Booking_Details)
SET @Date_To=(SELECT Date_To FROM Booking_Details)
IF(@Date_From>@Date_To)
    BEGIN
    PRINT "Date To must larger than Date From"
    END
    END

But when i test it return

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Upvotes: 1

Views: 4601

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

There are several flaws...

You have to change this

SET @Date_From=(SELECT Date_From FROM Booking_Details)
SET @Date_To=(SELECT Date_To FROM Booking_Details)

to this

SET @Date_From=(SELECT Date_From FROM inserted)
SET @Date_To=(SELECT Date_To FROM inserted)

The table 'inserted' is a structurally identical "table" containing the rows of the actual process.

Your attempt to set the variable's value with =(SELECT Date_From FROM inserted) will work with one single row only!

BUT

You must be aware, that a trigger must be able to deal with more than one row at once. This approach would break, if you insert or update more than one row...

Are you allowed to use a CHECK CONSTRAINT instead of this trigger? This would be much easier...

Maybe a bit simpler like this:

CREATE TRIGGER check_date ON Booking_Details
FOR UPDATE, INSERT
AS 
BEGIN
IF (UPDATE(Date_To) OR UPDATE(Date_From)) AND EXISTS(SELECT 1 FROM inserted WHERE Date_From>DateTo)
    --Do something (I'd suggest RAISERROR)

END

IF EXISTS(SELECT ...) will be true, if any of the inserted/updated rows is invalid. You might even let the (UPDATE(Date_To) OR UPDATE(Date_From)) away...

About RAISERROR find details here

Upvotes: 4

Related Questions