75 lines
2.1 KiB
Transact-SQL
75 lines
2.1 KiB
Transact-SQL
/* ============================================================================
|
||
SYSTEM_INFO – TABELLEN-SKRIPT
|
||
Voraussetzung: Schema [_meta] existiert (siehe [SCHEMA_META].sql)
|
||
============================================================================ */
|
||
|
||
USE [DD_IIM];
|
||
GO
|
||
|
||
SET NOCOUNT ON;
|
||
SET XACT_ABORT ON;
|
||
|
||
BEGIN TRY
|
||
BEGIN TRAN;
|
||
|
||
IF OBJECT_ID(N'_meta.TBDD_CFG_SYSTEM_INFO', N'U') IS NULL
|
||
BEGIN
|
||
CREATE TABLE [_meta].[TBDD_CFG_SYSTEM_INFO]
|
||
(
|
||
[PK_CFG_SYSTEM_INFO_ID] BIGINT IDENTITY(1,1) NOT NULL,
|
||
[SYSTEM_VERSION] NVARCHAR(50) NOT NULL,
|
||
[COMMENT] NVARCHAR(4000) NULL,
|
||
[CREATED_WHO] BIGINT NOT NULL,
|
||
[CREATED_WHEN] DATETIME2(0) NOT NULL,
|
||
[CHANGED_WHO] BIGINT NULL,
|
||
[CHANGED_WHEN] DATETIME2(0) NULL
|
||
);
|
||
END;
|
||
|
||
IF NOT EXISTS
|
||
(
|
||
SELECT 1
|
||
FROM sys.key_constraints kc
|
||
JOIN sys.tables t ON t.object_id = kc.parent_object_id
|
||
JOIN sys.schemas s ON s.schema_id = t.schema_id
|
||
WHERE s.name = N'_meta'
|
||
AND t.name = N'TBDD_CFG_SYSTEM_INFO'
|
||
AND kc.type = N'PK'
|
||
)
|
||
BEGIN
|
||
ALTER TABLE [_meta].[TBDD_CFG_SYSTEM_INFO]
|
||
ADD CONSTRAINT [PK_CFG_SYSTEM_INFO_ID]
|
||
PRIMARY KEY CLUSTERED ([PK_CFG_SYSTEM_INFO_ID]);
|
||
END;
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM [_meta].[TBDD_CFG_SYSTEM_INFO] WITH (HOLDLOCK, UPDLOCK))
|
||
BEGIN
|
||
INSERT INTO [_meta].[TBDD_CFG_SYSTEM_INFO]
|
||
(
|
||
[SYSTEM_VERSION],
|
||
[COMMENT],
|
||
[CREATED_WHO],
|
||
[CREATED_WHEN]
|
||
)
|
||
VALUES
|
||
(
|
||
N'1.0.0.0',
|
||
N'Initiale Anlage',
|
||
1,
|
||
GETDATE()
|
||
);
|
||
END;
|
||
|
||
COMMIT TRAN;
|
||
END TRY
|
||
BEGIN CATCH
|
||
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
|
||
|
||
DECLARE @msg NVARCHAR(4000) = ERROR_MESSAGE();
|
||
DECLARE @num INT = ERROR_NUMBER();
|
||
DECLARE @sev INT = ERROR_SEVERITY();
|
||
DECLARE @stt INT = ERROR_STATE();
|
||
RAISERROR(N'Fehler %d: %s', @sev, @stt, @num, @msg);
|
||
END CATCH;
|
||
GO
|