DATETIME2 in MS Access
-
I'm in search of information and advice regarding the SQL Server DATETIME2 datatype in MS Access.
I'm creating a database (SQL SERVER 2016) in which I want to use Temporal Tables to enable change tracking. The DDL for a temporal table requires the use of DATETIME2 columns to store start and end dates to denote the currency of the row. I'm using linked tables in MS Access (2019). When I try to update it I get the following error.
I've learned that this has to do with the fact that MS Access has trouble with the precision of DATETIME2 columns. I've learned that if I specify the precision in the DDL as DATETIME2(3) this this will eliminate the problem (see here). However, I encounter another problem. When I edit a record, sometimes get this error (I think it might be a timing related issue where if I edit, save, and then quickly edit again).
When I dismiss the message and try to save my edit, it saves without complaint. One last detail is that I've found some information that indicates that the ODBC driver being used may play a part (specifically, how MS Access interprets a DATETIME2 field - see here), although this appears not to be specifically related to the problem I'm experiencing.
Another wrinkle is that if saves have been made in quick succession (inside of a second), in the SQL Server, when the data is queried using the FOR SYSTEM_TIME syntax, not all of the saves are displayed (although they are all captured in the history table).
I've included the DDL for my SQL server database and the linked MS Access file so, hopefully, you can easily recreate the problem I'm seeing.
Does anyone know a way around this apparent shortcoming with the DATETIME2 datatype in MS Access?
Thanks in advance for any assistance you can provide.
-
Mmm... it appears I am unable to upload either a .sql file or a .accdb file. How unfortunate.
I tried to recreate the problem with simplified version of the table and could not reproduce the second error I describe above. My tables have a number of other accoutrements (a column with a uniqueidentifier datatype, a number of DRI foreign key relationships and an update trigger that captures the name of the user when the record is saved) that may be contributing to the problem. I will have to do more investigation to narrow down the actual source of the problem.
-
Put the code inline. We have a button (Insert/edit code) above in the toolbar for formatting.
For the Access file, you can zip it, but not many people want to download a file like this because of security concerns. It would be better with simple DDL and data uploaded, and then a better description of what you did in Access so someone can reproduce it.
-
This is a known problem, and there are some workarounds which may or may not be acceptable in your situation. See the following link for the info.
https://docs.microsoft.com/en-us/office/troubleshoot/access/access-linked-table-deleted
I also am aware of a presentation at the recent conference in Vienna, and will do some further digging.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain! -
Steve;
Thanks for the direction. I didn't include the code in-line initially because it is quite large (almost 1200 lines), and as I think about it now, would probably not run without modification since the triggers reference objects in another database. I'll see if I can produce a runnable version and post it.
As far as the Access file I don't really need to include that since it didn't really do anything. It was just an empty database with links to all of the SQL server tables.
-
According to the presenter in Vienna, if you define them as Datetime2 (2) that seems to work without problems. That gives you 1/100th (10ms) seconds precision. Hope that helps.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain! -
If you do not need the datetime2 columns in Access you could try hiding them so they do not appear in SELECT *.
eg
ALTER TABLE YourTable ALTER COLUMN SysStartTime ADD HIDDEN;
-
or maybe create a view where the datetime2's are cast as datetime and use the view in Access.
-
I'm including this for the sake of completing the discussion since I think I've discovered the source of my problem. Using this version of the DDL I was not able to reproduce the 2nd error I noted above. This leads me to believe that my second problem is related to the update trigger (which I've commented out). Another problem to solve... 🙁
Here's the DDL. I think this will work if you simply run it into an empty database.
Wendell; Thank you for your response. I have had success with defining the datatype as DATETIME2(3). I tried DATETIME2(4) and experienced the first error I identified above.
/*
THIS SCRIPT CONTAINS DDL TO CREATE A MODEL OF THE INFORMATION RELATIONSHIPS RELATED TO AER EVENT INVENTORY
IT ALSO CONTAINS INSERT STATEMENTS FOR VALUES IN REFERENCE (AKA LOOK-UP) TABLES.
THIS SCRIPT IS RERUNNABLE IN THE EVENT THAT CHANGES ARE REQUIRED.
THE SCRIPT STEP ORDER PRINCIPLE IS;
DROP OBJECTS IN ORDER OF DEPENDENCY
1 - Many-to-many link tables
2 - Business data tables
3 - Reference tables
4 - Functions
BUILD OBJECTS IN ORDER OF DEPENDENCY
1 - Functions
2 - Reference tables
2A - Insert reference values
3 - Business data tables
4 - Many-to-Many link tables
TABLE DESIGN
NAMING CONVENTION
This article describes the principle applied to table and column naming convention
https://www.sqlservercentral.com/steps/stairway-to-database-design-level-1-data-elements
ALL TABLES CONTAIN "FORENSIC" COLUMNS;
- [Updated_By] - The name of the person who created or last updated the record
- [Updated_On] - the date and time that the record was created or last updated
- [Update_ID] - a timestamp reflecting the time when the record was created or updated. Facilitates identification of records which have changed - for the purpose of database synchronization
- [GUID] - a globally unique identifier for the record. Facilitates database synchronization
THESE COLUMNS ARE INITIALLY SET BY DEFAULTS AND THEN UPDATED, WHERE APPROPRIATE, BY AN UPDATE TRIGGER
ALL TABLES HAVE A [Comment] COLUMN WHICH FACILITATES DOCUMENTING REASONS FOR CHANGES
MOST TABLES HAVE A SYSTEM GENERATED <TABLENAME>_ID COLUMN FOR USE IN DEFINING INFORMATION RELATIONSHIPS.
THIS IS A DATA SYSTEM IDENTIFIER NOT INTENDED FOR BUSINESS USE.
CERTAIN TABLES HAVE A PRIMARY KEY COMPOSED OF THE PRIMARY KEYS OF OTHER TABLES E.G.
- Many-to-Many LINK TABLES
THIS IS DONE TO ENSURE THAT THE ONLY DATA IN THOSE TABLES IS RELATED TO THE OBJECTS IDENTIFIED BY THE PRIMARY KEY
SORT ORDER
MOST TABLES HAVE A COLUMN CALLED [Sort_Order]. THIS IS PROVIDED TO ENABLE A MECHANISM FOR BUSINESS USERS
TO SPECIFY THE SORT ORDER OF RECORDS E.G. ORDER OR REFERENCE TABLE VALUES, ORDER EVENTS ETC. IT WOULD ALSO
ENABLE REFERRING TO RECORDS BY THEIR ORDERED NUMBER E.G. EVENT NUMBER X.
*/
-------------------------------------------------------------------------------------------------------------------------------
-- SET THE CONTEXT FOR RUNNING THE SCRIPT
-- THERE MAY BE CASES WHERE IT IS EASIER TO DROP AND RECREATE THE DATABASE THAN TO DELETE AND RECREATE INDIVIDUAL OBJECTS
-- E.G. OBJECTS HAVE BEEN RENAMED
-- IN THAT CASE SIMPLY UNCOMMENT THE SIX COMMENTED LINES AND RUN THE SCRIPT
-------------------------------------------------------------------------------------------------------------------------------
/*
USE [master]
GO
DROP DATABASE IF EXISTS [IDA_Event_Inventory]
GO
CREATE DATABASE [IDA_Event_Inventory]
GO
USE [IDA_Event_Inventory]
GO
*/
-------------------------------------------------------------------------------------------------------------------------------
-- DROP OBJECTS (IN ORDER OF DEPENDENCY) SO THE REST OF THE SCRIPT WILL RUN
-------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Event_Act') ALTER TABLE [dbo].[T_Event_Act] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Event_Act]
DROP TABLE IF EXISTS [dbo].[T_Event_Act_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Event_Rule_or_Regulation') ALTER TABLE [dbo].[T_Event_Rule_or_Regulation] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Event_Rule_or_Regulation]
DROP TABLE IF EXISTS [dbo].[T_Event_Rule_or_Regulation_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Event_Directive_or_Policy') ALTER TABLE [dbo].[T_Event_Directive_or_Policy] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Event_Directive_or_Policy]
DROP TABLE IF EXISTS [dbo].[T_Event_Directive_or_Policy_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Event_Requirement_Subsection') ALTER TABLE [dbo].[T_Event_Requirement_Subsection] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Event_Requirement_Subsection]
DROP TABLE IF EXISTS [dbo].[T_Event_Requirement_Subsection_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_IDA_Implementation_SME') ALTER TABLE [dbo].[T_IDA_Implementation_SME] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_IDA_Implementation_SME]
DROP TABLE IF EXISTS [dbo].[T_IDA_Implementation_SME_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_IDA_Implementation') ALTER TABLE [dbo].[T_IDA_Implementation] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_IDA_Implementation]
DROP TABLE IF EXISTS [dbo].[T_IDA_Implementation_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Process_Automation_Solution') ALTER TABLE [dbo].[T_Process_Automation_Solution] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Process_Automation_Solution]
DROP TABLE IF EXISTS [dbo].[T_Process_Automation_Solution_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Process_SME') ALTER TABLE [dbo].[T_Process_SME] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Process_SME]
DROP TABLE IF EXISTS [dbo].[T_Process_SME_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Process') ALTER TABLE [dbo].[T_Process] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Process]
DROP TABLE IF EXISTS [dbo].[T_Process_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Event_SME') ALTER TABLE [dbo].[T_Event_SME] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Event_SME]
DROP TABLE IF EXISTS [dbo].[T_Event_SME_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Event') ALTER TABLE [dbo].[T_Event] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Event]
DROP TABLE IF EXISTS [dbo].[T_Event_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Event_Type') ALTER TABLE [dbo].[R_Event_Type] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Event_Type]
DROP TABLE IF EXISTS [dbo].[R_Event_Type_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Public_Info_Sharing_Option') ALTER TABLE [dbo].[R_Public_Info_Sharing_Option] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Public_Info_Sharing_Option]
DROP TABLE IF EXISTS [dbo].[R_Public_Info_Sharing_Option_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Lifecycle_Phase') ALTER TABLE [dbo].[R_Lifecycle_Phase] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Lifecycle_Phase]
DROP TABLE IF EXISTS [dbo].[R_Lifecycle_Phase_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Resource') ALTER TABLE [dbo].[R_Resource] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Resource]
DROP TABLE IF EXISTS [dbo].[R_Resource_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Hazard') ALTER TABLE [dbo].[R_Hazard] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Hazard]
DROP TABLE IF EXISTS [dbo].[R_Hazard_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_PNoA_Requirement') ALTER TABLE [dbo].[R_PNoA_Requirement] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_PNoA_Requirement]
DROP TABLE IF EXISTS [dbo].[R_PNoA_Requirement_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Process_Era') ALTER TABLE [dbo].[R_Process_Era] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Process_Era]
DROP TABLE IF EXISTS [dbo].[R_Process_Era_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Attachment_Option') ALTER TABLE [dbo].[R_Attachment_Option] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Attachment_Option]
DROP TABLE IF EXISTS [dbo].[R_Attachment_Option_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Structured_Form_Option') ALTER TABLE [dbo].[R_Structured_Form_Option] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Structured_Form_Option]
DROP TABLE IF EXISTS [dbo].[R_Structured_Form_Option_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Conditional_Info_Required') ALTER TABLE [dbo].[R_Conditional_Info_Required] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Conditional_Info_Required]
DROP TABLE IF EXISTS [dbo].[R_Conditional_Info_Required_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Additional_Review_Required') ALTER TABLE [dbo].[R_Additional_Review_Required] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Additional_Review_Required]
DROP TABLE IF EXISTS [dbo].[R_Additional_Review_Required_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Professional_Discretion_Applied') ALTER TABLE [dbo].[R_Professional_Discretion_Applied] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Professional_Discretion_Applied]
DROP TABLE IF EXISTS [dbo].[R_Professional_Discretion_Applied_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Statutory_Decision_Maker_Position') ALTER TABLE [dbo].[R_Statutory_Decision_Maker_Position] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Statutory_Decision_Maker_Position]
DROP TABLE IF EXISTS [dbo].[R_Statutory_Decision_Maker_Position_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_AER_Confirmation_Provided') ALTER TABLE [dbo].[R_AER_Confirmation_Provided] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_AER_Confirmation_Provided]
DROP TABLE IF EXISTS [dbo].[R_AER_Confirmation_Provided_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Transformation_Tier') ALTER TABLE [dbo].[R_Transformation_Tier] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Transformation_Tier]
DROP TABLE IF EXISTS [dbo].[R_Transformation_Tier_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Process_Change_Size') ALTER TABLE [dbo].[R_Process_Change_Size] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Process_Change_Size]
DROP TABLE IF EXISTS [dbo].[R_Process_Change_Size_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Data_Sharing_Need') ALTER TABLE [dbo].[R_Data_Sharing_Need] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Data_Sharing_Need]
DROP TABLE IF EXISTS [dbo].[R_Data_Sharing_Need_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Record_Management_Method') ALTER TABLE [dbo].[R_Record_Management_Method] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Record_Management_Method]
DROP TABLE IF EXISTS [dbo].[R_Record_Management_Method_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Automation_Solution') ALTER TABLE [dbo].[R_Automation_Solution] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Automation_Solution]
DROP TABLE IF EXISTS [dbo].[R_Automation_Solution_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_SME_Role') ALTER TABLE [dbo].[R_SME_Role] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_SME_Role]
DROP TABLE IF EXISTS [dbo].[R_SME_Role_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Act') ALTER TABLE [dbo].[R_Act] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Act]
DROP TABLE IF EXISTS [dbo].[R_Act_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Rule_or_Regulation') ALTER TABLE [dbo].[R_Rule_or_Regulation] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Rule_or_Regulation]
DROP TABLE IF EXISTS [dbo].[R_Rule_or_Regulation_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Directive_or_Policy') ALTER TABLE [dbo].[R_Directive_or_Policy] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Directive_or_Policy]
DROP TABLE IF EXISTS [dbo].[R_Directive_or_Policy_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Requirement_Subsection') ALTER TABLE [dbo].[R_Requirement_Subsection] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Requirement_Subsection]
DROP TABLE IF EXISTS [dbo].[R_Requirement_Subsection_History]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Event') ALTER TABLE [dbo].[T_Event] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Event]
DROP TABLE IF EXISTS [dbo].[T_Event_History]
-------------------------------------------------------------------------------------------------------------------------------
-- CREATE A FUNCTION WHICH RETRIEVES THE USER NAME OF THE CURRENT USER BASED ON THEIR USER ID
--
-- THIS FUNCTION USES THE USER'S USER_ID to LOOK UP THEIR NAME. THIS FUNCTION IS USED AS THE DEFAULT FOR THE
-- UPDATED_BY COLUMN IN ALL TABLES IN THE DATABASE AS WELL AS IN THE UPDATE TRIGGER FOR EACH TABLE. IT ENABLES
-- CAPTURING THE IDENTITY OF THE LAST PERSON TO UPDATE A RECORD.
-------------------------------------------------------------------------------------------------------------------------------
--DROP FUNCTION IF EXISTS [dbo].[FN_Get_User_Name]
--GO
--CREATE FUNCTION [dbo].[FN_Get_User_Name]()
--RETURNS VARCHAR(51) AS
--BEGIN
--DECLARE @User_ID CHAR(5)
--SET @User_ID = RIGHT(SYSTEM_USER, 5)
--RETURN ISNULL((SELECT [FIRST_NAME] + ' ' + [LAST_NAME] FROM [PrdDWDB\DW1].[ApplicationDataWarehouse].[dbo].[ADW_EMPLOYEE_AD_INFO] WHERE [AD_LOGON_ID] = @User_ID), @User_ID)
--END
--GO
--///////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STORED PROCEDURE TO CREATE AN UPDATE TRIGGER FOR A TABLE BASED ON THE TABLE NAME
-- THIS PROCEDURE ONLY EXISTS TO ENABLE AUTOMATED CREATION OF THE UPDATE TRIGGER FOR REFERENCE TABLES
-- IT WILL BE DELETED FURTHER DOWN IN THIS SCRIPT (ONCE ALL OF THE REFERENCE TABLES HAVE BEEN CREATED
--///////////////////////////////////////////////////////////////////////////////////////////////////////////////
--DROP PROCEDURE IF EXISTS [dbo].[Create_Update_Trigger]
--GO
--CREATE PROCEDURE [dbo].[Create_Update_Trigger] (@Table_Name AS VARCHAR(255)) AS
--DECLARE
--@SQLString AS VARCHAR(8000)
---- THIS UPDATE TRIGGER UPDATES THE [Updated_By] AND [Updated_On] VALUES WITH DEFAULTS IF VALUES ARE NOT SUPPLIED
---- IN THE UPDATE STATEMENT.
--SET @SQLString =
--'CREATE TRIGGER [dbo].[R_' + @Table_Name + '_Update] ON [dbo].[R_' + @Table_Name + '] FOR UPDATE AS
--IF NOT UPDATE([' + @Table_Name + '_Modified_By])
--SET NOCOUNT ON
--UPDATE TBL SET
--TBL.[' + @Table_Name + '_Modified_By] = [dbo].[FN_Get_User_Name]()
--FROM [dbo].[R_' + @Table_Name + '] TBL
--JOIN inserted INS
--ON TBL.[' + @Table_Name + '_ID] = INS.[' + @Table_Name + '_ID]'
--EXEC(@SQLString)
--GO
--///////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STORED PROCEDURE TO CREATE A REFERENCE TABLE BASED ON THE TABLE NAME
-- THIS PROCEDURE ONLY EXISTS TO ENABLE AUTOMATED CREATION OF REFERENCE TABLES
-- IT WILL BE DELETED FURTHER DOWN IN THIS SCRIPT (ONCE ALL OF THE REFERENCE TABLES HAVE BEEN CREATED)
--///////////////////////////////////////////////////////////////////////////////////////////////////////////////
DROP PROCEDURE IF EXISTS [dbo].[Create_Ref_TBL]
GO
CREATE PROCEDURE [dbo].[Create_Ref_TBL] (@Table_Name AS VARCHAR(255), @Column_Name AS VARCHAR(255)) AS
DECLARE
@SQLString AS VARCHAR(8000)
SET @SQLString =
'CREATE TABLE [dbo].[R_' + @Table_Name + '] (
[' + @Table_Name + '_ID] INT IDENTITY(1,1) CONSTRAINT [PK_' + @Column_Name + '] PRIMARY KEY CLUSTERED -- The unique identifier of the reference value. This is a data system identifier not intended for business use
,[' + @Column_Name + '] VARCHAR(100) NOT NULL -- The name/term/descriptor of the reference value
,[' + @Column_Name + '_Business_Code] VARCHAR(12) NULL -- A convenient surrogate identifier meaningful to the business
,[' + @Table_Name + '_Description] VARCHAR(255) NULL -- A verbose description of the reference value
,[' + @Table_Name + '_Sort_Order] INT NULL -- A value which may be used to define the order in which reference values should be sorted
,[' + @Table_Name + '_Effective_Date] DATE NOT NULL CONSTRAINT [DF_' + @Table_Name + '_Expired_Date] DEFAULT CONVERT(VARCHAR(10), GETDATE(), 101) -- The date the reference value became available for use
,[' + @Table_Name + '_Expired_Date] DATE NULL -- The date the reference value ceased to be available for use
,[' + @Table_Name + '_Comment] VARCHAR(MAX) NULL -- Notes, details, descriptive text about the reference value
,[' + @Table_Name + '_Modification_Remark] VARCHAR(MAX) NULL -- Comments regarding maintenance of the record (e.g. creation, updates, expiration etc.)
,[' + @Table_Name + '_Modified_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_' + @Table_Name + '_Modified_By] DEFAULT ''User Name'' -- [dbo].[FN_Get_User_Name]() -- the name of the person who created or last updated the record
,[' + @Table_Name + '_Version_Start_Time] DATETIME2(3) GENERATED ALWAYS AS ROW START
,[' + @Table_Name + '_Version_End_Time] DATETIME2(3) GENERATED ALWAYS AS ROW END
,[' + @Table_Name + '_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_' + @Table_Name + '_GUID] DEFAULT NEWID() -- a globally unique identifier for the record. Facilitates database synchronization
,PERIOD FOR SYSTEM_TIME ([' + @Table_Name + '_Version_Start_Time], [' + @Table_Name + '_Version_End_Time])
,CONSTRAINT [UIX_' + @Table_Name + '] UNIQUE ([' + @Column_Name + '], [' + @Table_Name + '_Version_End_Time]) -- Check to ensure that there is only one active instance of a given reference value
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[R_' + @Table_Name + '_History]))'
EXEC(@SQLString)
--EXEC [dbo].[Create_Update_Trigger] @Table_Name
GO
-----------------------------------------------------------------------------------------------------------------
-- CREATE REFERENCE TABLES
-----------------------------------------------------------------------------------------------------------------
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Event_Type') ALTER TABLE [dbo].[R_Event_Type] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Event_Type]
DROP TABLE IF EXISTS [dbo].[R_Event_Type_History]
*/
EXEC [dbo].[Create_Ref_TBL] Event_Type, Event_Type
--INSERT INTO [dbo].[R_Event_Type] ([Event_Type], [Event_Type_Description], [Event_Type_Expired_Date], [Event_Type_Business_Code])
--SELECT
--[EVENT_TYPE]
--,[LONG_NAME]
--,CASE [EVENT_TYPE]
--WHEN 'Application' THEN NULL
--WHEN 'Notification' THEN NULL
--WHEN 'Submission' THEN NULL
--ELSE CAST(GETDATE() AS DATE)
--END
--,[ABBREVIATION]
--FROM [PRDDWDB\DW1].[Regulatory_DW].[dbo].[R_EVENT_TYPE]
--WHERE
--[ACTIVE_IND] = 'Y'
--AND [CURRENT_IND] = 'Y'
--AND [EVENT_TYPE] <> ''
INSERT INTO [dbo].[R_Event_Type] ([Event_Type], [Event_Type_Description])
VALUES
('Internally Initiated', 'An event initiated by internal processes')
,('Application', 'An event initiated by internal processes')
,('Submission', 'An event initiated by internal processes')
,('Notification', 'An event initiated by internal processes')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Public_Info_Sharing_Option') ALTER TABLE [dbo].[R_Public_Info_Sharing_Option] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Public_Info_Sharing_Option]
DROP TABLE IF EXISTS [dbo].[R_Public_Info_Sharing_Option_History]
*/
EXEC [dbo].[Create_Ref_TBL] Public_Info_Sharing_Option, Public_Info_Sharing_Option
INSERT INTO [dbo].[R_Public_Info_Sharing_Option] ([Public_Info_Sharing_Option]) VALUES
('Yes - with some restrictions')
,('Yes - full public access')
,('No')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Lifecycle_Phase') ALTER TABLE [dbo].[R_Lifecycle_Phase] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Lifecycle_Phase]
DROP TABLE IF EXISTS [dbo].[R_Lifecycle_Phase_History]
*/
EXEC [dbo].[Create_Ref_TBL] Lifecycle_Phase, Lifecycle_Phase
INSERT INTO [dbo].[R_Lifecycle_Phase] ([Lifecycle_Phase]) VALUES
('Initiate')
,('Construct')
,('Operate')
,('Close')
,('Multiple')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Resource') ALTER TABLE [dbo].[R_Resource] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Resource]
DROP TABLE IF EXISTS [dbo].[R_Resource_History]
*/
EXEC [dbo].[Create_Ref_TBL] Resource, Resource
INSERT INTO [dbo].[R_Resource] ([Resource]) VALUES
('Coal - In Situ')
,('Coal - Mineable')
,('Gas')
,('Oil')
,('Oil Sands - In Situ')
,('Oil, Gas')
,('Coal - Mineable, Oil Sands - Mineable')
,('Oil, Gas, Oil Sands - In Situ')
,('Coal - In Situ, Oil Sands - In Situ')
,('Oil, Gas, Coal - In Situ, Oil Sands - In Situ')
,('Gas, Coal - In Situ, Oil Sands - In Situ')
,('All')
,('N/A')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Hazard') ALTER TABLE [dbo].[R_Hazard] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Hazard]
DROP TABLE IF EXISTS [dbo].[R_Hazard_History]
*/
EXEC [dbo].[Create_Ref_TBL] Hazard, Hazard
INSERT INTO [dbo].[R_Hazard] ([Hazard]) VALUES
('Well')
,('Pipeline')
,('Geophysical')
,('Facility')
,('Mixed')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_PNoA_Requirement') ALTER TABLE [dbo].[R_PNoA_Requirement] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_PNoA_Requirement]
DROP TABLE IF EXISTS [dbo].[R_PNoA_Requirement_History]
*/
EXEC [dbo].[Create_Ref_TBL] PNoA_Requirement, PNoA_Requirement
INSERT INTO [dbo].[R_PNoA_Requirement] ([PNoA_Requirement]) VALUES
('No')
,('Yes - Non-expedited')
,('Yes- Expedited')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Process_Era') ALTER TABLE [dbo].[R_Process_Era] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Process_Era]
DROP TABLE IF EXISTS [dbo].[R_Process_Era_History]
*/
EXEC [dbo].[Create_Ref_TBL] Process_Era, Process_Era
INSERT INTO [dbo].[R_Process_Era] ([Process_Era]) VALUES
('Pre IDA')
,('IDA')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Attachment_Option') ALTER TABLE [dbo].[R_Attachment_Option] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Attachment_Option]
DROP TABLE IF EXISTS [dbo].[R_Attachment_Option_History]
*/
EXEC [dbo].[Create_Ref_TBL] Attachment_Option, Attachment_Option
INSERT INTO [dbo].[R_Attachment_Option] ([Attachment_Option]) VALUES
('Yes')
,('Yes - Provided Physically')
,('No')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Structured_Form_Option') ALTER TABLE [dbo].[R_Structured_Form_Option] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Structured_Form_Option]
DROP TABLE IF EXISTS [dbo].[R_Structured_Form_Option_History]
*/
EXEC [dbo].[Create_Ref_TBL] Structured_Form_Option, Structured_Form_Option
INSERT INTO [dbo].[R_Structured_Form_Option] ([Structured_Form_Option]) VALUES
('Yes - AER Web Form')
,('Yes - PDF')
,('Yes - Non-AER Web Form')
,('No')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Conditional_Info_Required') ALTER TABLE [dbo].[R_Conditional_Info_Required] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Conditional_Info_Required]
DROP TABLE IF EXISTS [dbo].[R_Conditional_Info_Required_History]
*/
EXEC [dbo].[Create_Ref_TBL] Conditional_Info_Required, Conditional_Info_Required
INSERT INTO [dbo].[R_Conditional_Info_Required] ([Conditional_Info_Required]) VALUES
('Yes - Additional Attachments')
,('Yes - Additional Structured Form Data')
,('Yes - Both')
,('No')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Additional_Review_Required') ALTER TABLE [dbo].[R_Additional_Review_Required] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Additional_Review_Required]
DROP TABLE IF EXISTS [dbo].[R_Additional_Review_Required_History]
*/
EXEC [dbo].[Create_Ref_TBL] Additional_Review_Required, Additional_Review_Required
INSERT INTO [dbo].[R_Additional_Review_Required] ([Additional_Review_Required]) VALUES
('Always')
,('Depends on response')
,('Never')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Professional_Discretion_Applied') ALTER TABLE [dbo].[R_Professional_Discretion_Applied] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Professional_Discretion_Applied]
DROP TABLE IF EXISTS [dbo].[R_Professional_Discretion_Applied_History]
*/
EXEC [dbo].[Create_Ref_TBL] Professional_Discretion_Applied, Professional_Discretion_Applied
INSERT INTO [dbo].[R_Professional_Discretion_Applied] ([Professional_Discretion_Applied]) VALUES
('Yes - Simple')
,('Yes - Complex')
,('No')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Statutory_Decision_Maker_Position') ALTER TABLE [dbo].[R_Statutory_Decision_Maker_Position] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Statutory_Decision_Maker_Position]
DROP TABLE IF EXISTS [dbo].[R_Statutory_Decision_Maker_Position_History]
*/
EXEC [dbo].[Create_Ref_TBL] Statutory_Decision_Maker_Position, Statutory_Decision_Maker_Position
--INSERT INTO [dbo].[R_Statutory_Decision_Maker_Position] ([Statutory_Decision_Maker_Position]) VALUES
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_AER_Confirmation_Provided') ALTER TABLE [dbo].[R_AER_Confirmation_Provided] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_AER_Confirmation_Provided]
DROP TABLE IF EXISTS [dbo].[R_AER_Confirmation_Provided_History]
*/
EXEC [dbo].[Create_Ref_TBL] AER_Confirmation_Provided, AER_Confirmation_Provided
INSERT INTO [dbo].[R_AER_Confirmation_Provided] ([AER_Confirmation_Provided]) VALUES
('Yes - Email Only')
,('Yes - Document')
,('Yes - Within user interface')
,('No')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Transformation_Tier') ALTER TABLE [dbo].[R_Transformation_Tier] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Transformation_Tier]
DROP TABLE IF EXISTS [dbo].[R_Transformation_Tier_History]
*/
EXEC [dbo].[Create_Ref_TBL] Transformation_Tier, Transformation_Tier
INSERT INTO [dbo].[R_Transformation_Tier] ([Transformation_Tier]) VALUES
('A')
,('B')
,('C')
,('D')
,('N/A')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Process_Change_Size') ALTER TABLE [dbo].[R_Process_Change_Size] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Process_Change_Size]
DROP TABLE IF EXISTS [dbo].[R_Process_Change_Size_History]
*/
EXEC [dbo].[Create_Ref_TBL] Process_Change_Size, Process_Change_Size
INSERT INTO [dbo].[R_Process_Change_Size] ([Process_Change_Size]) VALUES
('0 - No change necessary')
,('1 - Little Change Necessary')
,('2 - Moderate Change Necessary')
,('3 - Significant Change Necessary')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Data_Sharing_Need') ALTER TABLE [dbo].[R_Data_Sharing_Need] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Data_Sharing_Need]
DROP TABLE IF EXISTS [dbo].[R_Data_Sharing_Need_History]
*/
EXEC [dbo].[Create_Ref_TBL] Data_Sharing_Need, Data_Sharing_Need
INSERT INTO [dbo].[R_Data_Sharing_Need] ([Data_Sharing_Need]) VALUES
('Legacy')
,('GoA')
,('Legacy & GoA')
,('None')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Record_Management_Method') ALTER TABLE [dbo].[R_Record_Management_Method] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Record_Management_Method]
DROP TABLE IF EXISTS [dbo].[R_Record_Management_Method_History]
*/
EXEC [dbo].[Create_Ref_TBL] Record_Management_Method, Record_Management_Method
INSERT INTO [dbo].[R_Record_Management_Method] ([Record_Management_Method]) VALUES
('Withdraw')
,('Amend/Update')
,('Override')
,('All')
,('None')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Automation_Solution') ALTER TABLE [dbo].[R_Automation_Solution] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Automation_Solution]
DROP TABLE IF EXISTS [dbo].[R_Automation_Solution_History]
*/
EXEC [dbo].[Create_Ref_TBL] Automation_Solution, Automation_Solution
INSERT INTO [dbo].[R_Automation_Solution] ([Automation_Solution]) VALUES
('IAR')
,('FIS')
,('COM')
,('AppTracker')
,('PLA AppTracker')
,('MMA AppTracker')
,('GLIMPS')
,('EMS')
,('WATERS')
,('Spreadsheet')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_SME_Role') ALTER TABLE [dbo].[R_SME_Role] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_SME_Role]
DROP TABLE IF EXISTS [dbo].[R_SME_Role_History]
*/
EXEC [dbo].[Create_Ref_TBL] SME_Role, SME_Role
INSERT INTO [dbo].[R_SME_Role] ([SME_Role]) VALUES
('Primary SME')
,('Sign-off Authority')
,('Secondary SME')
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Act') ALTER TABLE [dbo].[R_Act] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Act]
DROP TABLE IF EXISTS [dbo].[R_Act_History]
*/
EXEC [dbo].[Create_Ref_TBL] Act, Act
--INSERT INTO [dbo].[R_Act] ([Act]) VALUES
--('
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Rule_or_Regulation') ALTER TABLE [dbo].[R_Rule_or_Regulation] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Rule_or_Regulation]
DROP TABLE IF EXISTS [dbo].[R_Rule_or_Regulation_History]
*/
EXEC [dbo].[Create_Ref_TBL] Rule_or_Regulation, Rule_or_Regulation
--INSERT INTO [dbo].[R_Rule_or_Regulation] ([Rule_or_Regulation]) VALUES
--('
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Directive_or_Policy') ALTER TABLE [dbo].[R_Directive_or_Policy] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Directive_or_Policy]
DROP TABLE IF EXISTS [dbo].[R_Directive_or_Policy_History]
*/
EXEC [dbo].[Create_Ref_TBL] Directive_or_Policy, Directive_or_Policy
--INSERT INTO [dbo].[R_Directive_or_Policy] ([Directive_or_Policy]) VALUES
--('
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'R_Requirement_Subsection') ALTER TABLE [dbo].[R_Requirement_Subsection] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[R_Requirement_Subsection]
DROP TABLE IF EXISTS [dbo].[R_Requirement_Subsection_History]
*/
EXEC [dbo].[Create_Ref_TBL] Requirement_Subsection, Requirement_Subsection
--INSERT INTO [dbo].[R_Requirement_Subsection] ([Requirement_Subsection]) VALUES
--('
-----------------------------------------------------------------------------------------------------------------
-- CREATE BASE TABLES
-----------------------------------------------------------------------------------------------------------------
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Event') ALTER TABLE [dbo].[T_Event] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Event]
DROP TABLE IF EXISTS [dbo].[T_Event_History]
*/
CREATE TABLE [dbo].[T_Event] (
[Event_ID] INT NOT NULL IDENTITY(1,1) CONSTRAINT [Event_PK] PRIMARY KEY CLUSTERED
,[Event_Name] VARCHAR(255) NOT NULL
,[Event_Type_ID] INT NOT NULL CONSTRAINT [FK_T_Event_Event_Type_ID_to_R_Event_Type] REFERENCES [dbo].[R_Event_Type] ([Event_Type_ID])
,[Event_Description] VARCHAR(255) NULL
,[Event_Condition] VARCHAR(3) NULL CHECK ([Event_Condition] IN('Yes', 'No'))
,[Accountable_Org_Unit] VARCHAR(255) NULL
,[Public_Info_Sharing_Option_ID] INT NULL CONSTRAINT [FK_T_Event_Public_Info_Sharing_Option_ID_to_R_Public_Info_Sharing_Option] REFERENCES [dbo].[R_Public_Info_Sharing_Option] ([Public_Info_Sharing_Option_ID])
,[Lifecycle_Phase_ID] INT NULL CONSTRAINT [FK_T_Event_Lifecycle_Phase_ID_to_R_Lifecycle_Phase] REFERENCES [dbo].[R_Lifecycle_Phase] ([Lifecycle_Phase_ID])
,[Resource_ID] INT NULL CONSTRAINT [FK_T_Event_Resource_ID_to_R_Resource] REFERENCES [dbo].[R_Resource] ([Resource_ID])
,[Hazard_ID] INT NULL CONSTRAINT [FK_T_Event_Hazard_ID_to_R_Hazard] REFERENCES [dbo].[R_Hazard] ([Hazard_ID])
,[PNoA_Requirement_ID] INT NULL CONSTRAINT [FK_T_Event_PNoA_Requirement_ID_to_R_PNoA_Requirement] REFERENCES [dbo].[R_PNoA_Requirement] ([PNoA_Requirement_ID])
,[Event_Comment] VARCHAR(MAX) NULL
--- Forensic columns
,[Event_Modification_Remark] VARCHAR(MAX) NULL -- Comments regarding maintenance of the record (e.g. creation, updates, expiration etc.)
,[Event_Modified_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_Event_Modified_By] DEFAULT 'User Name' -- [dbo].[FN_Get_User_Name]()
,[Event_Version_Start_Time] DATETIME2(3) GENERATED ALWAYS AS ROW START
,[Event_Version_End_Time] DATETIME2(3) GENERATED ALWAYS AS ROW END
,[Event_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_T_Event_GUID] DEFAULT NEWID() -- a globally unique identifier for the record. Facilitates database synchronization
,CONSTRAINT [UIX_T_Event] UNIQUE ([Event_Name], [Event_Type_ID])
,PERIOD FOR SYSTEM_TIME ([Event_Version_Start_Time], [Event_Version_End_Time])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[T_Event_History]))
GO
--CREATE TRIGGER [dbo].[T_Event_Update] ON [dbo].[T_Event] FOR UPDATE AS
--SET NOCOUNT ON
--IF NOT UPDATE([Event_Modified_By])
--UPDATE TBL SET
--TBL.[Event_Modified_By] = [dbo].[FN_Get_User_Name]()
--FROM [dbo].[T_Event] TBL
--JOIN inserted INS
--ON TBL.[Event_ID] = INS.[Event_ID]
--GO
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Event_Act') ALTER TABLE [dbo].[T_Event_Act] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Event_Act]
DROP TABLE IF EXISTS [dbo].[T_Event_Act_History]
*/
CREATE TABLE [dbo].[T_Event_Act] (
[Event_ID] INT NOT NULL CONSTRAINT [FK_Event_Act_Event_ID_to_T_Event] REFERENCES [dbo].[T_Event] (Event_ID)
,[Act_ID] INT NOT NULL CONSTRAINT [FK_Event_Act_Act_ID_to_R_Act] REFERENCES [dbo].[R_Act] ([Act_ID])
,[Event_Act_Comment] VARCHAR(MAX) NULL
--- Forensic columns
,[Event_Act_Modification_Remark] VARCHAR(MAX) NULL -- Comments regarding maintenance of the record (e.g. creation, updates, expiration etc.)
,[Event_Act_Modified_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_Event_Act_Modified_By] DEFAULT 'User Name' -- [dbo].[FN_Get_User_Name]()
,[Event_Act_Version_Start_Time] DATETIME2(3) GENERATED ALWAYS AS ROW START
,[Event_Act_Version_End_Time] DATETIME2(3) GENERATED ALWAYS AS ROW END
,[Event_Act_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_T_Event_Act_GUID] DEFAULT NEWID() -- a globally unique identifier for the record. Facilitates database synchronization
,CONSTRAINT [PK_T_Event_Act] PRIMARY KEY CLUSTERED ([Event_ID], [Act_ID])
,PERIOD FOR SYSTEM_TIME ([Event_Act_Version_Start_Time], [Event_Act_Version_End_Time])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[T_Event_Act_History]))
GO
--CREATE TRIGGER [dbo].[T_Event_Act_Update] ON [dbo].[T_Event_Act] FOR UPDATE AS
--SET NOCOUNT ON
--IF NOT UPDATE([Event_Act_Modified_By])
--UPDATE TBL SET
--TBL.[Event_Act_Modified_By] = [dbo].[FN_Get_User_Name]()
--FROM [dbo].[T_Event_Act] TBL
--JOIN inserted INS
--ON TBL.[Event_ID] = INS.[Event_ID]
--AND TBL.[Act_ID] = INS.[Act_ID]
--GO
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Event_Rule_or_Regulation') ALTER TABLE [dbo].[T_Event_Rule_or_Regulation] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Event_Rule_or_Regulation]
DROP TABLE IF EXISTS [dbo].[T_Event_Rule_or_Regulation_History]
*/
CREATE TABLE [dbo].[T_Event_Rule_or_Regulation] (
[Event_ID] INT NOT NULL CONSTRAINT [FK_Event_Rule_or_Regulation_Event_ID_to_T_Event] REFERENCES [dbo].[T_Event] (Event_ID)
,[Rule_or_Regulation_ID] INT NOT NULL CONSTRAINT [FK_Event_Rule_or_Regulation_Rule_or_Regulation_ID_to_R_Rule_or_Regulation] REFERENCES [dbo].[R_Rule_or_Regulation] ([Rule_or_Regulation_ID])
,[Event_Rule_or_Regulation_Comment] VARCHAR(MAX) NULL
--- Forensic columns
,[Event_Rule_or_Regulation_Modification_Remark] VARCHAR(MAX) NULL -- Comments regarding maintenance of the record (e.g. creation, updates, expiration etc.)
,[Event_Rule_or_Regulation_Modified_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_Event_Rule_or_Regulation_Modified_By] DEFAULT 'User Name' -- [dbo].[FN_Get_User_Name]()
,[Event_Rule_or_Regulation_Version_Start_Time] DATETIME2(3) GENERATED ALWAYS AS ROW START
,[Event_Rule_or_Regulation_Version_End_Time] DATETIME2(3) GENERATED ALWAYS AS ROW END
,[Event_Rule_or_Regulation_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_T_Event_Rule_or_Regulation_GUID] DEFAULT NEWID() -- a globally unique identifier for the record. Facilitates database synchronization
,CONSTRAINT [PK_T_Event_Rule_or_Regulation] PRIMARY KEY CLUSTERED ([Event_ID], [Rule_or_Regulation_ID])
,PERIOD FOR SYSTEM_TIME ([Event_Rule_or_Regulation_Version_Start_Time], [Event_Rule_or_Regulation_Version_End_Time])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[T_Event_Rule_or_Regulation_History]))
GO
--CREATE TRIGGER [dbo].[T_Event_Rule_or_Regulation_Update] ON [dbo].[T_Event_Rule_or_Regulation] FOR UPDATE AS
--SET NOCOUNT ON
--IF NOT UPDATE([Event_Rule_or_Regulation_Modified_By])
--UPDATE TBL SET
--TBL.[Event_Rule_or_Regulation_Modified_By] = [dbo].[FN_Get_User_Name]()
--FROM [dbo].[T_Event_Rule_or_Regulation] TBL
--JOIN inserted INS
--ON TBL.[Event_ID] = INS.[Event_ID]
--AND TBL.[Rule_or_Regulation_ID] = INS.[Rule_or_Regulation_ID]
--GO
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Event_Directive_or_Policy') ALTER TABLE [dbo].[T_Event_Directive_or_Policy] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Event_Directive_or_Policy]
DROP TABLE IF EXISTS [dbo].[T_Event_Directive_or_Policy_History]
*/
CREATE TABLE [dbo].[T_Event_Directive_or_Policy] (
[Event_ID] INT NOT NULL CONSTRAINT [FK_Event_Directive_or_Policy_Event_ID_to_T_Event] REFERENCES [dbo].[T_Event] (Event_ID)
,[Directive_or_Policy_ID] INT NOT NULL CONSTRAINT [FK_Event_Directive_or_Policy_Directive_or_Policy_ID_to_R_Directive_or_Policy] REFERENCES [dbo].[R_Directive_or_Policy] ([Directive_or_Policy_ID])
,[Event_Directive_or_Policy_Comment] VARCHAR(MAX) NULL
--- Forensic columns
,[Event_Directive_or_Policy_Modification_Remark] VARCHAR(MAX) NULL -- Comments regarding maintenance of the record (e.g. creation, updates, expiration etc.)
,[Event_Directive_or_Policy_Modified_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_Event_Directive_or_Policy_Modified_By] DEFAULT 'User Name' -- [dbo].[FN_Get_User_Name]()
,[Event_Directive_or_Policy_Version_Start_Time] DATETIME2(3) GENERATED ALWAYS AS ROW START
,[Event_Directive_or_Policy_Version_End_Time] DATETIME2(3) GENERATED ALWAYS AS ROW END
,[Event_Directive_or_Policy_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_T_Event_Directive_or_Policy_GUID] DEFAULT NEWID() -- a globally unique identifier for the record. Facilitates database synchronization
,CONSTRAINT [PK_T_Event_Directive_or_Policy] PRIMARY KEY CLUSTERED ([Event_ID], [Directive_or_Policy_ID])
,PERIOD FOR SYSTEM_TIME ([Event_Directive_or_Policy_Version_Start_Time], [Event_Directive_or_Policy_Version_End_Time])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[T_Event_Directive_or_Policy_History]))
GO
--CREATE TRIGGER [dbo].[T_Event_Directive_or_Policy_Update] ON [dbo].[T_Event_Directive_or_Policy] FOR UPDATE AS
--SET NOCOUNT ON
--IF NOT UPDATE([Event_Directive_or_Policy_Modified_By])
--UPDATE TBL SET
--TBL.[Event_Directive_or_Policy_Modified_By] = [dbo].[FN_Get_User_Name]()
--FROM [dbo].[T_Event_Directive_or_Policy] TBL
--JOIN inserted INS
--ON TBL.[Event_ID] = INS.[Event_ID]
--AND TBL.[Directive_or_Policy_ID] = INS.[Directive_or_Policy_ID]
--GO
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Event_Requirement_Subsection') ALTER TABLE [dbo].[T_Event_Requirement_Subsection] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Event_Requirement_Subsection]
DROP TABLE IF EXISTS [dbo].[T_Event_Requirement_Subsection_History]
*/
CREATE TABLE [dbo].[T_Event_Requirement_Subsection] (
[Event_ID] INT NOT NULL CONSTRAINT [FK_Event_Requirement_Subsection_Event_ID_to_T_Event] REFERENCES [dbo].[T_Event] (Event_ID)
,[Requirement_Subsection_ID] INT NOT NULL CONSTRAINT [FK_Event_Requirement_Subsection_Event_Requirement_Subsection_ID_to_R_Event_Requirement_Subsection] REFERENCES [dbo].[R_Requirement_Subsection] ([Requirement_Subsection_ID])
,[Event_Requirement_Subsection_Comment] VARCHAR(MAX) NULL
--- Forensic columns
,[Event_Requirement_Subsection_Modification_Remark] VARCHAR(MAX) NULL -- Comments regarding maintenance of the record (e.g. creation, updates, expiration etc.)
,[Event_Requirement_Subsection_Modified_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_Event_Requirement_Subsection_Modified_By] DEFAULT 'User Name' -- [dbo].[FN_Get_User_Name]()
,[Event_Requirement_Subsection_Version_Start_Time] DATETIME2(3) GENERATED ALWAYS AS ROW START
,[Event_Requirement_Subsection_Version_End_Time] DATETIME2(3) GENERATED ALWAYS AS ROW END
,[Event_Requirement_Subsection_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_T_Event_Requirement_Subsection_GUID] DEFAULT NEWID() -- a globally unique identifier for the record. Facilitates database synchronization
,CONSTRAINT [PK_T_Event_Requirement_Subsection] PRIMARY KEY CLUSTERED ([Event_ID], [Requirement_Subsection_ID])
,PERIOD FOR SYSTEM_TIME ([Event_Requirement_Subsection_Version_Start_Time], [Event_Requirement_Subsection_Version_End_Time])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[T_Event_Requirement_Subsection_History]))
GO
--CREATE TRIGGER [dbo].[T_Event_Requirement_Subsection_Update] ON [dbo].[T_Event_Requirement_Subsection] FOR UPDATE AS
--SET NOCOUNT ON
--IF NOT UPDATE([Event_Requirement_Subsection_Modified_By])
--UPDATE TBL SET
--TBL.[Event_Requirement_Subsection_Modified_By] = [dbo].[FN_Get_User_Name]()
--FROM [dbo].[T_Event_Requirement_Subsection] TBL
--JOIN inserted INS
--ON TBL.[Event_ID] = INS.[Event_ID]
--AND TBL.[Requirement_Subsection_ID] = INS.[Requirement_Subsection_ID]
--GO
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Process') ALTER TABLE [dbo].[T_Process] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Process]
DROP TABLE IF EXISTS [dbo].[T_Process_History]
*/
CREATE TABLE [dbo].[T_Process] (
[Process_ID] INT NOT NULL IDENTITY(1, 1) CONSTRAINT [PK_T_Process] PRIMARY KEY CLUSTERED
,[Process_Name] VARCHAR(255) NOT NULL
,[Process_Description] VARCHAR(255) NULL
,[Average_Annual_Instance_Count] INT NULL CONSTRAINT [CK_Average_Annual_Instance_Count_GE_Zero] CHECK ([Average_Annual_Instance_Count] >= 0)
,[Average_Annual_Instance_Duration] INT NULL CONSTRAINT [CK_Average_Annual_Instance_Duration_GE_Zero] CHECK ([Average_Annual_Instance_Duration] >= 0)
,[Target_Success_Goal] NUMERIC(3,2) NULL CONSTRAINT [CK_Target_Success_Goal_Between_Zero_and_One] CHECK ([Target_Success_Goal] BETWEEN 0 AND 1)
,[Process_Era_ID] INT NULL CONSTRAINT [FK_T_Process_Process_Era_ID_to_R_Process_Era] REFERENCES [dbo].[R_Process_Era] ([Process_Era_ID])
,[Commission_Date] DATE NULL
,[Decommission_Date] DATE NULL
,[Unstructured_Attachment_Option_ID] INT NULL CONSTRAINT [FK_T_Process_Unstructured_Attachment_Option_ID_to_R_Attachment_Option] REFERENCES [dbo].[R_Attachment_Option] ([Attachment_Option_ID])
,[Structured_Attachment_Option_ID] INT NULL CONSTRAINT [FK_T_Process_Structured_Attachment_Option_ID_to_R_Attachment_Option] REFERENCES [dbo].[R_Attachment_Option] ([Attachment_Option_ID])
,[GIS_Attachments] VARCHAR(3) NULL CHECK ([GIS_Attachments] IN('Yes', 'No'))
,[Structured_Form_Option_ID] INT NULL CONSTRAINT [FK_T_Process_Structured_Form_Option_ID_to_R_Structured_Form_Option] REFERENCES [dbo].[R_Structured_Form_Option] ([Structured_Form_Option_ID])
,[Conditional_Info_Required_ID] INT NULL CONSTRAINT [FK_T_Process_Conditional_Info_Required_ID_to_R_Conditional_Info_Required] REFERENCES [dbo].[R_Conditional_Info_Required] ([Conditional_Info_Required_ID])
,[Map_Interface_Used] VARCHAR(3) NULL CHECK ([Map_Interface_Used] IN('Yes', 'No'))
,[Validation_Required] VARCHAR(3) NULL CHECK ([Validation_Required] IN('Yes', 'No'))
,[Additional_Review_Required_ID] INT NULL CONSTRAINT [FK_T_Process_Additional_Review_Required_ID_to_R_Additional_Review_Required] REFERENCES [dbo].[R_Additional_Review_Required] ([Additional_Review_Required_ID])
,[Statutory_Decision_Required] VARCHAR(3) NULL CHECK ([Statutory_Decision_Required] IN('Yes', 'No'))
,[Professional_Discretion_Applied_ID] INT NULL CONSTRAINT [FK_T_Process_Professional_Discretion_Applied_ID_to_R_Professional_Discretion_Applied] REFERENCES [dbo].[R_Professional_Discretion_Applied] ([Professional_Discretion_Applied_ID])
,[Statutory_Decision_Maker_Position_ID] INT NULL CONSTRAINT [FK_T_Process_Statutory_Decision_Maker_Position_ID_to_R_Statutory_Decision_Maker_Position] REFERENCES [dbo].[R_Statutory_Decision_Maker_Position] ([Statutory_Decision_Maker_Position_ID])
,[AER_Confirmation_Provided_ID] INT NULL CONSTRAINT [FK_T_Process_AER_Confirmation_Provided_ID_to_R_AER_Confirmation_Provided] REFERENCES [dbo].[R_AER_Confirmation_Provided] ([AER_Confirmation_Provided_ID])
,[AER_Mandated_Format] VARCHAR(3) NULL CHECK ([AER_Mandated_Format] IN('Yes', 'No'))
,[Audit_or_Inspection_Performed] VARCHAR(3) NULL CHECK ([Audit_or_Inspection_Performed] IN('Yes', 'No'))
,[Collaboration_or_Communication] VARCHAR(3) NULL CHECK ([Collaboration_or_Communication] IN('Yes', 'No'))
,[Process_Comment] VARCHAR(MAX) NULL
,[Event_ID] INT NULL CONSTRAINT [FK_T_Process_Event_Id_to_Event] REFERENCES [dbo].[T_Event] ([Event_ID])
--- Forensic columns
,[Process_Modification_Remark] VARCHAR(MAX) NULL -- Comments regarding maintenance of the record (e.g. creation, updates, expiration etc.)
,[Process_Modified_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_Process_Modified_By] DEFAULT 'User Name' -- [dbo].[FN_Get_User_Name]()
,[Process_Version_Start_Time] DATETIME2(3) GENERATED ALWAYS AS ROW START
,[Process_Version_End_Time] DATETIME2(3) GENERATED ALWAYS AS ROW END
,[Process_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_T_Process_GUID] DEFAULT NEWID() -- a globally unique identifier for the record. Facilitates database synchronization
,CONSTRAINT [UIX_T_Process] UNIQUE ([Process_Id], [Event_ID])
,PERIOD FOR SYSTEM_TIME ([Process_Version_Start_Time], [Process_Version_End_Time])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[T_Process_History]))
GO
--CREATE TRIGGER [dbo].[T_Process_Update] ON [dbo].[T_Process] FOR UPDATE AS
--SET NOCOUNT ON
--IF NOT UPDATE([Process_Modified_By])
--UPDATE TBL SET
--TBL.[Process_Modified_By] = [dbo].[FN_Get_User_Name]()
--FROM [dbo].[T_Process] TBL
--JOIN inserted INS
--ON TBL.[Process_ID] = INS.[Process_ID]
--GO
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_IDA_Implementation') ALTER TABLE [dbo].[T_IDA_Implementation] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_IDA_Implementation]
DROP TABLE IF EXISTS [dbo].[T_IDA_Implementation_History]
*/
CREATE TABLE [dbo].[T_IDA_Implementation] (
[Event_ID] INT NOT NULL CONSTRAINT [FK_T_IDA_Implementation_Event_ID_to_T_Event] REFERENCES [dbo].[T_Event] ([Event_ID]) CONSTRAINT [PK_T_IDA_Implementation] PRIMARY KEY CLUSTERED
,[Assessment_Rule_Development_Percent_Complete] NUMERIC(3,2) NULL CONSTRAINT [CK_Assessment_Rule_Development_Percent_Complete_Between_Zero_and_One] CHECK([Assessment_Rule_Development_Percent_Complete] BETWEEN 0 AND 1)
,[Assessment_Rule_Implementation_Percent_Complete] NUMERIC(3,2) NULL CONSTRAINT [CK_Assessment_Rule_Implementation_Percent_Complete_Between_Zero_and_One] CHECK([Assessment_Rule_Implementation_Percent_Complete] BETWEEN 0 AND 1)
,[Structured_Review_Development_Percent_Complete] NUMERIC(3,2) NULL CONSTRAINT [CK_Structured_Review_Development_Percent_Complete_Between_Zero_and_One] CHECK([Structured_Review_Development_Percent_Complete] BETWEEN 0 AND 1)
,[Structured_Review_Implementation_Percent_Complete] NUMERIC(3,2) NULL CONSTRAINT [CK_Structured_Review_Implementation_Percent_Complete_Between_Zero_and_One] CHECK([Structured_Review_Implementation_Percent_Complete] BETWEEN 0 AND 1)
,[Random_Verification_Development_Percent_Complete] NUMERIC(3,2) NULL CONSTRAINT [CK_Random_Verification_Development_Percent_Complete_Between_Zero_and_One] CHECK([Random_Verification_Development_Percent_Complete] BETWEEN 0 AND 1)
,[Random_Verification_Implementation_Percent_Complete] NUMERIC(3,2) NULL CONSTRAINT [CK_Random_Verification_Implementation_Percent_Complete_Between_Zero_and_One] CHECK([Random_Verification_Implementation_Percent_Complete] BETWEEN 0 AND 1)
,[Current_Transformation_Tier_ID] INT NULL CONSTRAINT [FK_T_IDA_Implementation_Current_Transformation_Tier_ID_to_R_Transformation_Tier] REFERENCES [dbo].[R_Transformation_Tier] ([Transformation_Tier_ID])
,[Desired_Transformation_Tier_ID] INT NULL CONSTRAINT [FK_T_IDA_Implementation_Desired_Transformation_Tier_ID_to_R_Transformation_Tier] REFERENCES [dbo].[R_Transformation_Tier] ([Transformation_Tier_ID])
,[Planned_Transformation_Tier_ID] INT NULL CONSTRAINT [FK_T_IDA_Implementation_Planned_Transformation_Tier_ID_to_R_Transformation_Tier] REFERENCES [dbo].[R_Transformation_Tier] ([Transformation_Tier_ID])
,[Process_Change_Size_ID] INT NULL CONSTRAINT [FK_T_IDA_Implementation_Process_Change_Size_ID_to_R_Process_Change_Size] REFERENCES [dbo].[R_Process_Change_Size] ([Process_Change_Size_ID])
,[Data_Sharing_Need_ID] INT NULL CONSTRAINT [FK_T_IDA_Implementation_Data_Sharing_Need_ID_to_R_Data_Sharing_Need] REFERENCES [dbo].[R_Data_Sharing_Need] ([Data_Sharing_Need_ID])
,[Record_Management_Method_ID] INT NULL CONSTRAINT [FK_T_IDA_Implementation_Record_Management_Method_ID_to_R_Record_Management_Method] REFERENCES [dbo].[R_Record_Management_Method] ([Record_Management_Method_ID])
,[Automation_Required] VARCHAR(3) NULL CHECK ([Automation_Required] IN('Yes', 'No'))
,[Automation_Solution_ID] INT NULL CONSTRAINT [FK_T_IDA_Implementation_Automation_Solution_ID_to_R_Automation_Solution] REFERENCES [dbo].[R_Automation_Solution] ([Automation_Solution_ID])
,[Automation_Implementation_Percent_Complete] NUMERIC(3,2) NULL CONSTRAINT [CK_Automation_Implementation_Percent_Complete_Between_Zero_and_One] CHECK([Automation_Implementation_Percent_Complete] BETWEEN 0 AND 1)
,[IDA_Implementation_Comment] VARCHAR(MAX) NULL
--- Forensic columns
,[IDA_Implementation_Modification_Remark] VARCHAR(MAX) NULL -- Comments regarding maintenance of the record (e.g. creation, updates, expiration etc.)
,[IDA_Implementation_Modified_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_IDA_Implementation_Modified_By] DEFAULT 'User Name' -- [dbo].[FN_Get_User_Name]()
,[IDA_Implementation_Version_Start_Time] DATETIME2(3) GENERATED ALWAYS AS ROW START
,[IDA_Implementation_Version_End_Time] DATETIME2(3) GENERATED ALWAYS AS ROW END
,[IDA_Implementation_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_T_IDA_Implementation_GUID] DEFAULT NEWID() -- a globally unique identifier for the record. Facilitates database synchronization
,PERIOD FOR SYSTEM_TIME ([IDA_Implementation_Version_Start_Time], [IDA_Implementation_Version_End_Time])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[T_IDA_Implementation_History]))
GO
--CREATE TRIGGER [dbo].[T_IDA_Implementation_Update] ON [dbo].[T_IDA_Implementation] FOR UPDATE AS
--SET NOCOUNT ON
--IF NOT UPDATE([IDA_Implementation_Modified_By])
--UPDATE TBL SET
--TBL.[IDA_Implementation_Modified_By] = [dbo].[FN_Get_User_Name]()
--FROM [dbo].[T_IDA_Implementation] TBL
--JOIN inserted INS
--ON TBL.[Event_ID] = INS.[Event_ID]
--GO
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Automation_Solution') ALTER TABLE [dbo].[T_Automation_Solution] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Automation_Solution]
DROP TABLE IF EXISTS [dbo].[T_Automation_Solution_History]
*/
CREATE TABLE [dbo].[T_Process_Automation_Solution] (
[Process_ID] INT NOT NULL CONSTRAINT [FK_T_Automation_Solution_Process_ID_to_T_Process] REFERENCES [dbo].[T_Process] ([Process_ID])
,[Automation_Solution_ID] INT NOT NULL CONSTRAINT [FK_T_Automation_Solution_Automation_Solution_ID_to_R_Automation_Solution] REFERENCES [dbo].[R_Automation_Solution] ([Automation_Solution_ID])
,[Automation_Solution_Comment] VARCHAR(MAX) NULL
--- Forensic columns
,[Automation_Solution_Modification_Remark] VARCHAR(MAX) NULL -- Comments regarding maintenance of the record (e.g. creation, updates, expiration etc.)
,[Automation_Solution_Modified_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_T_Automation_Solution_Modified_By] DEFAULT 'User Name' -- [dbo].[FN_Get_User_Name]()
,[Automation_Solution_Version_Start_Time] DATETIME2(3) GENERATED ALWAYS AS ROW START
,[Automation_Solution_Version_End_Time] DATETIME2(3) GENERATED ALWAYS AS ROW END
,[Automation_Solution_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_T_Automation_Solution_GUID] DEFAULT NEWID() -- a globally unique identifier for the record. Facilitates database synchronization
,PERIOD FOR SYSTEM_TIME ([Automation_Solution_Version_Start_Time], [Automation_Solution_Version_End_Time])
,CONSTRAINT [PK_T_Process_Automation_Solution] PRIMARY KEY CLUSTERED ([Process_ID], [Automation_Solution_ID])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[T_Process_Automation_Solution_History]))
GO
--CREATE TRIGGER [dbo].[T_Automation_Solution_Update] ON [dbo].[T_Process_Automation_Solution] FOR UPDATE AS
--SET NOCOUNT ON
--IF NOT UPDATE([Automation_Solution_Modified_By])
--UPDATE TBL SET
--TBL.[Automation_Solution_Modified_By] = [dbo].[FN_Get_User_Name]()
--FROM [dbo].[T_Automation_Solution] TBL
--JOIN inserted INS
--ON TBL.[Process_ID] = INS.[Process_ID]
--GO
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Process_SME') ALTER TABLE [dbo].[T_Process_SME] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Process_SME]
DROP TABLE IF EXISTS [dbo].[T_Process_SME_History]
*/
CREATE TABLE [dbo].[T_Process_SME] (
[Process_ID] INT NOT NULL CONSTRAINT [FK_T_Process_SME_Process_ID_to_T_Process] REFERENCES [dbo].[T_Process] ([Process_ID])
,[Process_SME_Name] VARCHAR(51) NOT NULL
,[Process_SME_Role_ID] INT NOT NULL CONSTRAINT [FK_T_Process_SME_Process_SME_Role_ID_to_R_SME_Role] REFERENCES [dbo].[R_SME_Role] ([SME_Role_ID])
,[Process_SME_Comment] VARCHAR(MAX) NULL
--- Forensic columns
,[Process_SME_Modification_Remark] VARCHAR(MAX) NULL -- Comments regarding maintenance of the record (e.g. creation, updates, expiration etc.)
,[Process_SME_Modified_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_T_Process_SME_Modified_By] DEFAULT 'User Name' -- [dbo].[FN_Get_User_Name]()
,[Process_SME_Version_Start_Time] DATETIME2(3) GENERATED ALWAYS AS ROW START
,[Process_SME_Version_End_Time] DATETIME2(3) GENERATED ALWAYS AS ROW END
,[Process_SME_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_T_Process_SME_GUID] DEFAULT NEWID() -- a globally unique identifier for the record. Facilitates database synchronization
,PERIOD FOR SYSTEM_TIME ([Process_SME_Version_Start_Time], [Process_SME_Version_End_Time])
,CONSTRAINT [PK_T_Process_SME] PRIMARY KEY CLUSTERED ([Process_ID], [Process_SME_Name], [Process_SME_Role_ID])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[T_Process_SME_History]))
GO
--CREATE TRIGGER [dbo].[T_Process_SME_Update] ON [dbo].[T_Process_SME] FOR UPDATE AS
--SET NOCOUNT ON
--IF NOT UPDATE([Process_SME_Modified_By])
--UPDATE TBL SET
--TBL.[Process_SME_Modified_By] = [dbo].[FN_Get_User_Name]()
--FROM [dbo].[T_Process_SME] TBL
--JOIN inserted INS
--ON TBL.[Process_ID] = INS.[Process_ID]
--GO
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_Event_SME') ALTER TABLE [dbo].[T_Event_SME] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_Event_SME]
DROP TABLE IF EXISTS [dbo].[T_Event_SME_History]
*/
CREATE TABLE [dbo].[T_Event_SME] (
[Event_ID] INT NOT NULL CONSTRAINT [FK_T_Event_SME_Event_ID_to_T_Event] REFERENCES [dbo].[T_Event]
,[Event_SME_Name] VARCHAR(51) NOT NULL
,[Event_SME_Role_ID] INT NOT NULL CONSTRAINT [FK_T_Event_SME_Event_SME_Role_ID_to_R_SME_Role] REFERENCES [dbo].[R_SME_Role] ([SME_Role_ID])
,[Event_SME_Comment] VARCHAR(MAX) NULL
--- Forensic columns
,[Event_SME_Modification_Remark] VARCHAR(MAX) NULL -- Comments regarding maintenance of the record (e.g. creation, updates, expiration etc.)
,[Event_SME_Modified_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_T_Event_SME_Modified_By] DEFAULT 'User Name' -- [dbo].[FN_Get_User_Name]()
,[Event_SME_Version_Start_Time] DATETIME2(3) GENERATED ALWAYS AS ROW START
,[Event_SME_Version_End_Time] DATETIME2(3) GENERATED ALWAYS AS ROW END
,[Event_SME_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_T_Event_SME_GUID] DEFAULT NEWID() -- a globally unique identifier for the record. Facilitates database synchronization
,PERIOD FOR SYSTEM_TIME ([Event_SME_Version_Start_Time], [Event_SME_Version_End_Time])
,CONSTRAINT [PK_T_Event_SME] PRIMARY KEY CLUSTERED ([Event_ID], [Event_SME_Name], [Event_SME_Role_ID])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[T_Event_SME_History]))
GO
--CREATE TRIGGER [dbo].[T_Event_SME_Update] ON [dbo].[T_Event_SME] FOR UPDATE AS
--SET NOCOUNT ON
--IF NOT UPDATE([Event_SME_Modified_By])
--UPDATE TBL SET
--TBL.[Event_SME_Modified_By] = [dbo].[FN_Get_User_Name]()
--FROM [dbo].[T_Event_SME] TBL
--JOIN inserted INS
--ON TBL.[Event_ID] = INS.[Event_ID]
--AND TBL.[Event_SME_Name] = INS.[Event_SME_Name]
--AND TBL.[Event_SME_Role_ID] = INS.[Event_SME_Role_ID]
--GO
/*
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'T_IDA_Implementation_SME') ALTER TABLE [dbo].[T_IDA_Implementation_SME] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[T_IDA_Implementation_SME]
DROP TABLE IF EXISTS [dbo].[T_IDA_Implementation_SME_History]
*/
CREATE TABLE [dbo].[T_IDA_Implementation_SME] (
[Event_ID] INT NOT NULL CONSTRAINT [FK_T_IDA_Implementation_SME_Event_ID_to_T_IDA_Implementation] REFERENCES [dbo].[T_IDA_Implementation] ([Event_ID])
,[IDA_Implementation_SME_Name] VARCHAR(51) NOT NULL
,[IDA_Implementation_SME_Role_ID] INT NOT NULL CONSTRAINT [FK_T_IDA_Implementation_SME_IDA_Implementation_SME_Role_ID_to_R_SME_Role] REFERENCES [dbo].[R_SME_Role] ([SME_Role_ID])
,[IDA_Implementation_SME_Comment] VARCHAR(MAX) NULL
--- Forensic columns
,[IDA_Implementation_SME_Modification_Remark] VARCHAR(MAX) NULL -- Comments regarding maintenance of the record (e.g. creation, updates, expiration etc.)
,[IDA_Implementation_SME_Modified_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_T_IDA_Implementation_SME_Modified_By] DEFAULT 'User Name' -- [dbo].[FN_Get_User_Name]()
,[IDA_Implementation_SME_Version_Start_Time] DATETIME2(3) GENERATED ALWAYS AS ROW START
,[IDA_Implementation_SME_Version_End_Time] DATETIME2(3) GENERATED ALWAYS AS ROW END
,[IDA_Implementation_SME_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_T_IDA_Implementation_SME_GUID] DEFAULT NEWID() -- a globally unique identifier for the record. Facilitates database synchronization
,PERIOD FOR SYSTEM_TIME ([IDA_Implementation_SME_Version_Start_Time], [IDA_Implementation_SME_Version_End_Time])
,CONSTRAINT [PK_T_IDA_Implementation_SME] PRIMARY KEY CLUSTERED ([Event_ID], [IDA_Implementation_SME_Name], [IDA_Implementation_SME_Role_ID])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[T_IDA_Implementation_SME_History]))
GO
--CREATE TRIGGER [dbo].[T_IDA_Implementation_SME_Update] ON [dbo].[T_IDA_Implementation_SME] FOR UPDATE AS
--SET NOCOUNT ON
--IF NOT UPDATE([IDA_Implementation_SME_Modified_By])
--UPDATE TBL SET
--TBL.[IDA_Implementation_SME_Modified_By] = [dbo].[FN_Get_User_Name]()
--FROM [dbo].[T_IDA_Implementation_SME] TBL
--JOIN inserted INS
--ON TBL.[Event_ID] = INS.[Event_ID]
--AND TBL.[IDA_Implementation_SME_Name] = INS.[IDA_Implementation_SME_Name]
--AND TBL.[IDA_Implementation_SME_Role_ID] = INS.[IDA_Implementation_SME_Role_ID]
--GO
-------------------------------------------------------------------------------------------------------------------------
-- CREATE VIEWS
-------------------------------------------------------------------------------------------------------------------------
DROP VIEW IF EXISTS [dbo].[Event]
GO
CREATE VIEW [dbo].[Event] AS
SELECT
EV.[Event_ID]
,EV.[Event_Name]
,ET.[Event_Type]
,EV.[Event_Description]
,EV.[Event_Condition]
,EV.[Accountable_Org_Unit]
,PS.[Public_Info_Sharing_Option]
,LP.[Lifecycle_Phase]
,RS.[Resource]
,HZ.[Hazard]
,PN.[PNoA_Requirement]
,EV.[Event_Comment]
,EV.[Event_Modification_Remark]
,EV.[Event_Modified_By]
,EV.[Event_Version_Start_Time]
,EV.[Event_Version_End_Time]
,EV.[Event_GUID]
FROM [dbo].[T_Event] EV
LEFT JOIN [dbo].[R_Event_Type] ET
ON ET.[Event_Type_ID] = EV.[Event_Type_ID]
LEFT JOIN [dbo].[R_Public_Info_Sharing_Option] PS
ON PS.[Public_Info_Sharing_Option_ID] = EV.[Public_Info_Sharing_Option_ID]
LEFT JOIN [dbo].[R_Lifecycle_Phase] LP
ON LP.[Lifecycle_Phase_ID] = EV.[Lifecycle_Phase_ID]
LEFT JOIN [dbo].[R_Resource] RS
on RS.[Resource_ID] = EV.[Resource_ID]
LEFT JOIN [dbo].[R_Hazard] HZ
ON HZ.[Hazard_ID] = EV.[Hazard_ID]
LEFT JOIN [dbo].[R_PNoA_Requirement] PN
ON PN.[PNoA_Requirement_ID] = EV.[PNoA_Requirement_ID]
GO
DROP VIEW IF EXISTS [dbo].[Process]
GO
CREATE VIEW [dbo].[Process] AS
SELECT
PR.[Process_ID]
,PR.[Process_Name]
,PR.[Process_Description]
,PR.[Average_Annual_Instance_Count]
,PR.[Average_Annual_Instance_Duration]
,PR.[Target_Success_Goal]
,VN.[Process_Era]
,PR.[Commission_Date]
,PR.[Decommission_Date]
,UA.[Attachment_Option] AS [Unstructured_Attachment_Option]
,SA.[Attachment_Option] AS [Structured_Attachment_Option]
,CASE PR.[GIS_Attachments]
WHEN 0 THEN 'NO'
WHEN 1 THEN 'YES'
ELSE 'UNKNOWN'
END AS [GIS_Attachments]
,FM.[Structured_Form_Option]
,CO.[Conditional_Info_Required]
,CASE PR.[Map_Interface_Used]
WHEN 0 THEN 'NO'
WHEN 1 THEN 'YES'
ELSE 'UNKNOWN'
END AS [Map_Interface_Used]
,CASE PR.[Validation_Required]
WHEN 0 THEN 'NO'
WHEN 1 THEN 'YES'
ELSE 'UNKNOWN'
END AS [Validation_Required]
,AR.[Additional_Review_Required]
,CASE PR.[Statutory_Decision_Required]
WHEN 0 THEN 'NO'
WHEN 1 THEN 'YES'
ELSE 'UNKNOWN'
END AS [Statutory_Decision_Required]
,PD.[Professional_Discretion_Applied]
,SD.[Statutory_Decision_Maker_Position]
,CASE CP.[AER_Confirmation_Provided]
WHEN 0 THEN 'NO'
WHEN 1 THEN 'YES'
ELSE 'UNKNOWN'
END AS [AER_Confirmation_Provided]
,CASE PR.[AER_Mandated_Format]
WHEN 0 THEN 'NO'
WHEN 1 THEN 'YES'
ELSE 'UNKNOWN'
END AS [AER_Mandated_Format]
,CASE PR.[Audit_or_Inspection_Performed]
WHEN 0 THEN 'NO'
WHEN 1 THEN 'YES'
ELSE 'UNKNOWN'
END AS [Audit_or_Inspection_Performed]
,CASE PR.[Collaboration_or_Communication]
WHEN 0 THEN 'NO'
WHEN 1 THEN 'YES'
ELSE 'UNKNOWN'
END AS [Collaboration_or_Communication]
,PR.[Process_Comment]
,PR.[Process_Modification_Remark]
,PR.[Process_Modified_By]
,PR.[Process_Version_Start_Time]
,PR.[Process_Version_End_Time]
,PR.[Process_GUID]
FROM [dbo].[T_Process] PR
LEFT JOIN [dbo].[R_Process_Era] VN
ON VN.[Process_Era_ID] = PR.[Process_Era_ID]
LEFT JOIN [dbo].[R_Attachment_Option] UA
ON UA.[Attachment_Option_ID] = PR.[Unstructured_Attachment_Option_ID]
LEFT JOIN [dbo].[R_Attachment_Option] SA
ON SA.[Attachment_Option_ID] = PR.[Structured_Attachment_Option_ID]
LEFT JOIN [dbo].[R_Structured_Form_Option] FM
ON FM.[Structured_Form_Option_ID] = PR.[Structured_Form_Option_ID]
LEFT JOIN [dbo].[R_Conditional_Info_Required] CO
ON CO.[Conditional_Info_Required_ID] = PR.[Conditional_Info_Required_ID]
LEFT JOIN [dbo].[R_Additional_Review_Required] AR
ON AR.[Additional_Review_Required_ID] = PR.[Additional_Review_Required_ID]
LEFT JOIN [dbo].[R_Professional_Discretion_Applied] PD
ON PD.[Professional_Discretion_Applied_ID] = PR.[Professional_Discretion_Applied_ID]
LEFT JOIN [dbo].[R_Statutory_Decision_Maker_Position] SD
ON SD.[Statutory_Decision_Maker_Position_ID] = PR.[Statutory_Decision_Maker_Position_ID]
LEFT JOIN [dbo].[R_AER_Confirmation_Provided] CP
ON CP.[AER_Confirmation_Provided_ID] = PR.[AER_Confirmation_Provided_ID]
GO
DROP VIEW IF EXISTS [dbo].[IDA_Implementation]
GO
CREATE VIEW [dbo].[IDA_Implementation] AS
SELECT
II.[Event_ID]
,II.[Assessment_Rule_Development_Percent_Complete]
,II.[Assessment_Rule_Implementation_Percent_Complete]
,II.[Structured_Review_Development_Percent_Complete]
,II.[Structured_Review_Implementation_Percent_Complete]
,II.[Random_Verification_Development_Percent_Complete]
,II.[Random_Verification_Implementation_Percent_Complete]
,CT.[Transformation_Tier] AS [Current_Transformation_Tier]
,DT.[Transformation_Tier] AS [Desired_Transformation_Tier]
,PT.[Transformation_Tier] AS [Planned_Transformation_Tier]
,CS.[Process_Change_Size]
,SH.[Data_Sharing_Need]
,RM.[Record_Management_Method]
,CASE II.[Automation_Required]
WHEN 0 THEN 'NO'
WHEN 1 THEN 'YES'
ELSE 'UNKNOWN'
END AS [Automation_Required]
,AU.[Automation_Solution]
,II.[Automation_Implementation_Percent_Complete]
,II.[IDA_Implementation_Comment]
,II.[IDA_Implementation_Modification_Remark]
,II.[IDA_Implementation_Modified_By]
,II.[IDA_Implementation_Version_Start_Time]
,II.[IDA_Implementation_Version_End_Time]
,II.[IDA_Implementation_GUID]
FROM [dbo].[T_IDA_Implementation] II
LEFT JOIN [dbo].[R_Transformation_Tier] CT
ON CT.[Transformation_Tier_ID] = II.[Current_Transformation_Tier_ID]
LEFT JOIN [dbo].[R_Transformation_Tier] DT
ON DT.[Transformation_Tier_ID] = II.[Desired_Transformation_Tier_ID]
LEFT JOIN [dbo].[R_Transformation_Tier] PT
ON PT.[Transformation_Tier_ID] = II.[Planned_Transformation_Tier_ID]
LEFT JOIN [dbo].[R_Process_Change_Size] CS
ON CS.[Process_Change_Size_ID] = II.[Process_Change_Size_ID]
LEFT JOIN [dbo].[R_Data_Sharing_Need] SH
ON SH.[Data_Sharing_Need_ID] = II.[Data_Sharing_Need_ID]
LEFT JOIN [dbo].[R_Record_Management_Method] RM
ON RM.[Record_Management_Method_ID] = II.[Record_Management_Method_ID]
LEFT JOIN [dbo].[R_Automation_Solution] AU
ON AU.[Automation_Solution_ID] = II.[Automation_Solution_ID] -
Ken;
I tried what you suggest - using the syntax to create the columns as hidden. While this does "hide" the columns in a select * from [tablename], by default, (it simply does not return them. You can, however include them in a query by explicitly naming them).
The HIDDEN operator does not hide the columns from MS Access when linking them via linked tables.
-
Ken;
With regard to creating views; I have not yet tried that but I suspect it would work. I had just hoped to avoid going to that additional overhead.
Thanks!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply