CREATE TABLE [dbo].[SysNumber]
(
[SysNumberID] [int] NOT NULL IDENTITY(1, 1),
[SysNumberType] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Sequence] [bigint] NOT NULL CONSTRAINT [DF_SysNumber_Sequence] DEFAULT ((0)),
[SequenceYear] [int] NULL,
[Tale] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_SysNumber_Tale] DEFAULT ('')
) ON [PRIMARY]
GO

/**
*
*    This "for delete" trigger insures that a SysNumber row is not allowed to be deleted.
*/
create trigger [dbo].[SysNumberDeleteTrigger] on [dbo].[SysNumber] for delete
as
    if( exists( select 1 from deleted left outer join inserted on inserted.SysNumberID = deleted.SysNumberID where inserted.SysNumberID is null ) )
    begin
        raiserror( 'A SysNumber row cannot be deleted, ever.', 16, 1 )
        rollback transaction
    end
GO

/**
*
*    This "for update" trigger insures that the SysNumberType column is not allowed to change and that
*    the SequenceYear column is never nulled out nor decremented in value.
*/
create trigger [dbo].[SysNumberUpdateTrigger] on [dbo].[SysNumber] for update
as
    if update( SysNumberType ) and exists ( select 1 from inserted inner join deleted on inserted.SysNumberID = deleted.SysNumberID where inserted.SysNumberType <> deleted.SysNumberType)
    begin
        raiserror ('SysNumberType cannot change.', 16, 1)
        rollback transaction
    end
    else if update( SequenceYear ) and exists ( select 1 from inserted inner join deleted on inserted.SysNumberID = deleted.SysNumberID where inserted.SequenceYear is null and deleted.SequenceYear is not null )
    begin
        raiserror ('SequenceYear cannot be decremented.', 16, 1)
        rollback transaction
    end
GO
ALTER TABLE [dbo].[SysNumber] ADD CONSTRAINT [PK_SysNumber] PRIMARY KEY CLUSTERED  ([SysNumberType]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[SysNumber] TO [MssExec]
GRANT INSERT ON  [dbo].[SysNumber] TO [MssExec]
GRANT DELETE ON  [dbo].[SysNumber] TO [MssExec]
GRANT UPDATE ON  [dbo].[SysNumber] TO [MssExec]
GO