sql - Trigger: BEFORE INSERT -

how check if date being inserted or updated in table between two other dates table.

adicional info: have 2 tables:

activity:

• startdate date not null
• enddate date nullable

subactivity:

• subactivitydate date not null

when enddate not null check if: startdate ≤ subactivitydate ≤ enddate

when enddate null check if: startdate ≤ subactivitydate

i trying write before insert trigger figured out doesnt exist.

so do?

1. after insert?
2. instead of insert? looks better 1st solution
3. is possible check constraints?

how solve problem?

edit

i went check constraint + function:

constraint:

alter table subactivity     add constraint ck_subactivity_date check (dbo.ufnissubactivitydatevalid(activityid, subactivitydate) = 1); 

function:

create function ufnissubactivitydatevalid(@activityid [int], @subactivitydate [date]) returns [bit] begin     declare @startdate date, @enddate date;      select @startdate = startdate , @enddate = enddate      activity     activityid = @activityid;      if (@subactivitydate < @startdate )         return 0; -- out of range date      if (@enddate null)         return 1; -- date     else         if (@subactivitydate > @enddate)             return 0; -- out of range date      return 1; -- date end 

what best situation situation. constraint guarantees proper values rollsback entire transaction on 1 wrong value. triggers allow more control little more complex because of it.

create , populate table

if object_id('dbo.yourtable') not null     drop table yourtable;  create table yourtable (     id int identity(1,1) primary key,     startdate date not null,     subactivitydate date null,     enddate date null );  insert yourtable(startdate,subactivitydate,enddate) values  ('20150101',null,null),         ('20150101',null,null),              ('20150101',null,'20150201'),         ('20150101',null,'20150201'); 

constraint method:

alter table yourtable add constraint chk_date check (startdate <= subactivitydate , subactivitydate <= enddate);  update yourtable set subactivitydate =   case                             when id = 1 '20140101' --bad                             when id = 2 '20150102' --good                             when id = 3 '20140101' --bad                             when id = 4 '20150102' --good                         end  select * yourtable; 

since there @ least value not fit constraint, whole transaction rolled , result subactivitdate stays null.

results:

id          startdate  subactivitydate enddate ----------- ---------- --------------- ---------- 1           2015-01-01 null            null 2           2015-01-01 null            null 3           2015-01-01 null            2015-02-01 4           2015-01-01 null            2015-02-01 

trigger method(my preferred method)

create trigger trg_check_date on yourtable instead of update begin     update yourtable     set subactivitydate =   case                                 when inserted.subactivitydate >= inserted.startdate , ((inserted.enddate null) or inserted.subactivitydate <= inserted.enddate) inserted.subactivitydate                                 else null                             end     yourtable     inner join inserted      on yourtable.id = inserted.id end; go  update yourtable set subactivitydate =   case                             when id = 1 '20140101' --bad                             when id = 2 '20150102' --good                             when id = 3 '20140101' --bad                             when id = 4 '20150102' --good                         end  select * yourtable 

this method allows proper values , returns null improper ones. if wanted, export incorrect values inserted table log table know ones didn't work. or raise error message , list values didn't work. in short, have total control of situation.

results:

id          startdate  subactivitydate enddate ----------- ---------- --------------- ---------- 1           2015-01-01 null            null 2           2015-01-01 2015-01-02      null 3           2015-01-01 null            2015-02-01 4           2015-01-01 2015-01-02      2015-02-01