RiskMan

script.sql

use RiskMan;
go
set nocount, xact_abort on;
go

drop view if exists
	Risk.MyDatabaseRisks_V,
	Risk.AllRisks_V,
	Risk.AllDatabaseRisks_V;
go

drop procedure if exists
	Risk.ClaimDatabase,
	Risk.UnclaimDatabase;
go

drop table if exists
	Risk.DatabaseRisk,
	Risk.Risk,
	Risk.Category,
	Risk.[Database];
go

drop function if exists
	Risk.RiskNumberIsOneOrHasPredecessor;
go



drop schema if exists Risk;
go

/*
********************************************************************************
Create schema
********************************************************************************
*/

create schema Risk authorization dbo;
go


/*
********************************************************************************
Create tables, their indexes, and triggers
********************************************************************************
*/

create table Risk.Category (
	CategoryCode char(3) not null,
	Name nvarchar(50) not null,
	ParentCategoryCode char(3) not null
		constraint Risk_Category_ParentCategoryCode_DF default '',
	IsAnchorRow bit not null
		constraint Risk_Category_IsAnchorRow_DF default 0,
	IsDeleted bit not null
		constraint Risk_Category_IsDeleted_DF default 0,

	constraint UC_Risk_Category_PK
	primary key clustered (CategoryCode),

	constraint U__Risk_Category_AK1
	unique nonclustered (Name),

	constraint FK_Risk_Category_Has_Parent_Category
	foreign key (ParentCategoryCode)
	references Risk.Category (CategoryCode),

	constraint CK_Risk_Category_IsAnchorRow_Valid
	check (
		CategoryCode != '' and IsAnchorRow = 0 or
		CategoryCode = '' and IsAnchorRow = 1
	),

	constraint CK_Risk_Category_EnforceEmptyAnchorRow
	check (
		IsAnchorRow = 0 or
		IsAnchorRow = 1 and
		CategoryCode = '' and
		Name = '' and
		ParentCategoryCode = ''
	)
);
go

create or alter trigger Risk.Category_InsteadOf_Deleted_TR
	on Risk.Category
	instead of delete
as
begin
	set nocount, xact_abort on;

	update c
	set c.IsDeleted = 1
	from Risk.Category c
	join deleted d on c.CategoryCode = d.CategoryCode;
end
go

create unique nonclustered index IU_Risk_Category_IsAnchorRow
	on Risk.Category (IsAnchorRow)
	where IsAnchorRow = 1;
go

create table Risk.Risk (
	RiskCode char(15) not null,
	CategoryCode char(3) not null,
	RiskNumber int not null,
	Name nvarchar(50) not null,
	Notes nvarchar(4000) not null
		constraint Risk_Risk_Notes_DF default N'',
	IsDeleted bit not null
		constraint Risk_Risk_IsDeleted_DF default 0,

	constraint UC_Risk_Risk_PK
	primary key clustered (RiskCode),

	constraint U__Risk_Risk_AK1
	unique nonclustered (Name),

	constraint FK_Risk_Category_Has_Risk
	foreign key (CategoryCode)
	references Risk.Category (CategoryCode),

	constraint CK_Risk_Risk_RiskNumber_WithinRange
	check (RiskNumber between 0 and 99999),

	constraint CK_Risk_Risk_RiskCode_Consistent_With_CategoryCode_RiskNumber
	check (
		RiskCode = convert(char(15), concat('R-', ltrim(rtrim(CategoryCode)), '-', format(RiskNumber, 'D5')))
	)
);
go

create unique nonclustered index IU_Risk_Risk_CategoryCode_RiskNumber
	on Risk.Risk (CategoryCode, RiskNumber);
go

create or alter trigger Risk.Risk_InsteadOf_Deleted_TR
	on Risk.Risk
	instead of delete
as
begin
	set nocount, xact_abort on;

	update r
	set r.IsDeleted = 1
	from Risk.Risk r
	join deleted d on r.RiskCode = d.RiskCode;
end
go

create table Risk.[Database] (
	DatabaseId int identity(1,1) not null,
	Name nvarchar(200) not null,
	ClaimedBy nvarchar(200) not null
		constraint Risk_Database_ClaimedBy_DF default '',
	IsDeleted bit not null
		constraint Risk_Database_IsDeleted_DF default 0,

	constraint UC_Risk_Database_PK
	primary key clustered (DatabaseId),

	constraint U__Risk_Database_AK1
	unique nonclustered (Name)
);
go

create or alter trigger Risk.Database_InsteadOf_Deleted_TR
	on Risk.[Database]
	instead of delete
as
begin
	set nocount, xact_abort on;

	update t
	set t.IsDeleted = 1
	from Risk.[Database] t
	join deleted d on t.DatabaseId = d.DatabaseId;
end
go

create table Risk.DatabaseRisk (
	DatabaseId int not null,
	RiskCode char(15) not null,
	IsApplicable bit not null
		constraint Risk_DatabaseRisk_IsApplicable_DF default 0,
	Notes nvarchar(4000) not null
		constraint Risk_DatabaseRisk_Notes_DF default N'',

	constraint UC_Risk_DatabaseRisk_PK
	primary key clustered (DatabaseId, RiskCode)
);
go



