SQL Server : update trigger not between dates-CodePudding

I'm making a SQL instead of update trigger so It is not possible to have more than 1 promotion for the same store with time overlap.

My trigger looks like this:

create or alter trigger instead_update_promotion
on GHOST_PROMOTIONS
instead of update
as 
begin
    update GHOST_PROMOTIONS 
    set IDSTORE = i.IDSTORE, 
        STARTDATE = i.STARTDATE, 
        ENDDATE = i.ENDDATE, 
        TYPEOFPROMOTION = i.TYPEOFPROMOTION,
        STARTINGPRICE = i.STARTINGPRICE,
        FINALPRICE = i.FINALPRICE,
        PRICEREDUCTIONPERDAY = i.PRICEREDUCTIONPERDAY
    from 
        inserted i, GHOST_PROMOTIONS p
    where 
        i.IDPROMOTION = p.IDPROMOTION
        and not (i.IDPROMOTION <> p.IDPROMOTION and i.IDSTORE = p.IDSTORE and (i.STARTDATE between p.STARTDATE and p.ENDDATE or i.ENDDATE between p.STARTDATE and p.ENDDATE))
end

Explaining my logic:

First: i.IDPROMOTION <> p.IDPROMOTION -> so it doesn't compare with itself;

Second: i.IDSTORE = p.IDSTORE -> we only care about time overlap in the same store;

Third: (i.STARTDATE between p.STARTDATE and p.ENDDATE or i.ENDDATE between p.STARTDATE and p.ENDDATE) -> True if inserted date values are within a known date range

These 3 statements are written after a "and not" so if the 3 are True(meaning there exists a row different than itself with time overlap in the same store) it results in a False and the data isn't changed.

But this isn't working, it updated an overlapping date any idea why that is? Ty

CodePudding user response：

You really don't want to do this in one statement. It's harder; and you should fail the whole batch if any rows violate the rule. So first check INSERTED against the target table, then update if no violations are found. eg

create or alter trigger instead_update_promotion
on GHOST_PROMOTIONS
instead of update
as 
begin
    if exists (
    select *
    from inserted i, GHOST_PROMOTIONS p
    where i.IDPROMOTION <> p.IDPROMOTION 
      and i.IDSTORE = p.IDSTORE 
      and (i.STARTDATE between p.STARTDATE and p.ENDDATE or i.ENDDATE between p.STARTDATE and p.ENDDATE)
      )
      begin
         throw 51000, 'Cannont have overlapping promotions for the the same store', 1;
      end

      --update here
end