Tables [dbo].[SysNumber]
Properties
PropertyValue
CollationSQL_Latin1_General_CP1_CI_AS
Row Count (~)6
Created10:36:58 AM Monday, March 30, 2015
Last Modified7:28:26 PM Tuesday, September 24, 2024
Columns
NameData TypeMax Length (Bytes)Allow NullsIdentityDefault
SysNumberIDint4
No
1 - 1
Cluster Primary Key PK_SysNumber: SysNumberTypeSysNumberTypechar(3)3
No
Sequencebigint8
No
((0))
SequenceYearint4
Yes
Talevarchar(30)30
No
('')
Indexes Indexes
NameColumnsUnique
Cluster Primary Key PK_SysNumber: SysNumberTypePK_SysNumberSysNumberType
Yes
Triggers Triggers
NameANSI Nulls OnQuoted Identifier OnOn
SysNumberDeleteTrigger
Yes
Yes
After Delete
SysNumberUpdateTrigger
Yes
Yes
After Update
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
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
Uses