CODEWITHSUNDEEP
sqlsql-servertriggers
glnxhjeh
glnxhjeh

Reputation: 25

Trigger which counts datediff

Hello for hobby purposes i am trying to create a C# application with a MS SQL Server database which reassembles a hotel system. I am now trying to create a SQL trigger which calculates a datedifference. A reservation may not be longer than 6 weeks(42 days). However, my trigger goes off even when placing reservations which have a datedifference lower than 42 days, even if the difference is 1 day. so I am not sure what I am doing wrong.

My trigger:

create trigger trigger_reservation
on reservation
after update, insert
as
if exists
(
select reservationid, DATEDIFF(dd,Startdate,Enddate)
from reservation
group by reservationid, enddate, startdate
having DATEDIFF(dd,Startdate,Enddate) > 42
)
begin
    raiserror('Error: Reservation may not be longer than 6 weeks',16, 1)
    rollback transaction
end

Upvotes: 1

Views: 1308

Answers (1)

peterm
peterm

Reputation: 92805

Triggers are expensive to run and maintain. This type of check can be accomplished by a simple CHECK CONSTRAINT

CREATE TABLE reservation (
  reservationid INT,
  startdate DATE,
  enddate DATE,
  -- ...
  CONSTRAINT reservation_dates_ck 
    CHECK(DATEDIFF(dd, startdate, enddate) < 43)
)

Here is a dbfiddle demo

And here's how you go about doing it with a trigger

CREATE TRIGGER trigger_reservation
ON reservation AFTER UPDATE, INSERT
AS
  IF EXISTS (
    SELECT *
      FROM inserted
     WHERE DATEDIFF(dd, startdate, enddate) > 42
  )  
  BEGIN  
    RAISERROR ('Error: Reservation may not be longer than 6 weeks', 16, 1);  
    ROLLBACK TRANSACTION;  
  RETURN   
END;

Here is a dbfiddle demo

Upvotes: 3

Related Questions