CODEWITHSUNDEEP
sql-server-2008t-sqltriggers
user2099269
user2099269

Reputation: 11

SQL Server 2008 trigger doesn't work, why?

I have a table called tblReservations with follwing columns:

reserv_ID (int), aptID (int), client_ID (int), 
start_date (datetime), end_date (datetime), 
details (nvarchar(max)), confirmation (bit)

What trigger should is to compare two dates start_date for new reservation and end_date for existing reservation in tblReservation for specific aptID.

If start_date < end_date trigger must prevent insertion of new reservation for that aptID.

I wrote this trigger:

CREATE TRIGGER NewReservation
    on tblReservations
    after insert
    as
    begin
        declare @aptID int
        declare @start_date datetime
        declare @end_date datetime

        select @aptID=aptID, @start_date=start_date from inserted
        select @end_date=end_date from tblReservations
        where aptID=@aptID

        if @end_date>@start_date
        BEGIN
            ROLLBACK TRANSACTION
        END
    end

Why does this trigger not work?

please help

Upvotes: 1

Views: 1066

Answers (2)

HLGEM
HLGEM

Reputation: 96658

Aside from the multiorw issue that others have brought up, you are likely not considering that there are many rows in the tblReservations for a particular apt id.

Plus you do not want to insert the record and then get rid of it, you want the record to not go in. therefore an instead of trigger is a better bet.

CREATE TRIGGER NewReservation
    ON tblReservations
    INSTEAD OF INSERT
    AS
    BEGIN

        INSERT tblReservations (<put field list here>)
        SELECT <put field list here>
        FROM inserted i
        JOIN (SELECT aptid, MAX(reservationid)AS reservationid FROM tblReservations GROUP BY aptid)  maxid 
            ON i.aptid = r.aptid
        JOIN    tblReservations r 
            ON r.reservationid = maxid.reservationid
        WHERE r.enddate<i.startdate


    END

Upvotes: 1

automatic
automatic

Reputation: 2737

The first problem I see is you are assuming inserted is a single row.

Try:

Select top 1 @aptID=bb.aptID, @start_date=start_date
from inserted as aa
join
(
  select Max(start_date) as Start_Date from inserted 
) as bb
on aa.Start_Date =bb.Start_Date

But you should think about changing your logic so that the record never gets inserted in the first place if the date is wrong.

Upvotes: 0

Related Questions