text: -- =============================================
  -- Author:		Ben Driver
  -- Create date: 2020-01-16
  -- Updates: 
  --	2020-01-16: v0.14 incremental revisions in support of sprint 4.
  -- 
  -- Description:	T-SQL generated Triggers and History Tables.  Includes sequences for History tables.  All dates set to UTC time, to be consistent throughout the database.
  -- =============================================
  
  USE HMR_DEV; -- uncomment appropriate instance
  --USE HMR_TST;
  --USE HMR_UAT;
  --USE HMR_PRD;
  GO
  
  IF OBJECT_ID ('HMR_RCKFL_RPT_A_S_IUD_TR', 'TR') IS NOT NULL   
    drop trigger HMR_RCKFL_RPT_A_S_IUD_TR
  go
  
  IF OBJECT_ID ('HMR_ROCKFALL_REPORT_HIST', 'U') IS NOT NULL   
    drop table HMR_ROCKFALL_REPORT_HIST
  go
  
  IF OBJECT_ID ('HMR_ROCKFALL_REPORT_H_ID_SEQ', 'SO') IS NOT NULL   
    drop sequence HMR_ROCKFALL_REPORT_H_ID_SEQ
  go
  
  IF OBJECT_ID ('HMR_SUBM_RW_A_S_IUD_TR', 'TR') IS NOT NULL   
    drop trigger HMR_SUBM_RW_A_S_IUD_TR
  go
  
  IF OBJECT_ID ('HMR_SUBMISSION_ROW_HIST', 'U') IS NOT NULL   
    drop table HMR_SUBMISSION_ROW_HIST
  go
  
  IF OBJECT_ID ('HMR_SUBMISSION_ROW_H_ID_SEQ', 'SO') IS NOT NULL   
    drop sequence HMR_SUBMISSION_ROW_H_ID_SEQ
  go
  
  IF OBJECT_ID ('HMR_WLDLF_RPT_A_S_IUD_TR', 'TR') IS NOT NULL   
    drop trigger HMR_WLDLF_RPT_A_S_IUD_TR
  go
  
  IF OBJECT_ID ('HMR_WILDLIFE_REPORT_HIST', 'U') IS NOT NULL   
    drop table HMR_WILDLIFE_REPORT_HIST
  go
  
  IF OBJECT_ID ('HMR_WILDLIFE_REPORT_H_ID_SEQ', 'SO') IS NOT NULL   
    drop sequence HMR_WILDLIFE_REPORT_H_ID_SEQ
  go
  
  IF OBJECT_ID ('HMR_WRK_RPT_A_S_IUD_TR', 'TR') IS NOT NULL   
    drop trigger HMR_WRK_RPT_A_S_IUD_TR
  go
  
  IF OBJECT_ID ('HMR_WORK_REPORT_HIST', 'U') IS NOT NULL   
    drop table HMR_WORK_REPORT_HIST
  go
  
  IF OBJECT_ID ('HMR_WORK_REPORT_H_ID_SEQ', 'SO') IS NOT NULL   
    drop sequence HMR_WORK_REPORT_H_ID_SEQ
  go
  
  CREATE SEQUENCE [dbo].[HMR_ROCKFALL_REPORT_H_ID_SEQ] AS [bigint] START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 50;
  
  CREATE TABLE [dbo].[HMR_ROCKFALL_REPORT_HIST](
    ROCKFALL_REPORT_HIST_ID [bigint] DEFAULT (NEXT VALUE FOR [HMR_ROCKFALL_REPORT_H_ID_SEQ]) NOT NULL
    ,EFFECTIVE_DATE_HIST [datetime] NOT NULL default getutcdate()
    ,END_DATE_HIST [datetime]
    , [ROCKFALL_REPORT_ID] numeric NOT NULL, [SUBMISSION_OBJECT_ID] numeric NOT NULL, [ROW_NUM] numeric NULL, [VALIDATION_STATUS_ID] numeric NULL, [MCRR_INCIDENT_NUMBER] varchar(12) NULL, [ESTIMATED_ROCKFALL_DATE] date NULL, [ESTIMATED_ROCKFALL_TIME] time NULL, [START_LATITUDE] numeric NULL, [START_LONGITUDE] numeric NULL, [END_LATITUDE] numeric NULL, [END_LONGITUDE] numeric NULL, [HIGHWAY_UNIQUE_NUMBER] varchar(16) NULL, [HIGHWAY_UNIQUE_NAME] varchar(255) NULL, [LANDMARK] varchar(8) NULL, [LAND_MARK_NAME] varchar(255) NULL, [START_OFFSET] numeric NULL, [END_OFFSET] numeric NULL, [DIRECTION_FROM_LANDMARK] varchar(1) NULL, [LOCATION_DESCRIPTION] varchar(4000) NULL, [DITCH_VOLUME] varchar(30) NULL, [TRAVELLED_LANES_VOLUME] varchar(30) NULL, [OTHER_VOLUME] numeric NULL, [HEAVY_PRECIP] varchar(1) NULL, [FREEZE_THAW] varchar(1) NULL, [DITCH_SNOW_ICE] varchar(1) NULL, [VEHICLE_DAMAGE] varchar(1) NULL, [COMMENTS] varchar(4000) NULL, [REPORTER_NAME] varchar(150) NULL, [MC_PHONE_NUMBER] varchar(12) NULL, [REPORT_DATE] date NULL, [CONCURRENCY_CONTROL_NUMBER] bigint NOT NULL, [APP_CREATE_USERID] varchar(30) NOT NULL, [APP_CREATE_TIMESTAMP] datetime NOT NULL, [APP_CREATE_USER_GUID] uniqueidentifier NOT NULL, [APP_CREATE_USER_DIRECTORY] varchar(12) NOT NULL, [APP_LAST_UPDATE_USERID] varchar(30) NOT NULL, [APP_LAST_UPDATE_TIMESTAMP] datetime NOT NULL, [APP_LAST_UPDATE_USER_GUID] uniqueidentifier NOT NULL, [APP_LAST_UPDATE_USER_DIRECTORY] varchar(12) NOT NULL, [DB_AUDIT_CREATE_USERID] varchar(30) NOT NULL, [DB_AUDIT_CREATE_TIMESTAMP] datetime NOT NULL, [DB_AUDIT_LAST_UPDATE_USERID] varchar(30) NOT NULL, [DB_AUDIT_LAST_UPDATE_TIMESTAMP] datetime NOT NULL
    )
  ALTER TABLE HMR_ROCKFALL_REPORT_HIST ADD CONSTRAINT HMR_RCKFL_H_PK PRIMARY KEY CLUSTERED (ROCKFALL_REPORT_HIST_ID);  
  ALTER TABLE HMR_ROCKFALL_REPORT_HIST ADD CONSTRAINT HMR_RCKFL_H_UK UNIQUE (ROCKFALL_REPORT_HIST_ID,END_DATE_HIST)
  go
  
  CREATE SEQUENCE [dbo].[HMR_SUBMISSION_ROW_H_ID_SEQ] AS [bigint] START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 50;
  
  CREATE TABLE [dbo].[HMR_SUBMISSION_ROW_HIST](
    SUBMISSION_ROW_HIST_ID [bigint] DEFAULT (NEXT VALUE FOR [HMR_SUBMISSION_ROW_H_ID_SEQ]) NOT NULL
    ,EFFECTIVE_DATE_HIST [datetime] NOT NULL default getutcdate()
    ,END_DATE_HIST [datetime]
    , [ROW_ID] numeric NOT NULL, [SUBMISSION_OBJECT_ID] numeric NOT NULL, [ROW_STATUS_ID] numeric NULL, [ROW_NUM] numeric NULL, [RECORD_NUMBER] varchar(8) NULL, [ROW_VALUE] varchar(4000) NULL, [ROW_HASH] varchar(256) NULL, [IS_RESUBMITTED] varchar(40) NULL, [ERROR_DETAIL] varchar(4000) NULL, [CONCURRENCY_CONTROL_NUMBER] bigint NOT NULL, [APP_CREATE_USERID] varchar(30) NOT NULL, [APP_CREATE_TIMESTAMP] datetime NOT NULL, [APP_CREATE_USER_GUID] uniqueidentifier NOT NULL, [APP_CREATE_USER_DIRECTORY] varchar(12) NOT NULL, [APP_LAST_UPDATE_USERID] varchar(30) NOT NULL, [APP_LAST_UPDATE_TIMESTAMP] datetime NOT NULL, [APP_LAST_UPDATE_USER_GUID] uniqueidentifier NOT NULL, [APP_LAST_UPDATE_USER_DIRECTORY] varchar(12) NOT NULL, [DB_AUDIT_CREATE_USERID] varchar(30) NOT NULL, [DB_AUDIT_CREATE_TIMESTAMP] datetime NOT NULL, [DB_AUDIT_LAST_UPDATE_USERID] varchar(30) NOT NULL, [DB_AUDIT_LAST_UPDATE_TIMESTAMP] datetime NOT NULL
    )
  ALTER TABLE HMR_SUBMISSION_ROW_HIST ADD CONSTRAINT HMR_SUBM__H_PK PRIMARY KEY CLUSTERED (SUBMISSION_ROW_HIST_ID);  
  ALTER TABLE HMR_SUBMISSION_ROW_HIST ADD CONSTRAINT HMR_SUBM__H_UK UNIQUE (SUBMISSION_ROW_HIST_ID,END_DATE_HIST)
  go
  
  CREATE SEQUENCE [dbo].[HMR_WILDLIFE_REPORT_H_ID_SEQ] AS [bigint] START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 50;
  
  CREATE TABLE [dbo].[HMR_WILDLIFE_REPORT_HIST](
    WILDLIFE_REPORT_HIST_ID [bigint] DEFAULT (NEXT VALUE FOR [HMR_WILDLIFE_REPORT_H_ID_SEQ]) NOT NULL
    ,EFFECTIVE_DATE_HIST [datetime] NOT NULL default getutcdate()
    ,END_DATE_HIST [datetime]
    , [WILDLIFE_RECORD_ID] numeric NOT NULL, [SUBMISSION_OBJECT_ID] numeric NOT NULL, [ROW_NUM] numeric NULL, [VALIDATION_STATUS_ID] numeric NULL, [RECORD_TYPE] varchar(1) NULL, [SERVICE_AREA] numeric NOT NULL, [ACCIDENT_DATE] datetime NULL, [TIME_OF_KILL] varchar(1) NULL, [LATITUDE] numeric NULL, [LONGITUDE] numeric NULL, [HIGHWAY_UNIQUE_NUMBER] varchar(16) NULL, [LANDMARK] varchar(8) NULL, [START_OFFSET] numeric NULL, [NEAREST_TOWN] varchar(150) NULL, [WILDLIFE_SIGN] varchar(1) NULL, [QUANTITY] numeric NULL, [SPECIES] numeric NULL, [SEX] varchar(1) NULL, [AGE] varchar(1) NULL, [COMMENT] varchar(1024) NULL, [CONCURRENCY_CONTROL_NUMBER] bigint NOT NULL, [APP_CREATE_USERID] varchar(30) NOT NULL, [APP_CREATE_TIMESTAMP] datetime NOT NULL, [APP_CREATE_USER_GUID] uniqueidentifier NOT NULL, [APP_CREATE_USER_DIRECTORY] varchar(12) NOT NULL, [APP_LAST_UPDATE_USERID] varchar(30) NOT NULL, [APP_LAST_UPDATE_TIMESTAMP] datetime NOT NULL, [APP_LAST_UPDATE_USER_GUID] uniqueidentifier NOT NULL, [APP_LAST_UPDATE_USER_DIRECTORY] varchar(12) NOT NULL, [DB_AUDIT_CREATE_USERID] varchar(30) NOT NULL, [DB_AUDIT_CREATE_TIMESTAMP] datetime NOT NULL, [DB_AUDIT_LAST_UPDATE_USERID] varchar(30) NOT NULL, [DB_AUDIT_LAST_UPDATE_TIMESTAMP] datetime NOT NULL
    )
  ALTER TABLE HMR_WILDLIFE_REPORT_HIST ADD CONSTRAINT HMR_WLDLF_H_PK PRIMARY KEY CLUSTERED (WILDLIFE_REPORT_HIST_ID);  
  ALTER TABLE HMR_WILDLIFE_REPORT_HIST ADD CONSTRAINT HMR_WLDLF_H_UK UNIQUE (WILDLIFE_REPORT_HIST_ID,END_DATE_HIST)
  go
  
  CREATE SEQUENCE [dbo].[HMR_WORK_REPORT_H_ID_SEQ] AS [bigint] START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 50;
  
  CREATE TABLE [dbo].[HMR_WORK_REPORT_HIST](
    WORK_REPORT_HIST_ID [bigint] DEFAULT (NEXT VALUE FOR [HMR_WORK_REPORT_H_ID_SEQ]) NOT NULL
    ,EFFECTIVE_DATE_HIST [datetime] NOT NULL default getutcdate()
    ,END_DATE_HIST [datetime]
    , [WORK_REPORT_ID] numeric NOT NULL, [SUBMISSION_OBJECT_ID] numeric NOT NULL, [ROW_NUM] numeric NULL, [VALIDATION_STATUS_ID] numeric NULL, [RECORD_TYPE] varchar(1) NULL, [SERVICE_AREA] numeric NOT NULL, [RECORD_NUMBER] varchar(8) NULL, [TASK_NUMBER] varchar(6) NULL, [ACTIVITY_NUMBER] varchar(6) NULL, [START_DATE] date NULL, [END_DATE] date NULL, [ACCOMPLISHMENT] numeric NULL, [UNIT_OF_MEASURE] varchar(12) NULL, [POSTED_DATE] date NULL, [HIGHWAY_UNIQUE] varchar(16) NULL, [LANDMARK] varchar(8) NULL, [START_OFFSET] numeric NULL, [END_OFFSET] numeric NULL, [START_LATITUDE] numeric NULL, [START_LONGITUDE] numeric NULL, [END_LATITUDE] numeric NULL, [END_LONGITUDE] numeric NULL, [STRUCTURE_NUMBER] varchar(5) NULL, [SITE_NUMBER] varchar(8) NULL, [VALUE_OF_WORK] numeric NULL, [COMMENTS] varchar(1024) NULL, [CONCURRENCY_CONTROL_NUMBER] bigint NOT NULL, [APP_CREATE_USERID] varchar(30) NOT NULL, [APP_CREATE_TIMESTAMP] datetime NOT NULL, [APP_CREATE_USER_GUID] uniqueidentifier NOT NULL, [APP_CREATE_USER_DIRECTORY] varchar(12) NOT NULL, [APP_LAST_UPDATE_USERID] varchar(30) NOT NULL, [APP_LAST_UPDATE_TIMESTAMP] datetime NOT NULL, [APP_LAST_UPDATE_USER_GUID] uniqueidentifier NOT NULL, [APP_LAST_UPDATE_USER_DIRECTORY] varchar(12) NOT NULL, [DB_AUDIT_CREATE_USERID] varchar(30) NOT NULL, [DB_AUDIT_CREATE_TIMESTAMP] datetime NOT NULL, [DB_AUDIT_LAST_UPDATE_USERID] varchar(30) NOT NULL, [DB_AUDIT_LAST_UPDATE_TIMESTAMP] datetime NOT NULL
    )
  ALTER TABLE HMR_WORK_REPORT_HIST ADD CONSTRAINT HMR_WRK_R_H_PK PRIMARY KEY CLUSTERED (WORK_REPORT_HIST_ID);  
  ALTER TABLE HMR_WORK_REPORT_HIST ADD CONSTRAINT HMR_WRK_R_H_UK UNIQUE (WORK_REPORT_HIST_ID,END_DATE_HIST)
  go
  
  CREATE TRIGGER HMR_RCKFL_RPT_A_S_IUD_TR ON HMR_ROCKFALL_REPORT FOR INSERT, UPDATE, DELETE AS
  SET NOCOUNT ON
  BEGIN TRY
  DECLARE @curr_date datetime;
  SET @curr_date = getutcdate();
    IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- historical
    IF EXISTS(SELECT * FROM deleted)
      update HMR_ROCKFALL_REPORT_HIST set END_DATE_HIST = @curr_date where ROCKFALL_REPORT_ID in (select ROCKFALL_REPORT_ID from deleted) and END_DATE_HIST is null;
    
    IF EXISTS(SELECT * FROM inserted)
      insert into HMR_ROCKFALL_REPORT_HIST ([ROCKFALL_REPORT_ID], [SUBMISSION_OBJECT_ID], [ROW_NUM], [VALIDATION_STATUS_ID], [MCRR_INCIDENT_NUMBER], [ESTIMATED_ROCKFALL_DATE], [ESTIMATED_ROCKFALL_TIME], [START_LATITUDE], [START_LONGITUDE], [END_LATITUDE], [END_LONGITUDE], [HIGHWAY_UNIQUE_NUMBER], [HIGHWAY_UNIQUE_NAME], [LANDMARK], [LAND_MARK_NAME], [START_OFFSET], [END_OFFSET], [DIRECTION_FROM_LANDMARK], [LOCATION_DESCRIPTION], [DITCH_VOLUME], [TRAVELLED_LANES_VOLUME], [OTHER_VOLUME], [HEAVY_PRECIP], [FREEZE_THAW], [DITCH_SNOW_ICE], [VEHICLE_DAMAGE], [COMMENTS], [REPORTER_NAME], [MC_PHONE_NUMBER], [REPORT_DATE], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_USERID], [APP_CREATE_TIMESTAMP], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_AUDIT_CREATE_USERID], [DB_AUDIT_CREATE_TIMESTAMP], [DB_AUDIT_LAST_UPDATE_USERID], [DB_AUDIT_LAST_UPDATE_TIMESTAMP], ROCKFALL_REPORT_HIST_ID, END_DATE_HIST, EFFECTIVE_DATE_HIST)
        select [ROCKFALL_REPORT_ID], [SUBMISSION_OBJECT_ID], [ROW_NUM], [VALIDATION_STATUS_ID], [MCRR_INCIDENT_NUMBER], [ESTIMATED_ROCKFALL_DATE], [ESTIMATED_ROCKFALL_TIME], [START_LATITUDE], [START_LONGITUDE], [END_LATITUDE], [END_LONGITUDE], [HIGHWAY_UNIQUE_NUMBER], [HIGHWAY_UNIQUE_NAME], [LANDMARK], [LAND_MARK_NAME], [START_OFFSET], [END_OFFSET], [DIRECTION_FROM_LANDMARK], [LOCATION_DESCRIPTION], [DITCH_VOLUME], [TRAVELLED_LANES_VOLUME], [OTHER_VOLUME], [HEAVY_PRECIP], [FREEZE_THAW], [DITCH_SNOW_ICE], [VEHICLE_DAMAGE], [COMMENTS], [REPORTER_NAME], [MC_PHONE_NUMBER], [REPORT_DATE], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_USERID], [APP_CREATE_TIMESTAMP], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_AUDIT_CREATE_USERID], [DB_AUDIT_CREATE_TIMESTAMP], [DB_AUDIT_LAST_UPDATE_USERID], [DB_AUDIT_LAST_UPDATE_TIMESTAMP], (next value for [dbo].[HMR_ROCKFALL_REPORT_H_ID_SEQ]) as [ROCKFALL_REPORT_HIST_ID], null as [END_DATE_HIST], @curr_date as [EFFECTIVE_DATE_HIST] from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC hmr_error_handling
  END CATCH;
  go
  
  CREATE TRIGGER HMR_SUBM_RW_A_S_IUD_TR ON HMR_SUBMISSION_ROW FOR INSERT, UPDATE, DELETE AS
  SET NOCOUNT ON
  BEGIN TRY
  DECLARE @curr_date datetime;
  SET @curr_date = getutcdate();
    IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- historical
    IF EXISTS(SELECT * FROM deleted)
      update HMR_SUBMISSION_ROW_HIST set END_DATE_HIST = @curr_date where ROW_ID in (select ROW_ID from deleted) and END_DATE_HIST is null;
    
    IF EXISTS(SELECT * FROM inserted)
      insert into HMR_SUBMISSION_ROW_HIST ([ROW_ID], [SUBMISSION_OBJECT_ID], [ROW_STATUS_ID], [ROW_NUM], [RECORD_NUMBER], [ROW_VALUE], [ROW_HASH], [IS_RESUBMITTED], [ERROR_DETAIL], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_USERID], [APP_CREATE_TIMESTAMP], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_AUDIT_CREATE_USERID], [DB_AUDIT_CREATE_TIMESTAMP], [DB_AUDIT_LAST_UPDATE_USERID], [DB_AUDIT_LAST_UPDATE_TIMESTAMP], SUBMISSION_ROW_HIST_ID, END_DATE_HIST, EFFECTIVE_DATE_HIST)
        select [ROW_ID], [SUBMISSION_OBJECT_ID], [ROW_STATUS_ID], [ROW_NUM], [RECORD_NUMBER], [ROW_VALUE], [ROW_HASH], [IS_RESUBMITTED], [ERROR_DETAIL], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_USERID], [APP_CREATE_TIMESTAMP], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_AUDIT_CREATE_USERID], [DB_AUDIT_CREATE_TIMESTAMP], [DB_AUDIT_LAST_UPDATE_USERID], [DB_AUDIT_LAST_UPDATE_TIMESTAMP], (next value for [dbo].[HMR_SUBMISSION_ROW_H_ID_SEQ]) as [SUBMISSION_ROW_HIST_ID], null as [END_DATE_HIST], @curr_date as [EFFECTIVE_DATE_HIST] from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC hmr_error_handling
  END CATCH;
  go
  
  CREATE TRIGGER HMR_WLDLF_RPT_A_S_IUD_TR ON HMR_WILDLIFE_REPORT FOR INSERT, UPDATE, DELETE AS
  SET NOCOUNT ON
  BEGIN TRY
  DECLARE @curr_date datetime;
  SET @curr_date = getutcdate();
    IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- historical
    IF EXISTS(SELECT * FROM deleted)
      update HMR_WILDLIFE_REPORT_HIST set END_DATE_HIST = @curr_date where WILDLIFE_RECORD_ID in (select WILDLIFE_RECORD_ID from deleted) and END_DATE_HIST is null;
    
    IF EXISTS(SELECT * FROM inserted)
      insert into HMR_WILDLIFE_REPORT_HIST ([WILDLIFE_RECORD_ID], [SUBMISSION_OBJECT_ID], [ROW_NUM], [VALIDATION_STATUS_ID], [RECORD_TYPE], [SERVICE_AREA], [ACCIDENT_DATE], [TIME_OF_KILL], [LATITUDE], [LONGITUDE], [HIGHWAY_UNIQUE_NUMBER], [LANDMARK], [START_OFFSET], [NEAREST_TOWN], [WILDLIFE_SIGN], [QUANTITY], [SPECIES], [SEX], [AGE], [COMMENT], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_USERID], [APP_CREATE_TIMESTAMP], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_AUDIT_CREATE_USERID], [DB_AUDIT_CREATE_TIMESTAMP], [DB_AUDIT_LAST_UPDATE_USERID], [DB_AUDIT_LAST_UPDATE_TIMESTAMP], WILDLIFE_REPORT_HIST_ID, END_DATE_HIST, EFFECTIVE_DATE_HIST)
        select [WILDLIFE_RECORD_ID], [SUBMISSION_OBJECT_ID], [ROW_NUM], [VALIDATION_STATUS_ID], [RECORD_TYPE], [SERVICE_AREA], [ACCIDENT_DATE], [TIME_OF_KILL], [LATITUDE], [LONGITUDE], [HIGHWAY_UNIQUE_NUMBER], [LANDMARK], [START_OFFSET], [NEAREST_TOWN], [WILDLIFE_SIGN], [QUANTITY], [SPECIES], [SEX], [AGE], [COMMENT], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_USERID], [APP_CREATE_TIMESTAMP], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_AUDIT_CREATE_USERID], [DB_AUDIT_CREATE_TIMESTAMP], [DB_AUDIT_LAST_UPDATE_USERID], [DB_AUDIT_LAST_UPDATE_TIMESTAMP], (next value for [dbo].[HMR_WILDLIFE_REPORT_H_ID_SEQ]) as [WILDLIFE_REPORT_HIST_ID], null as [END_DATE_HIST], @curr_date as [EFFECTIVE_DATE_HIST] from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC hmr_error_handling
  END CATCH;
  go
  
  CREATE TRIGGER HMR_WRK_RPT_A_S_IUD_TR ON HMR_WORK_REPORT FOR INSERT, UPDATE, DELETE AS
  SET NOCOUNT ON
  BEGIN TRY
  DECLARE @curr_date datetime;
  SET @curr_date = getutcdate();
    IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- historical
    IF EXISTS(SELECT * FROM deleted)
      update HMR_WORK_REPORT_HIST set END_DATE_HIST = @curr_date where WORK_REPORT_ID in (select WORK_REPORT_ID from deleted) and END_DATE_HIST is null;
    
    IF EXISTS(SELECT * FROM inserted)
      insert into HMR_WORK_REPORT_HIST ([WORK_REPORT_ID], [SUBMISSION_OBJECT_ID], [ROW_NUM], [VALIDATION_STATUS_ID], [RECORD_TYPE], [SERVICE_AREA], [RECORD_NUMBER], [TASK_NUMBER], [ACTIVITY_NUMBER], [START_DATE], [END_DATE], [ACCOMPLISHMENT], [UNIT_OF_MEASURE], [POSTED_DATE], [HIGHWAY_UNIQUE], [LANDMARK], [START_OFFSET], [END_OFFSET], [START_LATITUDE], [START_LONGITUDE], [END_LATITUDE], [END_LONGITUDE], [STRUCTURE_NUMBER], [SITE_NUMBER], [VALUE_OF_WORK], [COMMENTS], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_USERID], [APP_CREATE_TIMESTAMP], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_AUDIT_CREATE_USERID], [DB_AUDIT_CREATE_TIMESTAMP], [DB_AUDIT_LAST_UPDATE_USERID], [DB_AUDIT_LAST_UPDATE_TIMESTAMP], WORK_REPORT_HIST_ID, END_DATE_HIST, EFFECTIVE_DATE_HIST)
        select [WORK_REPORT_ID], [SUBMISSION_OBJECT_ID], [ROW_NUM], [VALIDATION_STATUS_ID], [RECORD_TYPE], [SERVICE_AREA], [RECORD_NUMBER], [TASK_NUMBER], [ACTIVITY_NUMBER], [START_DATE], [END_DATE], [ACCOMPLISHMENT], [UNIT_OF_MEASURE], [POSTED_DATE], [HIGHWAY_UNIQUE], [LANDMARK], [START_OFFSET], [END_OFFSET], [START_LATITUDE], [START_LONGITUDE], [END_LATITUDE], [END_LONGITUDE], [STRUCTURE_NUMBER], [SITE_NUMBER], [VALUE_OF_WORK], [COMMENTS], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_USERID], [APP_CREATE_TIMESTAMP], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_AUDIT_CREATE_USERID], [DB_AUDIT_CREATE_TIMESTAMP], [DB_AUDIT_LAST_UPDATE_USERID], [DB_AUDIT_LAST_UPDATE_TIMESTAMP], (next value for [dbo].[HMR_WORK_REPORT_H_ID_SEQ]) as [WORK_REPORT_HIST_ID], null as [END_DATE_HIST], @curr_date as [EFFECTIVE_DATE_HIST] from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC hmr_error_handling
  END CATCH;
  go
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  