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
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
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