CREATE TABLE [dbo].[InternationalCustomsInspectionDate]
(
[InternationalCustomsInspectionDateID] [int] NOT NULL IDENTITY(1, 1),
[InternationalCustomsInspectionInformationFID] [int] NOT NULL,
[InspectionStartEstimatedDate] [datetime] NULL,
[InspectionStartActualDate] [datetime] NULL,
[InspectionEndEstimatedDate] [datetime] NULL,
[InspectionEndActualDate] [datetime] NULL
) ON [PRIMARY]
GO

CREATE trigger [dbo].[InternationalCustomsInspectionDateTaskFieldChanged] on [dbo].[InternationalCustomsInspectionDate]
after insert, update
as
set nocount on
    if( update( InspectionStartEstimatedDate ) or update( InspectionStartActualDate ) or
        update( InspectionEndEstimatedDate ) or update( InspectionEndActualDate )
    )
    begin
        update ToDo set
            ToDo.DueDate = dbo.udfTaskDueDate( ToDo.duetype, ToDo.duedays, dbo.GetInternationalCustomsInspectionDateByReportAndType( InternationalCustomsInspectionInformation.InternationalCustomsInspectionInformationID, DependencyDate.FieldName ) )
        from ToDo
        inner join InternationalCustomsInspectionInformation on InternationalCustomsInspectionInformation.OrdersFID = ToDo.OrderID
        inner join inserted on inserted.InternationalCustomsInspectionInformationFID = InternationalCustomsInspectionInformation.InternationalCustomsInspectionInformationID
        inner join DependencyDate on ToDo.DependencyDateID = DependencyDate.DependencyDateID
        where DependencyDate.TableName = 'InternationalCustomsInspectionDate'
    end
GO
ALTER TABLE [dbo].[InternationalCustomsInspectionDate] ADD CONSTRAINT [PK_InternationalCustomsInspectionDate] PRIMARY KEY NONCLUSTERED  ([InternationalCustomsInspectionDateID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[InternationalCustomsInspectionDate] ADD CONSTRAINT [IX_InternationalCustomsInspectionDate_InternationalCustomsInspectionInformationFID] UNIQUE CLUSTERED  ([InternationalCustomsInspectionInformationFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[InternationalCustomsInspectionDate] ADD CONSTRAINT [FK_InternationalCustomsInspectionDate_InternationalCustomsInspectionInformation] FOREIGN KEY ([InternationalCustomsInspectionInformationFID]) REFERENCES [dbo].[InternationalCustomsInspectionInformation] ([InternationalCustomsInspectionInformationID])
GO
GRANT SELECT ON  [dbo].[InternationalCustomsInspectionDate] TO [MssExec]
GRANT INSERT ON  [dbo].[InternationalCustomsInspectionDate] TO [MssExec]
GRANT DELETE ON  [dbo].[InternationalCustomsInspectionDate] TO [MssExec]
GRANT UPDATE ON  [dbo].[InternationalCustomsInspectionDate] TO [MssExec]
GO