Need help with the following trigger

[cba321]

The problem is it is not writting to ERROR_LIST table when the update is not done. Can you help me with the syntax.

CREATE TRIGGER SP_A ON dbo.B
FOR INSERT, UPDATE
AS
If UPDATE(STREAM_THROUGHPUT)

BEGIN

-- Declare variables for error handling
DECLARE @ROWCOUNT AS int
DECLARE @RETURN AS int
-----------------------------------------------------------------

Begin

-- loop 1
UPDATE dbo.ESP_MAT_STREAM_INFO
SET dbo.ESP_MAT_STREAM_INFO.VERSION_NO = (SELECT dbo.ESP_MAT_PROPERTY.VERSION_NO
FROM dbo.ESP_MAT_STREAM_INFO INNER JOIN inserted i
ON i.PROCESS_STREAM_ID = dbo.ESP_MAT_STREAM_INFO.PROCESS_STREAM_ID
AND i.BEGIN_DATE < dbo.ESP_MAT_STREAM_INFO.BEGIN_DATE
AND i.END_DATE <= dbo.ESP_MAT_STREAM_INFO.END_DATE
AND i.SITE_ID = dbo.ESP_MAT_STREAM_INFO.SITE_ID
INNER JOIN dbo.ESP_MAT_PROPERTY
ON i.BEGIN_DATE >= dbo.ESP_MAT_PROPERTY.BEGIN_DATE
AND i.END_DATE <= dbo.ESP_MAT_PROPERTY.END_DATE
AND dbo.ESP_MAT_STREAM_INFO.MATCHEM_ID = dbo.ESP_MAT_PROPERTY.MATCHEM_ID)

FROM dbo.ESP_MAT_STREAM_INFO INNER JOIN inserted i
ON i.PROCESS_STREAM_ID = dbo.ESP_MAT_STREAM_INFO.PROCESS_STREAM_ID
AND i.BEGIN_DATE >= dbo.ESP_MAT_STREAM_INFO.BEGIN_DATE
AND i.END_DATE <= dbo.ESP_MAT_STREAM_INFO.END_DATE
AND i.SITE_ID = dbo.ESP_MAT_STREAM_INFO.SITE_ID
-- Error handling process start for loop 1

SELECT @ROWCOUNT = @@ROWCOUNT
INSERT INTO ERROR_LIST (IN_UP_ROWCOUNT) values (@ROWCOUNT)
END
END

 

[rac2]

Isn't @@ROWCOUNT equal to zero when the UPDATE fails, since no rows are affected. If so then you should get a row added to ERROR_LIST with a value of zero for IN_UP_ROWCOUNT.

Is that what you mean by &quot;it is not writting to ERROR_LIST table&quot;?

Maybe the UPDATE statement is not the source of the problem. Could you post the code that follows the UPDATE?



Also, this would not cause an error, but I would never create a variable named @ROWCOUNT because it is so close to the reserved name @@ROWCOUNT. If it doesn't confuse T-SQL it sure would confuse me and the programmer that comes after me to maintain my code.

 

[cba321]

Dear Rac2,

After much of testing, I came to know that the update statement is always giving @@Rowcount as 1. It was surprising that when the update statement did not update, the @@rowcount was 1. I went ahead and changed the business logic to set version number to null, to help clear out the existing value. Then i updated the value from join. To check the join worked fine i check for value of the version number to see if its not null and wrote the error message to the error table.
Yes, as sugested by you i will stop using @rowcount.
Thanks,
cba321