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

GO

create trigger
TrigUpdateRowPriceList after update order 2 on
DBA.PriceList
referencing new as new_name old as old_name
for each row
begin
  update PriceKinds set
    LastChanged = Now(*) where
    PriceKind = new_name.PriceKindNo;
  update PriceKinds set
    LastChanged = Now(*) where
	PriceKind = old_name.PriceKindNo;
end
go

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

GO

create trigger
TrigInsertRowPriceList after insert order 1 on
DBA.PriceList
referencing new as new_name
for each row
begin
  update PriceKinds set
    LastChanged = Now(*) where
    PriceKind = new_name.PriceKindNo;
end
go

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

GO

create trigger
TrigDeleteRowPriceList after delete order 3 on
DBA.PriceList
referencing old as old_name
for each row
begin
  update PriceKinds set
    LastChanged = Now(*) where
	PriceKind = old_name.PriceKindNo;
end
go

-- PriceSeasonPeriods 
-- Last changed
IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st
where LCase(st.table_name)= LCase('PriceSeasons') and LCase(sc.column_name)= LCase('LastChanged') ) = 0 then
ALTER TABLE "PriceSeasons"
ADD "LastChanged" datetime not null default current timestamp
END IF

go

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

GO

create trigger
TrigUpdatePriceSeasonPeriods after update order 2 on
DBA.PriceSeasonPeriods
referencing new as new_name old as old_name
for each row
begin
  update PriceSeasons set
    LastChanged = Now(*) where
	SeasonNo = new_name.SeassonNo;
end
go

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

GO

create trigger
TrigInsertPriceSeasonPeriods after insert order 1 on
DBA.PriceSeasonPeriods
referencing new as new_name
for each row
begin
  update PriceSeasons set
    LastChanged = Now(*) where
	SeasonNo = new_name.SeassonNo;
end
go

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

GO

create trigger
TrigDeletePriceSeasonPeriods after delete order 1 on
DBA.PriceSeasonPeriods
referencing old as old_name
for each row
begin
  update PriceSeasons set
    LastChanged = Now(*) where
	SeasonNo = old_name.SeassonNo;
end
go

-- Export PriceSeasons
IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_ExportPriceSeasons')))
THEN Drop view v_ExportPriceSeasons
END IF

GO

create view DBA.v_ExportPriceSeasons
  as 
   select TableName='PriceSeasons',VersionNo='1',
    HotelID=(select IntStojnost from DBA.Registers where RegisterName = 'HotelID'),
	SeasonNo,
	Description,
	StartDate,
	EndDate
   from
    DBA.PriceSeasons where
    LastChanged > bvrExportData
go

IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_ExportPriceSeasonPeriods')))
THEN Drop view v_ExportPriceSeasonPeriods
END IF

GO

create view DBA.v_ExportPriceSeasonPeriods
  as 
   select TableName='PriceSeasonPeriods',VersionNo='1',
    HotelID=(select IntStojnost from DBA.Registers where RegisterName = 'HotelID'),
	PSP.N,
	PSP.StartDate,
	PSP.EndDate,
	PSP.SeassonNo
   from
    DBA.PriceSeasonPeriods PSP, DBA.PriceSeasons PS where
	PSP.SeassonNo=PS.SeasonNo and
    PS.LastChanged > bvrExportData
go


IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_ExportPriceList')))
THEN Drop view v_ExportPriceList
END IF

GO

create view DBA.v_ExportPriceList
  as 
   select TableName='PriceList',VersionNo='1',
    HotelID=(select IntStojnost from DBA.Registers where RegisterName = 'HotelID'),
	PL.TariffNo,
	PL.ClassNo,
	PL.PriceKindNo,
	PL.HumanKind,
	PL.AdultType,
	PL.SeasonNo,
	PL.PriceAll,
	PL.KodCurrency,
	PL.ExtraBed,
	PL.BoardPlan
   from
    DBA.PriceList PL, DBA.PriceKinds PK where
	PL.PriceKindNo=PK.PriceKind and
    PK.LastChanged > bvrExportData
go