Trigger Fail when update table with "In" clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger Fail when update table with "In" clause

Author

Topic

shukla_dh
Starting Member

9 Posts

Posted - 2010-06-12 : 05:20:20

I have created on trigger on table A. When i update single row it works fine. But when i try to update multiple row with in clause like
Update tableA set FieldA = 'a' where id in (1,2,3,4) it raise error.

UspTableName is store procedure which update tableA. In this store procedure i have not used any sub query.

Error:

Error occured while updating status for selected medications. Error:[UspTableName]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Thanks,

Dhaval Shukla
.Net Developer




ALTER       Trigger Trig_PatFavouriteMed__Update_Combined_New ON dbo.PatFavouriteMed
FOR UPDATE 
AS 
	DECLARE @StrBefore as varchar(8000) 
	DECLARE @StrAfter as varchar(8000) 
	DECLARE @StrUserDisplayText as varchar(8000)
	
	-- Variable to store after update values.	
	DECLARE @Trg_After_Update_Pno as varchar(500)	
	DECLARE @Trg_After_Update_MedicineDate as varchar(500)
	DECLARE @Trg_After_Update_Frequency as varchar(500)
	DECLARE @Trg_After_Update_Qty as varchar(500)
	DECLARE @Trg_After_Update_DAW as varchar(500) 
	DECLARE @Trg_After_Update_MedActive as varchar(500) 
	DECLARE @Trg_After_Update_StartDate as varchar(500)
	DECLARE @Trg_After_Update_StopDate as varchar(500)
	DECLARE @Trg_After_Update_Refill as varchar(500)
	DECLARE @Trg_After_Update_Times as varchar(500)
	DECLARE @Trg_After_Update_Reason as varchar(500)
	DECLARE @Trg_After_Update_UpdatedOn as varchar(500)
	DECLARE @Trg_After_Update_UpdatedBy as varchar(500)
	DECLARE @Trg_After_Update_RefillType as varchar(500)
	DECLARE @Trg_After_Update_QuantityType as varchar(500)
	DECLARE @Trg_After_Update_Comments as varchar(500)
	DECLARE @Trg_After_Update_Flagactivemed as varchar(500)
	DECLARE @Trg_After_Update_MedicineName as varchar(500)
	DECLARE @Trg_After_Update_CareGiver as varchar(500)
	DECLARE @Trg_After_Update_CreatedBy as varchar(500)
	DECLARE @Trg_After_Update_CreatedOn as varchar(500)
	DECLARE @Trg_After_Update_RxOTC as varchar(500)
	DECLARE @Trg_After_Update_ReNewdate as varchar(500)
	DECLARE @Trg_After_Update_Remarks as varchar(500)
	DECLARE @Trg_After_Update_RxHistoryMedId as varchar(500)
	DECLARE @Trg_After_Update_EncounterID as varchar(500)
	DECLARE @Trg_After_Update_NDCCode as varchar(500)
	DECLARE @Trg_After_Update_LotNo as varchar(500)
	DECLARE @Trg_After_Update_DrugSynonymID as varchar(500)
	DECLARE @Trg_After_Update_DrugID as varchar(500)
	DECLARE @Trg_After_Update_IsSample as varchar(500)
	DECLARE @Trg_After_Update_MachineName as varchar(500)




	
	-- Variable to store before update values.
	DECLARE @Trg_Before_Update_Pno as varchar(500)	
	DECLARE @Trg_Before_Update_MedicineDate as varchar(500)
	DECLARE @Trg_Before_Update_Frequency as varchar(500)
	DECLARE @Trg_Before_Update_Qty as varchar(500)
	DECLARE @Trg_Before_Update_DAW as varchar(500) 
	DECLARE @Trg_Before_Update_MedActive as varchar(500) 
	DECLARE @Trg_Before_Update_StartDate as varchar(500)
	DECLARE @Trg_Before_Update_StopDate as varchar(500)
	DECLARE @Trg_Before_Update_Refill as varchar(500)
	DECLARE @Trg_Before_Update_Times as varchar(500)
	DECLARE @Trg_Before_Update_Reason as varchar(500)
	DECLARE @Trg_Before_Update_UpdatedOn as varchar(500)
	DECLARE @Trg_Before_Update_UpdatedBy as varchar(500)
	DECLARE @Trg_Before_Update_RefillType as varchar(500)
	DECLARE @Trg_Before_Update_QuantityType as varchar(500)
	DECLARE @Trg_Before_Update_Comments as varchar(500)
	DECLARE @Trg_Before_Update_Flagactivemed as varchar(500)
	DECLARE @Trg_Before_Update_MedicineName as varchar(500)
	DECLARE @Trg_Before_Update_CareGiver as varchar(500)
	DECLARE @Trg_Before_Update_CreatedBy as varchar(500)
	DECLARE @Trg_Before_Update_CreatedOn as varchar(500)
	DECLARE @Trg_Before_Update_RxOTC as varchar(500)
	DECLARE @Trg_Before_Update_ReNewdate as varchar(500)
	DECLARE @Trg_Before_Update_Remarks as varchar(500)
	DECLARE @Trg_Before_Update_RxHistoryMedId as varchar(500)
	DECLARE @Trg_Before_Update_EncounterID as varchar(500)
	DECLARE @Trg_Before_Update_NDCCode as varchar(500)
	DECLARE @Trg_Before_Update_LotNo as varchar(500)
	DECLARE @Trg_Before_Update_DrugSynonymID as varchar(500)
	DECLARE @Trg_Before_Update_DrugID as varchar(500)
	DECLARE @Trg_Before_Update_IsSample as varchar(500)
	DECLARE @Trg_Before_Update_MachineName as varchar(500)

	-- Cursor for fatching value of old record. convert(nvarchar,isnull( Comments , '')),
 	declare xdeleted CURSOR for select  convert(nvarchar,isnull( pno , '')), convert(nvarchar,isnull( MedicineDate , '')),
	convert(nvarchar,isnull( Frequency , '')), convert(nvarchar,isnull( qty , '')), convert(nvarchar,isnull( DAW , '')),
	convert(nvarchar,isnull( MedActive , '')), convert(nvarchar,isnull( Startdate , '')), convert(nvarchar,isnull( StopDate , '')), 
	convert(nvarchar,isnull( Refill , '')), convert(nvarchar,isnull( Times , '')), convert(nvarchar,isnull( Reason , '')), 
	convert(nvarchar,isnull( UpdatedOn , '')), convert(nvarchar,isnull( UpdatedBy , '')), convert(nvarchar,isnull( RefillType , '')), 
	convert(nvarchar,isnull( QuantityType , '')),convert(nvarchar,isnull( Flgavtivemed , '')),
	convert(nvarchar,isnull( MedicineName , '')),convert(nvarchar,isnull( CareGiver , '')),convert(nvarchar,isnull( CreatedBy , '')),
	convert(nvarchar,isnull( CreatedOn , '')),convert(nvarchar,isnull( RxOTC , '')),convert(nvarchar,isnull( ReNewaldate , '')),
	convert(nvarchar,isnull( Remarks , '')),convert(nvarchar,isnull( RxHistoryMedId , '')),convert(nvarchar,isnull( EncounterID , '')),
	convert(nvarchar,isnull( NDCCode , '')),convert(nvarchar,isnull( LotNo , '')),convert(nvarchar,isnull( DrugSynonymID , '')),
	convert(nvarchar,isnull( DrugID , '')),convert(nvarchar,isnull(IsSample , '')),convert(nvarchar,isnull( MachineName , '')) From deleted  

	-- Cursor for factching value of modifying record. convert(nvarchar,isnull( Comments , '')),
	declare xinserted CURSOR for  select  convert(nvarchar,isnull( pno , '')), convert(nvarchar,isnull( MedicineDate , '')),
	convert(nvarchar,isnull( Frequency , '')), convert(nvarchar,isnull( qty , '')), convert(nvarchar,isnull( DAW , '')),
	convert(nvarchar,isnull( MedActive , '')), convert(nvarchar,isnull( Startdate , '')), convert(nvarchar,isnull( StopDate , '')), 
	convert(nvarchar,isnull( Refill , '')), convert(nvarchar,isnull( Times , '')), convert(nvarchar,isnull( Reason , '')), 
	convert(nvarchar,isnull( UpdatedOn , '')), convert(nvarchar,isnull( UpdatedBy , '')), convert(nvarchar,isnull( RefillType , '')), 
	convert(nvarchar,isnull( QuantityType , '')), convert(nvarchar,isnull( Flgavtivemed , '')),
	convert(nvarchar,isnull( MedicineName , '')),convert(nvarchar,isnull( CareGiver , '')),convert(nvarchar,isnull( CreatedBy , '')),
	convert(nvarchar,isnull( CreatedOn , '')),convert(nvarchar,isnull( RxOTC , '')),convert(nvarchar,isnull( ReNewaldate , '')),
	convert(nvarchar,isnull( Remarks , '')),convert(nvarchar,isnull( RxHistoryMedId , '')),convert(nvarchar,isnull( EncounterID , '')),
	convert(nvarchar,isnull( NDCCode , '')),convert(nvarchar,isnull( LotNo , '')),convert(nvarchar,isnull( DrugSynonymID , '')),
	convert(nvarchar,isnull( DrugID , '')),convert(nvarchar,isnull(IsSample , '')),convert(nvarchar,isnull( MachineName , ''))  From inserted 


	open xdeleted FETCH NEXT FROM xdeleted into   -- @Trg_Before_Update_Comments ,
	@Trg_Before_Update_Pno,@Trg_Before_Update_MedicineDate , @Trg_Before_Update_Frequency,@Trg_Before_Update_Qty,
	@Trg_Before_Update_DAW,@Trg_Before_Update_MedActive,@Trg_Before_Update_StartDate,@Trg_Before_Update_StopDate,
	@Trg_Before_Update_Refill,@Trg_Before_Update_Times,@Trg_Before_Update_Reason,@Trg_Before_Update_UpdatedOn,
	@Trg_Before_Update_UpdatedBy,@Trg_Before_Update_RefillType,@Trg_Before_Update_QuantityType,
	@Trg_Before_Update_Flagactivemed,@Trg_Before_Update_MedicineName,
	@Trg_Before_Update_CareGiver, @Trg_Before_Update_CreatedBy, @Trg_Before_Update_CreatedOn,
	@Trg_Before_Update_RxOTC, @Trg_Before_Update_ReNewdate, @Trg_Before_Update_Remarks,@Trg_Before_Update_RxHistoryMedId, 
	@Trg_Before_Update_EncounterID , @Trg_Before_Update_NDCCode ,@Trg_Before_Update_LotNo,
	@Trg_Before_Update_DrugSynonymID,@Trg_Before_Update_DrugID,@Trg_Before_Update_IsSample,@Trg_Before_Update_MachineName 


	-- @Trg_After_Update_Comments ,
	open xinserted FETCH NEXT FROM xinserted   into 
	@Trg_After_Update_Pno,@Trg_After_Update_MedicineDate , @Trg_After_Update_Frequency,@Trg_After_Update_Qty,
	@Trg_After_Update_DAW,@Trg_After_Update_MedActive,@Trg_After_Update_StartDate,@Trg_After_Update_StopDate,
	@Trg_After_Update_Refill,@Trg_After_Update_Times,@Trg_After_Update_Reason,@Trg_After_Update_UpdatedOn,
	@Trg_After_Update_UpdatedBy,@Trg_After_Update_RefillType,@Trg_After_Update_QuantityType,
	@Trg_After_Update_Flagactivemed,@Trg_After_Update_MedicineName,
	@Trg_After_Update_CareGiver, @Trg_After_Update_CreatedBy, @Trg_After_Update_CreatedOn,
	@Trg_After_Update_RxOTC, @Trg_After_Update_ReNewdate, @Trg_After_Update_Remarks,@Trg_After_Update_RxHistoryMedId ,
	@Trg_After_Update_EncounterID , @Trg_After_Update_NDCCode ,@Trg_After_Update_LotNo,
	@Trg_After_Update_DrugSynonymID,@Trg_After_Update_DrugID,@Trg_After_Update_IsSample,@Trg_After_Update_MachineName 


	WHILE @@FETCH_STATUS = 0 
	begin 
		set @StrBefore = '' 
		set @StrAfter = '' 
		set @StrUserDisplayText = ''

		-- Patient No
		if  @Trg_Before_Update_Pno <>  @Trg_After_Update_Pno 
		begin 
			set @StrBefore  = @StrBefore    + 'Patient No =*' +  @Trg_Before_Update_Pno  + '* | ' 
			set @StrAfter   = @StrAfter     + 'Patient No =*' +  @Trg_After_Update_Pno + '* | ' 
		End 
		

		-- Medicine Date
		if @Trg_Before_Update_MedicineDate <>  @Trg_After_Update_MedicineDate
		begin 
			set @StrBefore  = @StrBefore    + ' Medicine Date =*' +  @Trg_Before_Update_MedicineDate  + '* | ' 
			set @StrAfter   = @StrAfter     + ' Medicine Date =*' +  @Trg_After_Update_MedicineDate + '* | ' 
			set @StrUserDisplayText   = @StrUserDisplayText     + ' Medicine Date =*' +  @Trg_After_Update_MedicineDate + '* | ' 
		End 

		-- Frequency
		if @Trg_Before_Update_Frequency <>  @Trg_After_Update_Frequency
		begin 
			set @StrBefore  = @StrBefore    + ' Frequency =*' +  @Trg_Before_Update_Frequency  + '* | ' 
			set @StrAfter   = @StrAfter     + ' Frequency =*' +  @Trg_After_Update_Frequency + '* | ' 
			set @StrUserDisplayText   = @StrUserDisplayText     + ' Frequency =*' +  @Trg_After_Update_Frequency + '* | ' 
		End 

		-- Qty
		if @Trg_Before_Update_Qty  <>  @Trg_After_Update_Qty
		begin
			set @StrBefore = @StrBefore + ' Qty =*' + @Trg_Before_Update_Qty + '* | '
			set @StrAfter = @StrAfter + ' Qty = *'  + @Trg_After_Update_Qty + '* | '
			set @StrUserDisplayText = @StrUserDisplayText + ' Qty = *'  + @Trg_After_Update_Qty + '* | '
		end 

		-- DAW
		if @Trg_Before_Update_DAW <> @Trg_After_Update_DAW
		begin
			set @StrBefore = @StrBefore + ' Dispense As Written =*' + @Trg_Before_Update_DAW + '*' + ' | '  
			set @StrAfter =  @StrAfter + ' Dispense As Written =*' +   @Trg_After_Update_DAW  + '*' + ' | '  
		end

		-- Medicine Status
		if @Trg_Before_Update_MedActive <>  @Trg_After_Update_MedActive 
		begin
			if @Trg_Before_Update_MedActive ='0' set @Trg_Before_Update_MedActive = 'Inactive'
			else if @Trg_Before_Update_MedActive = '1' set @Trg_Before_Update_MedActive = 'Active'
			else if @Trg_Before_Update_MedActive = '2' set @Trg_Before_Update_MedActive = 'Discontinued'
			else if @Trg_Before_Update_MedActive = '3' set @Trg_Before_Update_MedActive = 'Erroneous'
			else if @Trg_Before_Update_MedActive = '4' set @Trg_Before_Update_MedActive = 'Complated'

			if @Trg_After_Update_MedActive ='0' set @Trg_After_Update_MedActive = 'Inactive'
			else if @Trg_After_Update_MedActive = '1' set @Trg_After_Update_MedActive = 'Active'
			else if @Trg_After_Update_MedActive = '2' set @Trg_After_Update_MedActive = 'Discontinued'
			else if @Trg_After_Update_MedActive = '3' set @Trg_After_Update_MedActive = 'Erroneous'
			else if @Trg_After_Update_MedActive = '4' set @Trg_After_Update_MedActive = 'Complated'

			set @StrBefore = @StrBefore + ' Medicine Status  =*' +  @Trg_Before_Update_MedActive + '*' + ' | '  
			set @StrAfter =  @StrAfter + ' Medicine Status =*' +  @Trg_After_Update_MedActive + '*' + ' | '  
			set @StrUserDisplayText =  @StrUserDisplayText + ' Medicine Status =*' +  @Trg_After_Update_MedActive + '*' + ' | '			
		end

		-- Start Date
		if @Trg_Before_Update_StartDate <> @Trg_After_Update_StartDate
		begin
			set @StrBefore = @StrBefore + ' Start Date =*' +  @Trg_Before_Update_StartDate   + '*' + ' | '  
			set @StrAfter =  @StrAfter + ' Start Date =*' +   @Trg_After_Update_StartDate  + '*' + ' | '  
			set @StrUserDisplayText =  @StrUserDisplayText + ' Start Date =*' +   @Trg_After_Update_StartDate  + '*' + ' | ' 
		end

		-- Stop Date
		if @Trg_Before_Update_StopDate <> @Trg_After_Update_StopDate
		begin
			set @StrBefore = @StrBefore + ' Stop Date =*' +  @Trg_Before_Update_StopDate   + '*' + ' | '  
			set @StrAfter =  @StrAfter + ' Stop Date =*' +   @Trg_After_Update_StopDate  + '*' + ' | '  
			set @StrUserDisplayText =  @StrUserDisplayText + ' Stop Date =*' +   @Trg_After_Update_StopDate  + '*' + ' | ' 
		end

		-- Refill
		if @Trg_Before_Update_Refill <> @Trg_After_Update_Refill
		begin
			set @StrBefore = @StrBefore + ' Refill  =*' +  @Trg_Before_Update_Refill   + '*' + ' | '  
			set @StrAfter =  @StrAfter + ' Refill =*' +   @Trg_After_Update_Refill  + '*' + ' | '  
			set @StrUserDisplayText =  @StrUserDisplayText + ' Refill =*' +   @Trg_After_Update_Refill  + '*' + ' | ' 
		end		

		-- Times
		if @Trg_Before_Update_Times <> @Trg_After_Update_Times
		begin
			set @StrBefore = @StrBefore + ' Times  =*' +  @Trg_Before_Update_Times   + '*' + ' | '  
			set @StrAfter =  @StrAfter + ' Times =*' +   @Trg_After_Update_Times  + '*' + ' | '  
			set @StrUserDisplayText =  @StrUserDisplayText + ' Times =*' +   @Trg_After_Update_Times  + '*' + ' | '  
		end		

		-- Reason
		if @Trg_Before_Update_Reason <> @Trg_After_Update_Reason
		begin
			set @StrBefore = @StrBefore + ' Reason  =*' +  @Trg_Before_Update_Reason   + '*' + ' | '  
			set @StrAfter =  @StrAfter + ' Reason =*' +   @Trg_After_Update_Reason  + '*' + ' | ' 
			set @StrUserDisplayText =  @StrUserDisplayText + ' Reason =*' +   @Trg_After_Update_Reason  + '*' + ' | ' 
		end	
 

		-- Updated On
		if @Trg_Before_Update_UpdatedOn <> @Trg_After_Update_UpdatedOn 
		begin 
			set @StrBefore  = @StrBefore    + ' Updated on =*' + @Trg_Before_Update_UpdatedOn + '* | ' 
			set @StrAfter   = @StrAfter     + ' Updated on =*' + @Trg_After_Update_UpdatedOn + '* | ' 
		End 

		-- Updated By
		if @Trg_Before_Update_UpdatedBy <> @Trg_After_Update_UpdatedBy
		begin 
			set @StrBefore  = @StrBefore    + ' Updated by =*' + @Trg_Before_Update_UpdatedBy + '* | ' 
			set @StrAfter   = @StrAfter     + ' Updated by =*' + @Trg_After_Update_UpdatedBy + '* | ' 
		End 

	
		-- Refill Type
		if @Trg_Before_Update_RefillType <> @Trg_After_Update_RefillType
		begin 
			
			SELECT @Trg_Before_Update_RefillType = Description FROM QuantityTypes WHERE QuantityType = @Trg_Before_Update_RefillType
			SELECT @Trg_After_Update_RefillType = Description FROM QuantityTypes WHERE QuantityType = @Trg_After_Update_RefillType

			set @StrBefore  = @StrBefore    + ' Refill Type =*' + @Trg_Before_Update_RefillType + '* | ' 
			set @StrAfter   = @StrAfter     + ' Refill Type =*' + @Trg_After_Update_RefillType + '* | ' 
			set @StrUserDisplayText   = @StrUserDisplayText + ' Refill Type =*' + @Trg_After_Update_RefillType + '* | '
		End 
		

		-- Quantity 
		if @Trg_Before_Update_QuantityType <>@Trg_After_Update_QuantityType
		begin 
			
			SELECT @Trg_Before_Update_QuantityType = Description FROM QuantityTypes WHERE QuantityType = @Trg_Before_Update_UpdatedBy
			SELECT @Trg_After_Update_QuantityType = Description FROM QuantityTypes WHERE QuantityType = @Trg_After_Update_UpdatedBy

			set @StrBefore  = @StrBefore    + ' Quantity Type =*' + @Trg_Before_Update_QuantityType + '* | ' 
			set @StrAfter   = @StrAfter     + ' Quantity Type =*' + @Trg_After_Update_QuantityType + '* | ' 
			set @StrUserDisplayText   = @StrUserDisplayText     + ' Quantity Type =*' + @Trg_After_Update_QuantityType + '* | ' 
		End 

	
		-- Comment
		--if @Trg_Before_Update_Comments <>  @Trg_After_Update_Comments
		--begin
		--	set @StrBefore  = @StrBefore    + ' Comments =*' + @Trg_Before_Update_Comments + '* | ' 
		--	set @StrAfter   = @StrAfter     + ' Comments =*' + @Trg_After_Update_Comments + '* | ' 
		--end


		-- Flag For Active Medication
		
		if @Trg_Before_Update_Flagactivemed <> @Trg_After_Update_Flagactivemed
		begin
			set @StrBefore  = @StrBefore    + ' Flag for Active Medicine =*' + @Trg_Before_Update_Flagactivemed + '* | ' 
			set @StrAfter   = @StrAfter     + ' Flag for Active Medicine =*' + @Trg_After_Update_Flagactivemed + '* | ' 
		end

		-- Medicine Name
		if @Trg_Before_Update_MedicineName <> @Trg_After_Update_MedicineName
		begin
			set @StrBefore  = @StrBefore    + ' Medicine Name =*' + @Trg_Before_Update_MedicineName + '* | ' 
			set @StrAfter   = @StrAfter     + ' Medicine Name =*' + @Trg_After_Update_MedicineName + '* | ' 
			set @StrUserDisplayText   = @StrUserDisplayText  + ' Medicine Name =*' + @Trg_After_Update_MedicineName + '* | ' 
		end

		-- Care Giver
		if @Trg_Before_Update_CareGiver <> @Trg_After_Update_CareGiver
		begin
			set @StrBefore  = @StrBefore    + ' Care Giver =*' + @Trg_Before_Update_CareGiver + '* | ' 
			set @StrAfter   = @StrAfter     + ' Care Giver =*' + @Trg_After_Update_CareGiver + '* | ' 
			set @StrUserDisplayText = @StrUserDisplayText + ' Care Giver =*' + @Trg_After_Update_CareGiver + '* | ' 
		end
		
		-- Created by
		if @Trg_Before_Update_CreatedBy <> @Trg_After_Update_CreatedBy
		begin
			set @StrBefore  = @StrBefore    + ' Created by =*' + @Trg_Before_Update_CreatedBy + '* | ' 
			set @StrAfter   = @StrAfter     + ' Created by =*' + @Trg_After_Update_CreatedBy+ '* | ' 
		end		

		
		-- Created On
		if @Trg_Before_Update_CreatedOn <> @Trg_After_Update_CreatedOn
		begin
			set @StrBefore  = @StrBefore    + ' Created on =*' + @Trg_Before_Update_CreatedOn + '* | ' 
			set @StrAfter   = @StrAfter     + ' Created on =*' + @Trg_After_Update_CreatedOn+ '* | ' 
		end		

		-- RxOTC
		if @Trg_Before_Update_RxOTC <> @Trg_After_Update_RxOTC
		begin
			set @StrBefore  = @StrBefore    + ' RxOTC =*' + @Trg_Before_Update_RxOTC + '* | ' 
			set @StrAfter   = @StrAfter     + ' RxOTC =*' + @Trg_After_Update_RxOTC + '* | ' 
		end			

		--Renewal Date
		if @Trg_Before_Update_ReNewdate <>  @Trg_After_Update_ReNewdate
		begin
			set @StrBefore  = @StrBefore    + ' Renewal Date =*' + @Trg_Before_Update_ReNewDate + '* | ' 
			set @StrAfter   = @StrAfter     + ' Renewal Date =*' + @Trg_After_Update_ReNewDate + '* | ' 
			set @StrUserDisplayText   = @StrUserDisplayText     + ' Renewal Date =*' + @Trg_After_Update_ReNewDate + '* | ' 
		end

		-- Remarks
		If @Trg_Before_Update_Remarks <>  @Trg_After_Update_Remarks
		begin
			set @StrBefore  = @StrBefore    + ' Remarks =*' + @Trg_Before_Update_Remarks + '* | ' 
			set @StrAfter   = @StrAfter     + ' Remarks =*' + @Trg_After_Update_Remarks + '* | ' 
			set @StrUserDisplayText   = @StrUserDisplayText     + ' Remarks =*' + @Trg_After_Update_Remarks + '* | ' 
		end
		
		-- Rx History Medicine Id
		if @Trg_Before_Update_RxHistoryMedId <> @Trg_After_Update_RxHistoryMedId
		begin
			set @StrBefore  = @StrBefore    + ' Rx History Medicine Id =*' + @Trg_Before_Update_RxHistoryMedId + '* | ' 
			set @StrAfter   = @StrAfter     + ' Rx History Medicine Id =*' + @Trg_After_Update_RxHistoryMedId + '* | ' 
		end

		-- Encounter ID
		if @Trg_Before_Update_EncounterID <> @Trg_After_Update_EncounterID
		begin
			set @StrBefore  = @StrBefore    + ' Encounter Id =*' + @Trg_Before_Update_EncounterID + '* | ' 
			set @StrAfter   = @StrAfter     + ' Encounter Id =*' + @Trg_After_Update_EncounterID + '* | ' 
		end

		-- NDC Code
		if @Trg_Before_Update_NDCCode <> @Trg_After_Update_NDCCode
		begin
			set @StrBefore  = @StrBefore    + ' NDC Code =*' + @Trg_Before_Update_NDCCode + '* | ' 
			set @StrAfter   = @StrAfter     + ' NDC Code =*' + @Trg_After_Update_NDCCode + '* | ' 
		end

		-- Lot No
		if @Trg_Before_Update_LotNo <> @Trg_After_Update_LotNo
		begin
			set @StrBefore  = @StrBefore    + ' Lot No =*' + @Trg_Before_Update_LotNo + '* | ' 
			set @StrAfter   = @StrAfter     + ' Lot No =*' + @Trg_After_Update_LotNo + '* | ' 
			set @StrUserDisplayText   = @StrUserDisplayText     + ' Lot No =*' + @Trg_After_Update_LotNo + '* | ' 
		end

		-- DrugSynonymID
		if @Trg_Before_Update_DrugSynonymID <>  @Trg_After_Update_DrugSynonymID
		begin
			set @StrBefore  = @StrBefore    + ' Drug Synonym Id =*' + @Trg_Before_Update_DrugSynonymID + '* | ' 
			set @StrAfter   = @StrAfter     + ' Drug Synonym Id =*' + @Trg_After_Update_DrugSynonymID + '* | ' 
		end


		-- Drug ID
		if @Trg_Before_Update_DrugID <>  @Trg_After_Update_DrugID
		begin
			set @StrBefore  = @StrBefore    + ' Drug Id =*' + @Trg_Before_Update_DrugID + '* | ' 
			set @StrAfter   = @StrAfter     + ' Drug Id =*' + @Trg_After_Update_DrugID + '* | ' 
		end

		
		-- Is Sample
		if @Trg_Before_Update_IsSample <>  @Trg_After_Update_IsSample
		begin
			set @StrBefore  = @StrBefore    + ' Sample =*' + @Trg_Before_Update_IsSample + '* | ' 
			set @StrAfter   = @StrAfter     + ' Sample =*' + @Trg_After_Update_IsSample + '* | ' 
			set @StrUserDisplayText   = @StrUserDisplayText     + ' Sample =*' + @Trg_After_Update_IsSample + '* | ' 

		end

		-- Machine Name
		if @Trg_Before_Update_MachineName <>  @Trg_After_Update_MachineName
		begin
			set @StrBefore  = @StrBefore    + ' Machine Name =*' + @Trg_Before_Update_MachineName + '* | ' 
			set @StrAfter   = @StrAfter     + ' Machine Name =*' + @Trg_After_Update_MachineName + '* | ' 
		end
		

		if @StrAfter <>''    
		begin 
  
			insert into PatFavouriteMed_Log (ID, UpdatedOn, UpdatedBy, TableName, Status, Pno,Tableid,UserDisplayChange,BeforeChange, AfterChange,
			OldUpdatedBy, OldUpdatedOn,MachineName) Select top 1 (select isnull(max(id),0)+1 from PatFavouriteMed_Log), getdate(), 
			@Trg_After_Update_UpdatedBy , 'Active Medication', 'Update', @Trg_After_Update_Pno, convert(nvarchar,isnull((select ID  from inserted),'')) , 
			@StrUserDisplayText,@StrBefore,@StrAfter, @Trg_Before_Update_UpdatedBy ,@Trg_Before_Update_UpdatedOn,@Trg_After_Update_MachineName From deleted 

		end

		-- @Trg_Before_Update_Comments ,
		FETCH NEXT FROM xdeleted into @Trg_Before_Update_Pno,@Trg_Before_Update_MedicineDate , @Trg_Before_Update_Frequency,@Trg_Before_Update_Qty,
		@Trg_Before_Update_DAW,@Trg_Before_Update_MedActive,@Trg_Before_Update_StartDate,@Trg_Before_Update_StopDate,
		@Trg_Before_Update_Refill,@Trg_Before_Update_Times,@Trg_Before_Update_Reason,@Trg_Before_Update_UpdatedOn,
		@Trg_Before_Update_UpdatedBy,@Trg_Before_Update_RefillType,@Trg_Before_Update_QuantityType,
		@Trg_Before_Update_Flagactivemed,@Trg_Before_Update_MedicineName,
		@Trg_Before_Update_CareGiver, @Trg_Before_Update_CreatedBy, @Trg_Before_Update_CreatedOn,
		@Trg_Before_Update_RxOTC, @Trg_Before_Update_ReNewdate, @Trg_Before_Update_Remarks,@Trg_Before_Update_RxHistoryMedId,
		@Trg_Before_Update_EncounterID , @Trg_Before_Update_NDCCode ,@Trg_Before_Update_LotNo,
		@Trg_Before_Update_DrugSynonymID,@Trg_Before_Update_DrugID,@Trg_Before_Update_IsSample,@Trg_Before_Update_MachineName 


		-- @Trg_After_Update_Comments ,
		FETCH NEXT FROM xinserted into @Trg_After_Update_Pno,@Trg_After_Update_MedicineDate , @Trg_After_Update_Frequency,@Trg_After_Update_Qty,
		@Trg_After_Update_DAW,@Trg_After_Update_MedActive,@Trg_After_Update_StartDate,@Trg_After_Update_StopDate,
		@Trg_After_Update_Refill,@Trg_After_Update_Times,@Trg_After_Update_Reason,@Trg_After_Update_UpdatedOn,
		@Trg_After_Update_UpdatedBy,@Trg_After_Update_RefillType,@Trg_After_Update_QuantityType,
		@Trg_After_Update_Flagactivemed,@Trg_After_Update_MedicineName,
		@Trg_After_Update_CareGiver, @Trg_After_Update_CreatedBy, @Trg_After_Update_CreatedOn,
		@Trg_After_Update_RxOTC, @Trg_After_Update_ReNewdate, @Trg_After_Update_Remarks,@Trg_After_Update_RxHistoryMedId ,
		@Trg_After_Update_EncounterID , @Trg_After_Update_NDCCode ,@Trg_After_Update_LotNo,
		@Trg_After_Update_DrugSynonymID,@Trg_After_Update_DrugID,@Trg_After_Update_IsSample,@Trg_After_Update_MachineName 


	End 
	Close xdeleted DEALLOCATE xdeleted 
	Close xinserted DEALLOCATE xinserted

Kristen
Test

22859 Posts

Posted - 2010-06-12 : 05:42:15

Post the code for the trigger please

Please put [CODE] tags around it to preserve the formatting.

Kristen
Test

22859 Posts

Posted - 2010-06-12 : 05:51:33

OMG!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! What a ghastly solution .........

Anyways, you are getting this error:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

because of this


convert(nvarchar,isnull((select ID  from inserted),''))

which won't work if there is more than 1 row in [inserted]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources