CREATE TABLE [dbo].[ci_twa_relation]
(
[id] [int] NOT NULL,
[last_mod_dt] [int] NULL,
[last_mod_by] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[del] [int] NULL,
[tenant] [binary] (16) NULL,
[creation_date] [nvarchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[tgt_delete_flag] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[tgt_tenant] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[tran_message] [nvarchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[tran_status] [int] NULL,
[tran_apply_after_dt] [int] NULL,
[tran_dt] [int] NULL,
[tran_chg_ref_num] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[type] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[provider_id] [binary] (16) NULL,
[provider_name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[provider_serial_number] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[provider_mac_address] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[provider_asset_num] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[provider_dns_name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[provider_system_name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dependent_id] [binary] (16) NULL,
[dependent_name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dependent_serial_number] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dependent_mac_address] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dependent_asset_num] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dependent_dns_name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dependent_system_name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dependent_federated_asset_id] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dependent_mdr_class] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dependent_mdr_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[provider_federated_asset_id] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[provider_mdr_class] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[provider_mdr_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[provider_tenant] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dependent_tenant] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
/**********************************************************************************************************/
    /*************Affected Table   : ci_twa_relation                                                         **/
    /*************Affected Columns : tran_dt, last_mod_dt, id, creation_date, del, tran_status**/
    /**********************************************************************************************************/
    
    /*NOTE : If any changes are made to this trigger then make sure to change trigger tgr_insert_for_ci_twa_ci, because the logic is same expect the table names referred here*/

CREATE TRIGGER tgr_insert_for_ci_twa_relation ON ci_twa_relation
FOR INSERT
AS
     DECLARE      @computed_id     integer;
     DECLARE      @max_limit       integer;
     DECLARE      @number_of_rows  integer;
     DECLARE      @epoch_date      datetime;
     DECLARE      @ERROR_STRING    varchar;
     
     DECLARE      @tran_dt            integer;
     DECLARE      @last_mod_dt        integer;
     DECLARE      @id                 integer;
     DECLARE      @del                integer;
     DECLARE      @creation_date      integer;
     DECLARE      @tran_status integer;
BEGIN
     DECLARE datacursor CURSOR for select id,del,tran_dt,tran_status,last_mod_dt from INSERTED;

     SET @max_limit  =  1999999999;
     SET @epoch_date =  '19700101';
     
     OPEN datacursor;
     FETCH NEXT FROM datacursor INTO @id,@del,@tran_dt,@tran_status,@last_mod_dt;

     WHILE @@FETCH_STATUS = 0   
     BEGIN
        if(@id = 0)
        BEGIN
           select @number_of_rows=count(id) from ci_twa_relation where (id between 0 and @max_limit);
           if(@number_of_rows = 0)
           BEGIN
             set @computed_id = 1;
           END
           ELSE
           BEGIN
                select @computed_id = max(id) from ci_twa_relation where (id between 0 and @max_limit);
                if(@computed_id = @max_limit) -- Max. limit has been exceeded
                BEGIN
                        --set @ERROR_STRING = 'The limit exceeds max. limit , contact db administrator for details!';
                        RAISERROR ('CMDB_DBMS_TRIGGER2000:ERROR TABLE (ci_twa_relation table) id > %i', 15, -1,@max_limit) WITH NOWAIT;
                        ROLLBACK TRAN;
                        RETURN;
                END
                set @computed_id = @computed_id + 1;
           END
        END
        ELSE
        BEGIN
           set @computed_id = @id;
        END
       
        set @creation_date = DATEDIFF(s, @epoch_date, GETDATE());  -- Creation date should always be equal to current datetime, ignore what is supplied by the application

        IF(@tran_dt is null)   --If the user doesn't supply this, then populate with the current date and time
        BEGIN
           set @tran_dt = @creation_date;
        END
  
        IF(@last_mod_dt is null)   --If the user doesn't supply this, then populate with the current date and time
        BEGIN
           set @last_mod_dt = @creation_date;
        END
  
        IF(@del is null)
        BEGIN
           set @del = 0;
        END
  
        IF(@tran_status is null)
        BEGIN
           set @tran_status = 0;
        END
  
        BEGIN TRY
  
           BEGIN TRANSACTION
              UPDATE ci_twa_relation SET creation_date = @creation_date, tran_dt = @tran_dt,del = @del, id = @computed_id,
              tran_status = @tran_status, last_mod_dt = @last_mod_dt WHERE id=@id;
           COMMIT TRANSACTION
  
        END TRY
  
        BEGIN CATCH
            IF @@TRANCOUNT > 0 --some error has occurred
                ROLLBACK TRAN; --so rollback all transactions
            RAISERROR ('CMDB_DBMS_TRIGGER2001:ERROR TABLE (ci_twa_relation table) update failed  - could not populate default values', 15, -1) WITH NOWAIT;
            BREAK;
        END CATCH
        FETCH NEXT FROM datacursor INTO @id,@del,@tran_dt,@tran_status,@last_mod_dt;
     END
     CLOSE datacursor
     DEALLOCATE datacursor
  END
GO
ALTER TABLE [dbo].[ci_twa_relation] ADD CONSTRAINT [XPKCI_TWA_RELATION] PRIMARY KEY CLUSTERED ([id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [CI_TWA_RELATION_IDX_01] ON [dbo].[ci_twa_relation] ([provider_name]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[ci_twa_relation] TO [service_desk_admin_group]
GRANT INSERT ON  [dbo].[ci_twa_relation] TO [service_desk_admin_group]
GRANT DELETE ON  [dbo].[ci_twa_relation] TO [service_desk_admin_group]
GRANT UPDATE ON  [dbo].[ci_twa_relation] TO [service_desk_admin_group]
GRANT SELECT ON  [dbo].[ci_twa_relation] TO [service_desk_ro_group]
GO
EXEC sp_addextendedproperty N'Comment', N'This is a new table for CMDB PES.', 'SCHEMA', N'dbo', 'TABLE', N'ci_twa_relation', NULL, NULL
GO