136 lines
5.0 KiB
Transact-SQL
136 lines
5.0 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
/****** Object: Table [dbo].[Resource] Script Date: 9/22/2014 1:56:50 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
begin transaction
|
|
|
|
begin try
|
|
exec sp_rename 'Resource.PK_Resource', 'PK_Resource1'
|
|
|
|
CREATE TABLE [dbo].[tmp_Resource](
|
|
[Id] [uniqueidentifier] NOT NULL,
|
|
[FirstName] [nvarchar](250) NOT NULL,
|
|
[LastName] [nvarchar](250) NOT NULL,
|
|
[IsActiveEmployee] [bit] NOT NULL,
|
|
CONSTRAINT [PK_Resource] PRIMARY KEY CLUSTERED
|
|
(
|
|
[Id] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
|
|
CREATE TABLE [dbo].[Resource2ExpenditureCategories](
|
|
[ResourceId] [uniqueidentifier] NOT NULL,
|
|
[EpenditureCategoryId] [uniqueidentifier] NOT NULL,
|
|
CONSTRAINT [PK_Resource2ExpenditureCategories] PRIMARY KEY CLUSTERED
|
|
(
|
|
[ResourceId] ASC,
|
|
[EpenditureCategoryId] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
|
|
CREATE TABLE [dbo].[Resource2Teams](
|
|
[ResourceId] [uniqueidentifier] NOT NULL,
|
|
[TeamId] [uniqueidentifier] NOT NULL,
|
|
CONSTRAINT [PK_Resource2Teams] PRIMARY KEY CLUSTERED
|
|
(
|
|
[ResourceId] ASC,
|
|
[TeamId] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
|
|
insert into tmp_Resource
|
|
select * from Resource
|
|
|
|
insert into Resource2ExpenditureCategories
|
|
select * from ResourceExpenditureCategories
|
|
|
|
insert into Resource2Teams
|
|
select * from ResourceTeams
|
|
|
|
ALTER TABLE [dbo].[Resource] DROP CONSTRAINT [DF_Resource_IsActiveEmployee]
|
|
ALTER TABLE [dbo].[ResourceTeams] DROP CONSTRAINT [FK_ResourceTeams_Team]
|
|
ALTER TABLE [dbo].[ResourceTeams] DROP CONSTRAINT [FK_ResourceTeams_Resource]
|
|
ALTER TABLE [dbo].[ResourceExpenditureCategories] DROP CONSTRAINT [FK_ResourceExpenditureCategories_Resource]
|
|
ALTER TABLE [dbo].[ResourceExpenditureCategories] DROP CONSTRAINT [FK_ResourceExpenditureCategories_Expenditure_Category]
|
|
|
|
DROP TABLE [dbo].[ResourceExpenditureCategories]
|
|
DROP TABLE [dbo].[ResourceTeams]
|
|
DROP TABLE [dbo].[Resource]
|
|
|
|
exec sp_rename 'dbo.tmp_Resource', 'Resource'
|
|
|
|
ALTER TABLE [dbo].[Resource] ADD CONSTRAINT [DF_Resource_IsActiveEmployee] DEFAULT ((0)) FOR [IsActiveEmployee]
|
|
|
|
ALTER TABLE [dbo].[Resource2ExpenditureCategories] WITH CHECK ADD CONSTRAINT [FK_Resource2ExpenditureCategories_Expenditure_Category] FOREIGN KEY([EpenditureCategoryId])
|
|
REFERENCES [dbo].[Expenditure_Category] ([Id])
|
|
|
|
ALTER TABLE [dbo].[Resource2ExpenditureCategories] CHECK CONSTRAINT [FK_Resource2ExpenditureCategories_Expenditure_Category]
|
|
|
|
ALTER TABLE [dbo].[Resource2ExpenditureCategories] WITH CHECK ADD CONSTRAINT [FK_Resource2ExpenditureCategories_Resource] FOREIGN KEY([ResourceId])
|
|
REFERENCES [dbo].[Resource] ([Id])
|
|
|
|
ALTER TABLE [dbo].[Resource2ExpenditureCategories] CHECK CONSTRAINT [FK_Resource2ExpenditureCategories_Resource]
|
|
|
|
ALTER TABLE [dbo].[Resource2Teams] WITH CHECK ADD CONSTRAINT [FK_Resource2Teams_Resource] FOREIGN KEY([ResourceId])
|
|
REFERENCES [dbo].[Resource] ([Id])
|
|
|
|
ALTER TABLE [dbo].[Resource2Teams] CHECK CONSTRAINT [FK_Resource2Teams_Resource]
|
|
|
|
ALTER TABLE [dbo].[Resource2Teams] WITH CHECK ADD CONSTRAINT [FK_Resource2Teams_Team] FOREIGN KEY([TeamId])
|
|
REFERENCES [dbo].[Team] ([Id])
|
|
|
|
ALTER TABLE [dbo].[Resource2Teams] CHECK CONSTRAINT [FK_Resource2Teams_Team]
|
|
|
|
CREATE TABLE [dbo].[ResourceAllocation](
|
|
[Id] [uniqueidentifier] NOT NULL,
|
|
[ResourceId] [uniqueidentifier] NOT NULL,
|
|
[ScenarioId] [uniqueidentifier] NOT NULL,
|
|
[ExpenditureCategoryId] [uniqueidentifier] NOT NULL,
|
|
[StartDate] [date] NOT NULL,
|
|
[EndDate] [date] NOT NULL,
|
|
[AllocatePercentage] [float] NOT NULL,
|
|
CONSTRAINT [PK_ResourceAllocation] PRIMARY KEY CLUSTERED
|
|
(
|
|
[Id] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
|
|
|
|
ALTER TABLE [dbo].[ResourceAllocation] WITH CHECK ADD CONSTRAINT [FK_ResourceAllocation_Expenditure_Category] FOREIGN KEY([ExpenditureCategoryId])
|
|
REFERENCES [dbo].[Expenditure_Category] ([Id])
|
|
|
|
ALTER TABLE [dbo].[ResourceAllocation] CHECK CONSTRAINT [FK_ResourceAllocation_Expenditure_Category]
|
|
|
|
ALTER TABLE [dbo].[ResourceAllocation] WITH CHECK ADD CONSTRAINT [FK_ResourceAllocation_Resource] FOREIGN KEY([ResourceId])
|
|
REFERENCES [dbo].[Resource] ([Id])
|
|
|
|
ALTER TABLE [dbo].[ResourceAllocation] CHECK CONSTRAINT [FK_ResourceAllocation_Resource]
|
|
|
|
ALTER TABLE [dbo].[ResourceAllocation] WITH CHECK ADD CONSTRAINT [FK_ResourceAllocation_Scenario] FOREIGN KEY([ScenarioId])
|
|
REFERENCES [dbo].[Scenario] ([Id])
|
|
|
|
ALTER TABLE [dbo].[ResourceAllocation] CHECK CONSTRAINT [FK_ResourceAllocation_Scenario]
|
|
|
|
end try
|
|
begin catch
|
|
SELECT
|
|
ERROR_NUMBER() AS ErrorNumber
|
|
,ERROR_SEVERITY() AS ErrorSeverity
|
|
,ERROR_STATE() AS ErrorState
|
|
,ERROR_LINE () AS ErrorLine
|
|
,ERROR_PROCEDURE() AS ErrorProcedure
|
|
,ERROR_MESSAGE() AS ErrorMessage;
|
|
IF @@TRANCOUNT > 0
|
|
ROLLBACK TRANSACTION;
|
|
end catch
|
|
|
|
IF @@TRANCOUNT > 0
|
|
COMMIT TRANSACTION;
|
|
|
|
GO |