CREATE TABLE [dbo].[XtraStop]
(
[PriKey] [int] NOT NULL IDENTITY(1, 1),
[OrdPriKey] [int] NOT NULL,
[OriginDest] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Contact] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[County] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AgentPriKey] [int] NULL,
[StopNumber] [int] NOT NULL,
[Company] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL
) ON [PRIMARY]
GO

CREATE trigger [dbo].[XtraStopTaskFieldChanged] on [dbo].[XtraStop]
after update
as
set nocount on
    if( update( StartDate ) or update( EndDate ) )
    begin

        ;with XtraStopDependencyDates as
        (
            select
                DependencyDateID,
                FieldName
            from DependencyDate
            where DependencyDate.TableName = 'XtraStop'
        )
        update ToDo set ToDo.DueDate = dbo.udfTaskDueDate( ToDo.duetype, ToDo.duedays, dbo.GetXtraStopDateByOrderAndType( inserted.OrdPriKey, inserted.PriKey, XtraStopDependencyDates.FieldName ) )
        from inserted
        cross join XtraStopDependencyDates
        inner join ToDo on
            ToDo.OrderID = inserted.OrdPriKey and
            ToDo.Identifier = dbo.BuildExtraStopToDoIdentifier( OriginDest, StopNumber ) and
            ToDo.DependencyDateID = XtraStopDependencyDates.DependencyDateID
    end
GO
ALTER TABLE [dbo].[XtraStop] ADD CONSTRAINT [PK_XTRASTOP] PRIMARY KEY CLUSTERED  ([PriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[XtraStop] ADD CONSTRAINT [IX_XtraStop_UniqueColumns] UNIQUE NONCLUSTERED  ([OrdPriKey], [OriginDest], [StopNumber]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_XtraStop_OrdPriKey] ON [dbo].[XtraStop] ([OrdPriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[XtraStop] ADD CONSTRAINT [FK_XTRASTOP_AGENT] FOREIGN KEY ([AgentPriKey]) REFERENCES [dbo].[Agent] ([AgentPriKey])
GO
ALTER TABLE [dbo].[XtraStop] ADD CONSTRAINT [FK_XTRASTOP_ORDERS] FOREIGN KEY ([OrdPriKey]) REFERENCES [dbo].[Orders] ([PriKey])
GO
GRANT SELECT ON  [dbo].[XtraStop] TO [MssExec]
GRANT INSERT ON  [dbo].[XtraStop] TO [MssExec]
GRANT DELETE ON  [dbo].[XtraStop] TO [MssExec]
GRANT UPDATE ON  [dbo].[XtraStop] TO [MssExec]
GO