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?
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