CREATE TABLE [dbo].[MoversConnectQueueName]
(
[MoversConnectQueueNameID] [int] NOT NULL IDENTITY(1, 1),
[QueueName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Used] [bit] NOT NULL CONSTRAINT [DF_MoversConnectQueueName_Used] DEFAULT ((0)),
[Retired] [bit] NOT NULL CONSTRAINT [DF_MoversConnectQueueName_Retired] DEFAULT ((0)),
[Status] [int] NOT NULL CONSTRAINT [DF_MoversConnectQueueName_Status] DEFAULT ((0)),
[Exception] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/**
*
*    This "for delete" trigger insures that illegal deletes for the MoversConnectQueueName table do not occur.
*    Deletes are only allowed if the Used ahd Retired flags are both set to true.
*/
create trigger [dbo].[MoversConnectQueueNameDeleteTrigger] on [dbo].[MoversConnectQueueName] for delete
as
    if( isnull( ( select top 1 1 from deleted where deleted.Retired = 0 or deleted.Used = 0 ), 0 ) != 0 )
    begin
        raiserror( 'Delete is not allowed because either the Used or Retired flags are both not set.', 16, 1 )
        rollback transaction
    end
GO

/**
*
*    This "for insert" trigger insures that illegal inserts for the MoversConnectQueueName table do not occur.
*    Inserts that set the Retired flag to true are not allowed.
*/
create trigger [dbo].[MoversConnectQueueNameInsertTrigger] on [dbo].[MoversConnectQueueName] for insert
as
    if( isnull(
    (
        select top 1 1
        from inserted
         where inserted.Retired != 0
    ), 0 ) = 1 )
    begin
        raiserror( 'Insert is not allowed because the Retired flag cannot be set on insert.', 16, 1 )
        rollback transaction
    end
    else if( isnull(
    (
        select top 1 1
        from inserted
         where inserted.Used != 0
    ), 0 ) = 1 )
    begin
        raiserror( 'Insert is not allowed because the Used flag cannot be set on insert.', 16, 1 )
        rollback transaction
    end
    else if( isnull(
    (
        select top 1 1
        from inserted
         where inserted.[Status] != 0
    ), 0 ) = 1 )
    begin
        raiserror( 'Insert is not allowed because the Status cannot be set on insert.', 16, 1 )
        rollback transaction
    end
    else if( isnull(
    (
        select top 1 1
        from inserted
         where inserted.Exception is not null
    ), 0 ) = 1 )
    begin
        raiserror( 'Insert is not allowed because the Exception cannot be set on insert.', 16, 1 )
        rollback transaction
    end
GO

/**
*
*    This "for update" trigger insures that illegal update for the MoversConnectQueueName table do not occur.
*    Updates that set the Used flag from true to false are not allowed.
*    Updates that change the QueueName are not allowed.
*/
create trigger [dbo].[MoversConnectQueueNameUpdateTrigger] on [dbo].[MoversConnectQueueName] for update
as
    if( isnull(
    (
        select top 1 1
        from deleted
        inner join inserted on inserted.MoversConnectQueueNameID = deleted.MoversConnectQueueNameID
        where deleted.Used = 1 and
            inserted.Used = 0
    ), 0 ) = 1 )
    begin
        raiserror( 'Update is not allowed because once the Used flag is set, it cannot be cleared.', 16, 1 )
        rollback transaction
    end
    else if( isnull(
    (
        select top 1 1
        from inserted
        where inserted.Used = 0 and
            inserted.Retired = 1

    ), 0 ) = 1 )
    begin
        raiserror( 'Update is not allowed because the Retired flag cannot be set before the Used flag has been set.', 16, 1 )
        rollback transaction
    end
    else if( isnull(
    (
        select top 1 1
        from deleted
        inner join inserted on inserted.MoversConnectQueueNameID = deleted.MoversConnectQueueNameID
        where deleted.QueueName != inserted.QueueName
    ), 0 ) = 1 )
    begin
        raiserror( 'Update is not allowed because once the QueueName is set, it cannot be changed.', 16, 1 )
        rollback transaction
    end
    else if( isnull(
    (
        select top 1 1
        from inserted
        where inserted.[Status] < 0 or
            inserted.[Status] > 2
    ), 0 ) = 1 )
    begin
        raiserror( 'Update is not allowed because Status can only be 0, 1 or 2.', 16, 1 )
        rollback transaction
    end
    else if( isnull(
    (
        select top 1 1
        from inserted
        where inserted.[Status] > 1 and
            inserted.Exception is null
    ), 0 ) = 1 )
    begin
        raiserror( 'Update is not allowed because Exception cannot be null if Status is > 1.', 16, 1 )
        rollback transaction
    end
GO
ALTER TABLE [dbo].[MoversConnectQueueName] ADD CONSTRAINT [PK_MoversConnectQueueName] PRIMARY KEY NONCLUSTERED  ([MoversConnectQueueNameID]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[MoversConnectQueueName] TO [MssExec]
GRANT INSERT ON  [dbo].[MoversConnectQueueName] TO [MssExec]
GRANT DELETE ON  [dbo].[MoversConnectQueueName] TO [MssExec]
GRANT UPDATE ON  [dbo].[MoversConnectQueueName] TO [MssExec]
GO