Sql Server is not responding when update trigger is enabled

AbdulQadir
AbdulQadir used Ask the Experts™
on
Hi experts;

I created an update trigger for a table. This trigger fires when a defined column is updated. And inserts new row. This is working great when i testing. But sql server is not responding when more users starts using application. I could not find any solution. Database working on Compatibility Level 80 and Recursive Triggers Enabled option is true.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
please post the trigger code
does it have SET NOCOUNT ON in it (it should have)

Author

Commented:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER TRIGGER [trg_bakimolustur]
ON [dbo].[BTAKIPH]
FOR UPDATE
AS
DECLARE @GITTAR_OLD AS DATETIME
DECLARE @GITTAR_NEW AS DATETIME
DECLARE @BTAKIPMNO AS FLOAT
DECLARE @SISNO AS VARCHAR
DECLARE @KOD AS VARCHAR
DECLARE @PERYOT AS INT
DECLARE @BTAKIPHNO AS FLOAT
SELECT @BTAKIPHNO=(SELECT ISNULL(MAX(BTAKIPHNO),0)+1 FROM BTAKIPH)
SELECT @BTAKIPMNO=(SELECT I.BTAKIPMNO FROM INSERTED I INNER JOIN BTAKIPH AS B ON B.BTAKIPHNO=I.BTAKIPHNO)
SELECT @SISNO=(SELECT I.SISNO FROM INSERTED I INNER JOIN BTAKIPH AS B ON B.BTAKIPHNO=I.BTAKIPHNO)
SELECT @GITTAR_OLD=(SELECT D.GITTAR FROM DELETED D INNER JOIN BTAKIPH AS B ON B.BTAKIPHNO=D.BTAKIPHNO)
SELECT @GITTAR_NEW=(SELECT I.GITTAR FROM INSERTED I INNER JOIN BTAKIPH AS B ON B.BTAKIPHNO=I.BTAKIPHNO)
SET @PERYOT=0
SET @KOD=(SELECT TOP 1 SUBSTRING(KODU,1,2) FROM BTAKIPM WHERE BTAKIPMNO=@BTAKIPMNO)

BEGIN
	IF @GITTAR_NEW IS NOT NULL
	BEGIN
		--IF (SELECT COUNT(*) FROM BTAKIPH WHERE BAKTAR>@GITTAR_NEW AND BTAKIPMNO=@BTAKIPMNO)=0
		BEGIN
			DECLARE @SONGIDILEN AS INT
			IF @KOD='AW' BEGIN SET @PERYOT=8 END ELSE IF @KOD='AK' BEGIN SET @PERYOT=6 END ELSE BEGIN RETURN END
			SET @SONGIDILEN=(SELECT TOP 1 SUBSTRING(ACIKLAMA,1,1) FROM BTAKIPH WHERE BTAKIPMNO=@BTAKIPMNO AND GITTAR IS NOT NULL AND (SUBSTRING(ACIKLAMA,1,2)='1.' OR SUBSTRING(ACIKLAMA,1,2)='2.' OR SUBSTRING(ACIKLAMA,1,2)='3.' OR SUBSTRING(ACIKLAMA,1,2)='4.') ORDER BY GITTAR DESC)
			DECLARE @ACIKLAMA AS VARCHAR(50)
			IF @SONGIDILEN=1 BEGIN SET @ACIKLAMA='2. BAKIM - 1xFT82 1xFT83 P.CARBON DEGISIMI ' END ELSE IF @SONGIDILEN=2 BEGIN SET @ACIKLAMA='3. BAKIM - 1xFT82 2xFT83 DEGISIMI ' END ELSE IF @SONGIDILEN=3 BEGIN SET @ACIKLAMA='4. BAKIM - 1xFT82 1xFT83 MEMBRAN P.CARBON DEGISIM ' END ELSE IF @SONGIDILEN=4 BEGIN SET @ACIKLAMA='1. BAKIM - 1xFT82 2xFT83 DEGISIMI ' END ELSE BEGIN RETURN END
			INSERT INTO BTAKIPH (BTAKIPMNO,BTAKIPHNO,BAKTAR,SISNO,ACIKLAMA)
			VALUES 
			(
				@BTAKIPMNO,
				@BTAKIPHNO,
				DATEADD(MONTH,@PERYOT,@GITTAR_NEW),
				@SISNO,
				@ACIKLAMA
			)
		END
	END
	IF @GITTAR_OLD IS NOT NULL
	BEGIN
		IF @KOD='AW' BEGIN SET @PERYOT=8 END ELSE IF @KOD='AK' BEGIN SET @PERYOT=6 END ELSE BEGIN RETURN END
		IF (SELECT COUNT(*) FROM BTAKIPH WHERE BAKTAR=DATEADD(MONTH,@PERYOT,@GITTAR_OLD) AND BTAKIPMNO=@BTAKIPMNO AND (KAPALI='H' OR KAPALI IS NULL))=1
		BEGIN
			DELETE FROM BTAKIPH WHERE BAKTAR=DATEADD(MONTH,@PERYOT,@GITTAR_OLD) AND BTAKIPMNO=@BTAKIPMNO
		END
	END
	UPDATE BTAKIPM SET BITTAR=DATEADD(YEAR,3,(SELECT MAX(BAKTAR) FROM BTAKIPH WHERE BTAKIPMNO=@BTAKIPMNO))
END

Open in new window

Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
>        UPDATE BTAKIPM SET BITTAR=DATEADD(YEAR,3,(SELECT MAX(BAKTAR) FROM BTAKIPH WHERE BTAKIPMNO=@BTAKIPMNO))

this is the issue, giving you an endless loop, and updating ALL the records ...
are you sure that is what you need?

Author

Commented:
:) you are right. thak u very much. i missed this row

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial