CREATE TABLE [dbo].[History]
(
[HistoryID] [uniqueidentifier] NOT NULL,
[ReportID] [uniqueidentifier] NOT NULL,
[SnapshotDataID] [uniqueidentifier] NOT NULL,
[SnapshotDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TRIGGER [dbo].[History_Notifications] ON [dbo].[History]
AFTER INSERT
AS
insert
into [dbo].[Event]
([EventID], [EventType], [EventData], [TimeEntered])
select NewID(), 'ReportHistorySnapshotCreated', inserted.[HistoryID], GETUTCDATE()
from inserted

GO
CREATE TRIGGER [dbo].[HistoryDelete_SnapshotRefcount] ON [dbo].[History]
AFTER DELETE
AS
UPDATE [dbo].[SnapshotData]
SET [PermanentRefcount] = [PermanentRefcount] - 1
FROM [SnapshotData] SD INNER JOIN deleted D on SD.[SnapshotDataID] = D.[SnapshotDataID]

GO
ALTER TABLE [dbo].[History] ADD CONSTRAINT [PK_History] PRIMARY KEY NONCLUSTERED ([HistoryID]) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [IX_History] ON [dbo].[History] ([ReportID], [SnapshotDate]) ON [PRIMARY]
GO
GRANT REFERENCES ON  [dbo].[History] TO [RSExecRole]
GRANT SELECT ON  [dbo].[History] TO [RSExecRole]
GRANT INSERT ON  [dbo].[History] TO [RSExecRole]
GRANT DELETE ON  [dbo].[History] TO [RSExecRole]
GRANT UPDATE ON  [dbo].[History] TO [RSExecRole]
GO