Bidirectional Trigger ( Insert in one table and reflected in another table and vice versa)

Concept : Enabling the trigger on first table and enabling the CT on other table to get the table data inserted.

/* On first table */
   
CREATE TRIGGER [dbo].[InsertReportedsoftwareimages]   
ON [dbo].[ReportedSoftwareImages]   
AFTER INSERT   
AS   
IF @@ROWCOUNT > 0   
BEGIN TRY;   
SET NOCOUNT ON ;   
   
-- dbo.SoftwareBuildRecords --------------   
MERGE dbo.SoftwareBuildRecords AS tgt   
USING (SELECT t.[Changerequestid],t.[softwareimageBuild],t.[softwareimagename], t.IsFoundOnSoftwareImage   
FROM INSERTED t inner join   
dbo.ChangeRequests_ProblemReport problemReports   
ON t.ChangeRequestId = problemReports.Id and t.IsFoundOnSoftwareImage=1) as src   
ON ( tgt.[Changerequestid] = src.[Changerequestid]   
and tgt.softwareimagename = src.softwareimagename   
 )   
WHEN NOT MATCHED  BY TARGET THEN   
    INSERT([Changerequestid],[BuildName],[softwareimagename])   
    VALUES(src.[Changerequestid],src.[softwareimageBuild],src.[softwareimagename]);   
   
       
END TRY   
BEGIN CATCH   
   
       DECLARE @ErrorMessage NVARCHAR(2048) ;   
       DECLARE @ErrorSeverity INT ;   
       DECLARE @ErrorState INT ;   
   
       /************************************************************************   
       * Retrieve any error information and pass it up to the calling routine *   
       ************************************************************************/   
       SELECT @ErrorMessage = ERROR_MESSAGE(),   
                  @ErrorSeverity = ERROR_SEVERITY(),   
                  @ErrorState = ERROR_STATE() ;   
   
       RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);   
END CATCH ;



/* on Second table */



DECLARE @Currentversion BIGINT;
DECLARE @String varchar(1000)
EXEC sys.sp_executesql
  N'USE Prism;
    SELECT @ReturnValue = CHANGE_TRACKING_CURRENT_VERSION()',
  N'@ReturnValue BIGINT OUTPUT',
  @ReturnValue = @Currentversion OUTPUT
 set @String =' update [Prism].DBA.changetrackingversions set [version] ='+ cast(@Currentversion as varchar)+'
   where TableName=''FoundOnSoftwareImage_CurrentVersion'''
EXEC(@String)




begin tran
declare @Endversion int
DECLARE @String varchar(1000)
   SELECT @Endversion = [version] FROM prism.DBA.changetrackingversions
   where TableName='FoundOnSoftwareImage_EndVersion'


select  ct.ChangeRequestId ,sbr.softwareimagename,sbr.buildname,
case when sbr.ChangeRequestId is null then 1 else 0 end IsDeleted into #Temp
from
changetable (changes [dbo].[SoftwareBuildRecords],@Endversion) as ct
 INNER JOIN [dbo].[SoftwareBuildRecords] as sbr ON ct.[ChangeRequestId] = sbr.[ChangeRequestId]

 -- dbo.Reportedsoftwareimages --------------
MERGE dbo.Reportedsoftwareimages AS tgt
USING (SELECT * FROM #Temp) as src
ON ( tgt.[Changerequestid] = src.[Changerequestid] and tgt.softwareimagename = src.softwareimagename
 )
WHEN NOT MATCHED  BY TARGET THEN
    INSERT([softwareimageBuild],[softwareimagename],[Changerequestid],[IsFoundOnSoftwareImage])
    VALUES(src.buildname,src.[softwareimagename],src.[Changerequestid],1)
 
WHEN MATCHED  THEN
 UPDATE SET [IsFoundOnSoftwareImage]=1;
commit


update tgt set [version] = src.[Version]
 from [Prism].DBA.changetrackingversions as tgt
inner join [Prism].DBA.changetrackingversions as src on
tgt.TableName='FoundOnSoftwareImage_EndVersion'
and src.tablename = 'FoundOnSoftwareImage_CurrentVersion'