IF (EXISTS(Select * from sys.systrigger where LCase(trigger_name) = LCase('TrigGuestsDates')))
THEN Drop trigger TrigGuestsDates
END IF

GO

create trigger "TrigGuestsDates" after insert,update order 1
on "DBA".Guests
referencing new as GSNEW
for each row
begin
declare @PPDate date;
declare @BDate date;
set @PPDate=FloatToDate(GSNEW.PassportDate);
set @BDate=(if GSNEW.EGN is not null and Length(GSNEW.EGN)>5 and GSNEW.HumanKind=1 and GSNEW.IsEgnEnch=1 then
EGNToDate(GSNEW.EGN)
else FloatToDate(GSNEW.BirthDate)
endif);
/*if GSNEW.EGN<>'' then
if CheckEGN(GSNEW.EGN)=1 then
update Guests set IsEgnEnch=1 where GuestNum=GSNEW.GuestNum
else
update Guests set IsEgnEnch=0 where GuestNum=GSNEW.GuestNum
end if
end if
;*/
if GSNEW.PassportDateDt is null or GSNEW.BirthDateDt is null
or @PPDate<>GSNEW.PassportDateDt or @BDate<>GSNEW.BirthDateDt then
update Guests set BirthDateDt=@BDate,BirthDate=DateToFloat(@BDate),
PassportDateDt=@PPDate
where GuestNum=GSNEW.GuestNum
end if
end

