· 4 years ago · Jan 22, 2021, 11:08 AM
1/*
2Deployment script for shopcatalog
3
4This code was generated by a tool.
5Changes to this file may cause incorrect behavior and will be lost if
6the code is regenerated.
7*/
8
9GO
10SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
11
12SET NUMERIC_ROUNDABORT OFF;
13
14
15GO
16:setvar DatabaseName "shopcatalog"
17:setvar DefaultFilePrefix "shopcatalog"
18:setvar DefaultDataPath "C:\Users\user\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\mssqllocaldb\"
19:setvar DefaultLogPath "C:\Users\user\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\mssqllocaldb\"
20
21GO
22:on error exit
23GO
24/*
25Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
26To re-enable the script after enabling SQLCMD mode, execute the following:
27SET NOEXEC OFF;
28*/
29:setvar __IsSqlCmdEnabled "True"
30GO
31IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
32 BEGIN
33 PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
34 SET NOEXEC ON;
35 END
36
37
38GO
39USE [$(DatabaseName)];
40
41
42GO
43
44IF (SELECT OBJECT_ID('tempdb..#tmpErrors')) IS NOT NULL DROP TABLE #tmpErrors
45GO
46CREATE TABLE #tmpErrors (Error int)
47GO
48SET XACT_ABORT ON
49GO
50SET TRANSACTION ISOLATION LEVEL READ COMMITTED
51GO
52BEGIN TRANSACTION
53GO
54PRINT N'Creating [dbo].[Products]...';
55
56
57GO
58CREATE TABLE [dbo].[Products] (
59 [Id] INT IDENTITY (1, 1) NOT NULL,
60 [ShopId] VARCHAR (50) NOT NULL,
61 [ProductId] INT NOT NULL,
62 [Name] NVARCHAR (50) NOT NULL,
63 PRIMARY KEY CLUSTERED ([Id] ASC)
64);
65
66
67GO
68IF @@ERROR <> 0
69 AND @@TRANCOUNT > 0
70 BEGIN
71 ROLLBACK;
72 END
73
74IF @@TRANCOUNT = 0
75 BEGIN
76 INSERT INTO #tmpErrors (Error)
77 VALUES (1);
78 BEGIN TRANSACTION;
79 END
80
81
82GO
83PRINT N'Creating [dbo].[Shops]...';
84
85
86GO
87CREATE TABLE [dbo].[Shops] (
88 [Id] VARCHAR (50) NOT NULL,
89 [Name] NVARCHAR (50) NULL,
90 [Company] NVARCHAR (50) NULL,
91 [Url] VARCHAR (200) NULL,
92 PRIMARY KEY CLUSTERED ([Id] ASC)
93);
94
95
96GO
97IF @@ERROR <> 0
98 AND @@TRANCOUNT > 0
99 BEGIN
100 ROLLBACK;
101 END
102
103IF @@TRANCOUNT = 0
104 BEGIN
105 INSERT INTO #tmpErrors (Error)
106 VALUES (1);
107 BEGIN TRANSACTION;
108 END
109
110
111GO
112PRINT N'Creating [dbo].[FK_ShopId]...';
113
114
115GO
116ALTER TABLE [dbo].[Products] WITH NOCHECK
117 ADD CONSTRAINT [FK_ShopId] FOREIGN KEY ([ShopId]) REFERENCES [dbo].[Shops] ([Id]);
118
119
120GO
121IF @@ERROR <> 0
122 AND @@TRANCOUNT > 0
123 BEGIN
124 ROLLBACK;
125 END
126
127IF @@TRANCOUNT = 0
128 BEGIN
129 INSERT INTO #tmpErrors (Error)
130 VALUES (1);
131 BEGIN TRANSACTION;
132 END
133
134
135GO
136
137IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
138GO
139IF @@TRANCOUNT>0 BEGIN
140PRINT N'The transacted portion of the database update succeeded.'
141COMMIT TRANSACTION
142END
143ELSE PRINT N'The transacted portion of the database update failed.'
144GO
145IF (SELECT OBJECT_ID('tempdb..#tmpErrors')) IS NOT NULL DROP TABLE #tmpErrors
146GO
147GO
148PRINT N'Checking existing data against newly created constraints';
149
150
151GO
152USE [$(DatabaseName)];
153
154
155GO
156ALTER TABLE [dbo].[Products] WITH CHECK CHECK CONSTRAINT [FK_ShopId];
157
158
159GO
160PRINT N'Update complete.';
161
162
163GO