Set Xact_abort On

[Tom Frohman]

Newbie question
I have an Update trigger and an Insert trigger on a table both of which use a variant of the code below. The trigger takes information from the table and updates information in another database configured as a linked server.


The update trigger works fine without the Set XACT_ABORT ON statement.
The insert trigger crashes unless I put this line in.

The update trigger runs 10 times faster without the statement.
Is there an alternative to this that will allow the insert trigger to run without crashing? and run faster. The insert trigger takes forever to run now. (Forever being about 20 seconds). The Update trigger takes about 2 seconds without the Set XACT_ABORT ON statement and 20 seconds with it.

Any help would be appreciated.
Tom

Set XACT_ABORT ON
			
Select @pos=COUNT(*)	From SQLPSPRD.DTEFSPRD.dbo.PS_GCC_CONTACT_DTL
where UPPER(GCC_CONTACT_ID)=UPPER(RTRIM(@tmpcompid2)) and GCC_CONTACT_SEQ=@maxseq2
            
    

if(@pos>0)
	begin


	Update SQLPSPRD.DTEFSPRD.dbo.PS_GCC_CONTACT_DTL  
 	set GCC_BUSINESS_UNIT=UPPER(@tmpbus),GCC_CONTACT_NAME=isnull(@nameout,' '),
	GCC_CONTACT_ID=@tmpcompid,GCC_CONTACT_SEQ=@maxseq,
	GCC_CONTACT_CMPY=isnull(UPPER(@compout),' '),GCC_CONTACT_ADDR1=isnull(@add1out,' '),
	GCC_CONTACT_ADDR2=isnull(@add2out,' '),GCC_CONTACT_ADDR3=isnull(@add3out,' '),

	GCC_CONTACT_CITY=isnull(@tmpcity,' '),GCC_CONTACT_STATE=isnull(UPPER(@tmpstate),' '),
	GCC_CONTACT_ZIP=isnull(UPPER(@zipout),' '),GCC_CONTACT_CNTRY=isnull(UPPER(@ctryout),' '),
	GCC_CONTACT_PHONE=isnull(UPPER(@tel1out),' '),GCC_CONTACT_FAX=isnull(UPPER(@tel2out),' '),GCC_CONTACT_CELL=isnull(UPPER(@tel3out),' '),
	GCC_CONTACT_EMAIL=isnull(@emailout,' '),GCC_EFF_STATUS=@outstatus,GCC_OPRID=isnull(@mgrout,' '),GCC_DTTM_STAMP=CONVERT(Char, GetDate(), 101)
	where UPPER(RTRIM(@tmpcompid2))=UPPER(GCC_CONTACT_ID) and @maxseq2=GCC_CONTACT_SEQ  


	end
else	
	begin
	Select @rcount=count(GCC_CONTACT_SEQ)
	From SQLPSPRD.DTEFSPRD.dbo.PS_GCC_CONTACT_DTL
	where UPPER(GCC_CONTACT_ID)=UPPER(RTRIM(@tmpcompid)) AND GCC_CONTACT_SEQ=@maxseq AND UPPER(RTRIM(@nameout))=UPPER(RTRIM(GCC_CONTACT_NAME)) 

	
            

    if(@rcount<1)
		begin

		Insert into SQLPSPRD.DTEFSPRD.dbo.PS_GCC_CONTACT_DTL 
		(GCC_BUSINESS_UNIT,GCC_CONTACT_ID,GCC_CONTACT_SEQ,GCC_EFFDT,GCC_CONTACT_NAME,GCC_CONTACT_CMPY,GCC_CONTACT_TYPE,GCC_CONTACT_ADDR1,GCC_CONTACT_ADDR2,GCC_CONTACT_ADDR3,GCC_CONTACT_ADDR4,GCC_CONTACT_CITY,GCC_CONTACT_CNTY,GCC_CONTACT_STATE,GCC_CONTACT_ZIP,GCC_CONTACT_CNTRY,GCC_CONTACT_PHONE,GCC_CONTACT_FAX,GCC_CONTACT_CELL, GCC_CONTACT_EMAIL,GCC_EFF_STATUS,GCC_OPRID,GCC_DTTM_STAMP) 
		values(UPPER(@tmpbus),UPPER(@tmpcompid),@maxseq,CONVERT(Char, GetDate(), 101),isnull(@nameout,' '),
        isnull(UPPER(@compout),' '),'E',isnull(@add1out,' '),isnull(@add2out,' '),isnull(@add3out,' '),'  ',isnull(@tmpcity,' '),'  ',
        isnull(UPPER(@tmpstate),' '),isnull(UPPER(@zipout),' '),isnull(UPPER(@ctryout),' '),isnull(UPPER(@tel1out),' '),isnull(UPPER(@tel2out),' '),isnull(UPPER(@tel3out),' '),
        isnull(@emailout,' '),@outstatus,isnull(@mgrout,' '),CONVERT(Char, GetDate(), 101))
		end

	end

                    

end

[Krzemo]

This could help:
Move body of this triggers into stored procs on target db. Leave only EXECUTE statesment.

Best regards,
Krzemo.