USE [TransferDb]
GO
/****** Object:  Trigger [dbo].[Trigger_externUpdate_TT_Aux]    Script Date: 04/23/2008 07:32:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Trigger_externUpdate_TT_Aux] ON [dbo].[TT_Aux] 

AFTER UPDATE AS  
Declare @RecId int, @AccessFlag int 

BEGIN  
	select @RecId = i.[RecId], @AccessFlag = i.[AccessFlag] from INSERTED as i JOIN DELETED as d ON i.RecId = d.RecId



	Set TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

		IF @AccessFlag < 4  
			UPDATE [Server1].[TransferDB].[dbo].[TT_Aux] SET [AccessFlag] = @AccessFlag  WHERE [RecID] = @RecId  
	
END 

USE [TransferDb]
GO
/****** Object:  Trigger [dbo].[INS_TT_Aux_3]    Script Date: 04/23/2008 07:35:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*** Trigger instead of update to make the error handling ***/
ALTER TRIGGER [dbo].[INS_TT_Aux_3]
   ON  [dbo].[TT_Aux]
	AFTER UPDATE
AS
Declare
	@RecId int,
	@access_flag int,
	@pps_access_flag int, 
	@l_access_flag int,	  
	@query nvarchar(256)
BEGIN
	--Get all the access flags and the RecId of the last updated record

	Select @RecId = d.RecId, @l_access_flag = d.AccessFlag 
	from INSERTED AS i JOIN DELETED AS d on i.RecId = d.RecId


	set @access_flag = (select AccessFlag from TT_Aux where RecId = @RecId)

	

	set @pps_access_flag = (select AccessFlag from [Server1\PPS_SQL].[TransferDb].[dbo].[TT_Aux] where RecId = @RecId)

	
	if (@pps_access_flag = 3 AND @access_flag = 4)
		begin
			
			UPDATE TT_Auxiliary SET AccessFlag = @l_access_flag where RecId = @RecId
			
		end
	else
		begin
			
			if (@pps_access_flag < 3 AND @access_flag = 4)
				begin
					
									
					UPDATE [Server1\PPS_SQL].[TransferDb].[dbo].[TT_Aux]
					SET 
						[AccessFlag] = @access_flag
					WHERE
						RecId = @RecId
				
				end
			else
				
				begin
				
					UPDATE TT_Aux SET AccessFlag = @access_flag where RecId = @RecId
				
				end
		end	

END