RE: Trigger and RowsAffected

Chan — Mon, 30 Jul 2007 09:58:03 GMT

Hi,
I am using dynamic SQL. I have trigger as below which will call another stored procedure. If I disable this trigger, RecordsAffected will be correct value. Any ideas?

[codesnippet]CREATE TRIGGER [dbo].[T_PurchaseInvoices_UpdateQty]
ON [dbo].[PurchaseInvoices]
FOR UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here

IF UPDATE(LocationID) OR EXISTS(SELECT * FROM deleted)
BEGIN
SELECT * INTO #inserted_tmp FROM inserted
SELECT * INTO #deleted_tmp FROM deleted

EXEC spHeaderUpdateQty 'PurchaseInvoiceDetails', 'PInvoiceID', default, '+', 'InStock'
END

SET NOCOUNT OFF

END

CREATE PROCEDURE [dbo].[spHeaderUpdateQty]
@tablename VARCHAR(100),
@primarykey VARCHAR(100),
@locationfield VARCHAR(100) = 'LocationID',
@addminus CHAR(1),
@uom VARCHAR(20) = ''
AS

BEGIN
DECLARE
@row_count INT,
@error_number INT,
@error_message VARCHAR(255),
@sql VARCHAR(4000)

SET NOCOUNT ON;

SET @sql = 'SELECT i.ItemID
FROM ' + @tablename + ' p INNER JOIN Items i ON p.ItemID = i.ItemID
INNER JOIN #inserted_tmp t ON p.' + @primarykey + ' = t.' + @primarykey +
' WHERE KeepStock = 1'

EXEC (@sql)

SELECT @row_count = @@rowcount

IF (@row_count > 0)
BEGIN
SET @sql = 'UPDATE [dbo].[ItemLocations]
SET Qty = CASE p.Draft WHEN 1 THEN i.Qty ELSE i.Qty ' + @addminus + '
ISNULL(dbo.GetQtyByUOM(t.UOMID, s.' + @uom + 'UOMID, t.Qty), t.Qty) END
FROM [dbo].[ItemLocations] i, [dbo].[' + @tablename + '] t, [#inserted_tmp] p,
[dbo].[Items] s
WHERE s.ItemID = t.ItemID AND s.KeepStock = 1 AND
p.' + @primarykey + ' = t.' + @primarykey +
' AND i.ItemID = t.ItemID AND i.LocationID = p.' + @locationfield

EXEC (@sql)

SELECT @row_count = @@rowcount
IF @row_count = 0
BEGIN
SELECT @error_number=50001,
@error_message='No item qty is updated.'
GOTO error
END
ELSE
PRINT 'Qty updated'
END

SET @sql = 'SELECT i.ItemID
FROM ' + @tablename + ' p INNER JOIN Items i ON p.ItemID = i.ItemID
INNER JOIN #deleted_tmp t ON p.' + @primarykey + ' = t.' + @primarykey +
' WHERE KeepStock = 1'

EXEC (@sql)

SELECT @row_count = @@rowcount

IF (@row_count > 0)
BEGIN
IF (@addminus = '+')
BEGIN
SET @addminus = '-'
END
ELSE
BEGIN
SET @addminus = '+'
END

SET @sql = 'UPDATE [dbo].[ItemLocations]
SET Qty = CASE p.Draft WHEN 1 THEN i.Qty ELSE i.Qty ' + @addminus + '
ISNULL(dbo.GetQtyByUOM(t.UOMID, s.' + @uom + 'UOMID, t.Qty), t.Qty) END
FROM [dbo].[ItemLocations] i, [dbo].[' + @tablename + '] t, [#deleted_tmp] p,
[dbo].[Items] s
WHERE s.ItemID = t.ItemID AND s.KeepStock = 1 AND
p.' + @primarykey + ' = t.' + @primarykey +
' AND i.ItemID = t.ItemID AND i.LocationID = p.' + @locationfield

EXEC (@sql)

SELECT @row_count = @@rowcount
IF @row_count = 0
BEGIN
SELECT @error_number=50001,
@error_message='No item qty is reverted.'
GOTO error
END
ELSE
PRINT N'Qty reverted.'
END

SET NOCOUNT OFF

RETURN

/* Error Handling */
error:
RAISERROR @error_number @error_message
ROLLBACK TRANSACTION

SET NOCOUNT OFF

END
[/codesnippet]

RE: Trigger and RowsAffected

Ben Chase — Mon, 30 Jul 2007 09:08:07 GMT

If you want the number of affected records to be returned by the update sproc, but not the trigger, then call SET NOCOUNT ON at the top of the trigger and SET NOCOUNT OFF at the bottom of the trigger and call SET NOCOUNT OFF at the top of your UPDATE sproc and SET NOCOUNT ON at the bottom of the update sproc.

RE: Trigger and RowsAffected

Chan — Sun, 29 Jul 2007 05:38:35 GMT

Hi,
I tried to SET NOCOUNT ON but doesn't work.

Any ideas?

Trigger and RowsAffected

Chan — Sun, 29 Jul 2007 05:34:27 GMT

Hi,
How to prevent anything inside trigger affect value SqlDataReader.RecordsAffected?

Thank you

StrataFrame Forum / General .NET Discussion / .NET Forums / Trigger and RowsAffected / Latest Posts

RE: Trigger and RowsAffected

RE: Trigger and RowsAffected

RE: Trigger and RowsAffected

Trigger and RowsAffected