CREATE TABLE [dbo].[XmlXtraStopMap]
(
[XmlXtraStopMapID] [int] NOT NULL IDENTITY(1, 1),
[XtraStopFID] [int] NOT NULL,
[OrderFID] [int] NOT NULL,
[XmlSystemFID] [int] NOT NULL,
[ExternalStopNumber] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ExternalTripNumber] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ExternalMoveNumber] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

create trigger [dbo].[XmlXtraStopMapDeleted] on [dbo].[XmlXtraStopMap]
for delete
as
    set nocount on
    insert into [XmlXtraStopDeletedMap]
    (
        [OrderFID],
        [XmlSystemFID],
        [ExternalStopNumber],
        [ExternalTripNumber],
        [ExternalMoveNumber]
    )
    select
        [OrderFID] = DELETED.OrderFID,
        [XmlSystemFID] = DELETED.[XmlSystemFID],
        [ExternalStopNumber] = DELETED.[ExternalStopNumber],
        [ExternalTripNumber] = DELETED.[ExternalTripNumber],
        [ExternalMoveNumber] = DELETED.[ExternalMoveNumber]
    from DELETED
    except
    select
        [OrderFID] = [XmlXtraStopDeletedMap].OrderFID,
        [XmlSystemFID] = DELETED.[XmlSystemFID],
        [ExternalStopNumber] = DELETED.[ExternalStopNumber],
        [ExternalTripNumber] = DELETED.[ExternalTripNumber],
        [ExternalMoveNumber] = DELETED.[ExternalMoveNumber]
    from [XmlXtraStopDeletedMap]
    inner join DELETED on
        [XmlXtraStopDeletedMap].[OrderFID] = DELETED.OrderFID and
        [XmlXtraStopDeletedMap].[XmlSystemFID] = DELETED.[XmlSystemFID] and
        [XmlXtraStopDeletedMap].[ExternalStopNumber] = DELETED.[ExternalStopNumber]
GO
ALTER TABLE [dbo].[XmlXtraStopMap] ADD CONSTRAINT [PK_XmlXtraStopMap] PRIMARY KEY CLUSTERED  ([XmlXtraStopMapID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[XmlXtraStopMap] ADD CONSTRAINT [IX_XmlXtraStopMap_Orders_ExternalStopNumber_XmlSystem] UNIQUE NONCLUSTERED  ([OrderFID], [ExternalStopNumber], [XmlSystemFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[XmlXtraStopMap] ADD CONSTRAINT [IX_XmlXtraStopMap_XtraStop_XmlSystem] UNIQUE NONCLUSTERED  ([XtraStopFID], [XmlSystemFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[XmlXtraStopMap] ADD CONSTRAINT [FK_XmlXtraStopMap_Orders] FOREIGN KEY ([OrderFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
ALTER TABLE [dbo].[XmlXtraStopMap] ADD CONSTRAINT [FK_XmlXtraStopMap_XmlSystem] FOREIGN KEY ([XmlSystemFID]) REFERENCES [dbo].[XmlSystem] ([XmlSystemID])
GO
ALTER TABLE [dbo].[XmlXtraStopMap] ADD CONSTRAINT [FK_XmlXtraStopMap_XtraStop] FOREIGN KEY ([XtraStopFID]) REFERENCES [dbo].[XtraStop] ([PriKey]) ON DELETE CASCADE
GO
GRANT SELECT ON  [dbo].[XmlXtraStopMap] TO [MssExec]
GRANT INSERT ON  [dbo].[XmlXtraStopMap] TO [MssExec]
GRANT DELETE ON  [dbo].[XmlXtraStopMap] TO [MssExec]
GRANT UPDATE ON  [dbo].[XmlXtraStopMap] TO [MssExec]
GO