CREATE TABLE [dbo].[LaborRatingSchedule]
(
[LaborRatingScheduleID] [int] NOT NULL IDENTITY(1, 1),
[LaborRatingGroupFID] [int] NOT NULL,
[LaborRatingDayFID] [int] NOT NULL,
[StartTime] [datetime] NOT NULL,
[LaborRatingTypeFID] [int] NOT NULL
) ON [PRIMARY]
GO

/*
* Project the change to LaborRatingScheduleQuery
*/
CREATE TRIGGER [dbo].[LaborRatingSchedule_PushChangesAfterInsert]
    ON [dbo].[LaborRatingSchedule]
    AFTER insert
    AS
    begin
        --Admin (the source of this data) will only insert one record at a time.  

        if 1 = (select count(*) from inserted)
        begin
            declare @theLaborRatingDayID int
            declare @theLaborRatingGroupID int

            select
                @theLaborRatingGroupID = inserted.LaborRatingGroupFID,
                @theLaborRatingDayID = inserted.LaborRatingDayFID
            from inserted
            
            exec PushLaborRatingScheduleDataToQuery
                @theLaborRatingGroupID,
                @theLaborRatingDayID
        end
    end
GO


/*
* Project the change to LaborRatingScheduleQuery
*/
CREATE TRIGGER [dbo].[LaborRatingSchedule_PushChangesAfterUpdate]
    ON [dbo].[LaborRatingSchedule]
    AFTER update
    AS
    begin
        --Admin (the source of this data) will only insert one record at a time.  

        if 1 = (select count(*) from inserted)
        begin
            declare @theLaborRatingDayID int
            declare @theLaborRatingGroupID int

            select
                @theLaborRatingGroupID = inserted.LaborRatingGroupFID,
                @theLaborRatingDayID = inserted.LaborRatingDayFID
            from inserted
            
            exec PushLaborRatingScheduleDataToQuery
                @theLaborRatingGroupID,
                @theLaborRatingDayID

            declare @thePreviousLaborRatingDayID int
            select @thePreviousLaborRatingDayID = deleted.LaborRatingDayFID from deleted
            if @thePreviousLaborRatingDayID <> @theLaborRatingDayID
            begin
                exec PushLaborRatingScheduleDataToQuery
                    @theLaborRatingGroupID,
                    @thePreviousLaborRatingDayID
            end
        end
    end
GO

/*
* Project the change to LaborRatingScheduleQuery
*/
CREATE TRIGGER [dbo].[LaborRatingSchedule_RenormalizeDataForGroupAndDayAfterDelete]
    ON [dbo].[LaborRatingSchedule]
    AFTER delete
    AS
    begin
        --Admin (the source of this data) will only insert one record at a time.  

        if 1 = (select count(*) from deleted)
        begin
            declare @theLaborRatingDayID int
            declare @theLaborRatingGroupID int
            
            select
                @theLaborRatingGroupID = deleted.LaborRatingGroupFID,
                @theLaborRatingDayID = deleted.LaborRatingDayFID
            from deleted
            
            exec PushLaborRatingScheduleDataToQuery
                @theLaborRatingGroupID,
                @theLaborRatingDayID
        end
    end
GO
ALTER TABLE [dbo].[LaborRatingSchedule] ADD CONSTRAINT [PK_LaborRatingSchedule] PRIMARY KEY NONCLUSTERED  ([LaborRatingScheduleID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LaborRatingSchedule] ADD CONSTRAINT [IX_LaborRatingSchedule_LaborRatingGroup_LaborRatingDay_StartTime] UNIQUE CLUSTERED  ([LaborRatingGroupFID], [LaborRatingDayFID], [StartTime]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LaborRatingSchedule] ADD CONSTRAINT [FK_LaborRatingSchedule_LaborRatingDay] FOREIGN KEY ([LaborRatingDayFID]) REFERENCES [dbo].[LaborRatingDay] ([LaborRatingDayID])
GO
ALTER TABLE [dbo].[LaborRatingSchedule] ADD CONSTRAINT [FK_LaborRatingSchedule_LaborRatingGroup] FOREIGN KEY ([LaborRatingGroupFID]) REFERENCES [dbo].[LaborRatingGroup] ([LaborRatingGroupID]) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[LaborRatingSchedule] ADD CONSTRAINT [FK_LaborRatingSchedule_LaborRatingType] FOREIGN KEY ([LaborRatingTypeFID]) REFERENCES [dbo].[LaborRatingType] ([LaborRatingTypeID])
GO
GRANT SELECT ON  [dbo].[LaborRatingSchedule] TO [MssExec]
GRANT INSERT ON  [dbo].[LaborRatingSchedule] TO [MssExec]
GRANT DELETE ON  [dbo].[LaborRatingSchedule] TO [MssExec]
GRANT UPDATE ON  [dbo].[LaborRatingSchedule] TO [MssExec]
GO