/*
********************************************************************************
Create CHECK constraint functions
********************************************************************************
*/

create or alter function Risk.RiskNumberIsOneOrHasPredecessor (
	@CategoryCode char(3),
	@RiskNumber int
)
returns bit
as
begin
	if @RiskNumber = 1 return 1;

	if exists (
		select top 1 1
		from Risk.Risk
		where
			CategoryCode = @CategoryCode and
			RiskNumber = (@RiskNumber - 1)
	)
	begin
		return 1;
	end

	return 0;
end
go



/*
********************************************************************************
Alter tables to add CHECK constraints
********************************************************************************
*/

alter table Risk.Risk add
	constraint CK_Risk_Risk_RiskNumber_Is_1_Or_Has_Predecessor
	check (Risk.RiskNumberIsOneOrHasPredecessor(CategoryCode, RiskNumber) = 1);
go



/*
********************************************************************************
Create stored procedure APIs
********************************************************************************
*/
create or alter procedure Risk.ClaimDatabase
	@Name nvarchar(200)
as
begin
	if not exists (
		select top 1 1
		from Risk.[Database]
		where Name = @Name
	)
	begin
		;throw 50000, N'No database with that name exists', 1;
	end

	if exists (
		select top 1 1
		from Risk.[Database]
		where
			Name = @Name and
			ClaimedBy = suser_sname()
	)
	begin
		return 0;
	end

	update t
	set t.ClaimedBy = suser_sname()
	from Risk.[Database] t
	where
		t.Name = @Name and
		t.ClaimedBy = '';

	if @@rowcount != 1 throw 50000, N'That database has already been claimed', 1;

	return 0;
end
go

create or alter procedure Risk.UnclaimDatabase
	@Name nvarchar(200)
as
begin
	if not exists (
		select top 1 1
		from Risk.[Database]
		where Name = @Name
	)
	begin
		;throw 50000, N'No database with that name exists', 1;
	end

	if exists (
		select top 1 1
		from Risk.[Database]
		where
			Name = @Name and
			ClaimedBy = ''
	)
	begin
		return 0;
	end

	update t
	set t.ClaimedBy = ''
	from Risk.[Database] t
	where
		t.Name = @Name and
		t.ClaimedBy = suser_sname();

	return 0;
end
go



/*
********************************************************************************
Create views
********************************************************************************
*/
create or alter view Risk.AllRisks_V
as
select top 100 percent
	c.Name as CategoryName,
	r.RiskCode,
	r.Name as RiskName
from Risk.Risk r
join Risk.Category c on r.CategoryCode = c.CategoryCode
order by
	c.CategoryCode,
	r.RiskCode;

go

create or alter view Risk.AllDatabaseRisks_V
as
select top 100 percent
	db.Name,
	db.ClaimedBy,
	r.CategoryName,
	r.RiskCode,
	r.RiskName
from Risk.[Database] db
cross apply (
	select
		c.CategoryCode,
		c.Name as CategoryName,
		r.RiskCode,
		r.Name as RiskName
	from Risk.Risk r
	join Risk.Category c on r.CategoryCode = c.CategoryCode
) r
where
	db.IsDeleted = 0
order by
	db.Name,
	r.CategoryCode,
	r.RiskCode;

go

create or alter view Risk.MyDatabaseRisks_V
as
select *
from Risk.AllDatabaseRisks_V
where
	ClaimedBy = suser_sname();

go


/*
********************************************************************************
Insert reference data
********************************************************************************
*/

insert into Risk.Category (CategoryCode, Name, IsAnchorRow) values ('', '', 1);
go

insert into Risk.Category (CategoryCode, Name)
values
	('A', 'Availability'),
	('P', 'Performance'),
	('S', 'Scalability');
go

insert into Risk.Risk (RiskCode, CategoryCode, RiskNumber, Name)
values
	('R-A-00001', 'A', 1, 'Availability risk #1'),
	('R-A-00002', 'A', 2, 'Availability risk #2'),
	('R-P-00001', 'P', 1, 'Performance risk #1'),
	('R-P-00002', 'P', 2, 'Performance risk #2'),
	('R-S-00001', 'S', 1, 'Scalability risk #1'),
	('R-S-00002', 'S', 2, 'Scalability risk #2');
go

insert into Risk.[Database] (Name)
values
	('DB1'),
	('DB2');
go

select * from Risk.Category;
select * from Risk.Risk;
select * from Risk.[Database];
go
select * from Risk.AllRisks_V;
select * from Risk.AllDatabaseRisks_V;
go

exec Risk.ClaimDatabase @Name = 'DB1';
exec Risk.ClaimDatabase @Name = 'DB1';
go
select * from Risk.AllDatabaseRisks_V;
select * from Risk.MyDatabaseRisks_V;
go
exec Risk.UnclaimDatabase @Name = 'DB1';
exec Risk.UnclaimDatabase @Name = 'DB1';
go