Tables [dbo].[XtraStop]
Properties
PropertyValue
CollationSQL_Latin1_General_CP1_CI_AS
Row Count (~)715
Created4:57:08 PM Thursday, September 7, 2006
Last Modified7:31:15 PM Thursday, August 29, 2024
Columns
NameData TypeMax Length (Bytes)Allow NullsIdentity
Cluster Primary Key PK_XTRASTOP: PriKeyPriKeyint4
No
1 - 1
Foreign Keys FK_XTRASTOP_ORDERS: [dbo].[Orders].OrdPriKeyIndexes IX_XtraStop_OrdPriKey: OrdPriKeyIndexes IX_XtraStop_UniqueColumns: OrdPriKey\OriginDest\StopNumberOrdPriKeyint4
No
Indexes IX_XtraStop_UniqueColumns: OrdPriKey\OriginDest\StopNumberOriginDestvarchar(1)1
No
Contactvarchar(50)50
Yes
Countyvarchar(32)32
Yes
Foreign Keys FK_XTRASTOP_AGENT: [dbo].[Agent].AgentPriKeyAgentPriKeyint4
Yes
Indexes IX_XtraStop_UniqueColumns: OrdPriKey\OriginDest\StopNumberStopNumberint4
No
Companyvarchar(50)50
Yes
StartDatedatetime8
Yes
EndDatedatetime8
Yes
StartTimedatetime8
Yes
EndTimedatetime8
Yes
Indexes Indexes
NameColumnsUniqueFill Factor
Cluster Primary Key PK_XTRASTOP: PriKeyPK_XTRASTOPPriKey
Yes
80
IX_XtraStop_UniqueColumnsOrdPriKey, OriginDest, StopNumber
Yes
80
IX_XtraStop_OrdPriKeyOrdPriKey80
Triggers Triggers
NameANSI Nulls OnQuoted Identifier OnOn
XtraStopTaskFieldChanged
Yes
Yes
After Update
Foreign Keys Foreign Keys
NameColumns
FK_XTRASTOP_AGENTAgentPriKey->[dbo].[Agent].[AgentPriKey]
FK_XTRASTOP_ORDERSOrdPriKey->[dbo].[Orders].[PriKey]
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
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
Uses
Used By