September 19, 2014 at 6:31 am
I have the following situation , the SRV - APP03 , source database ( MP - OFC ) with a sb2010 table ( in stock sales ) , and another bank , private messenger SRV - HML , target database ( MP - IFC ) , table C_INVENTORY_QUANT_INT .
I made 3 trigger (insert, update and delete ) that the update inventory in " SB2 " table , replica information for " C_INVENTORY_QUANT_INT " table. I did the tests with using the same server , all ok , however on different servers could not. Can anyone help me down the trigger update .
NOTE : Here is using the MP11 - SUP bank that is on the same server - MP IFS , when I copy this to trigger another " SRV - APP03 " server by updating the " SRV - HML " server generates an error because it does not finds the target server .
USE [MP11-SUP]
GO
/****** Object: Trigger [dbo].[trgAlteraEstoqueIFS] Script Date: 09/18/2014 11:10:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:João Lucas Esteves e Estevam
-- Create date: 04/06/2014
-- Description:Change a record in the table C_INVENTORY_QUANT_INT Bank MP - IFS when a record is
- Changed the sb2010 table
-- =============================================
ALTER TRIGGER [dbo].[trgAlteraEstoqueIFS]
ON [MP11-SUP].[dbo].[SB2010]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON
DECLARE
@RECNOINT
SELECT@RECNO = R_E_C_N_O_ FROM inserted
-- Checks if the balance in stock for Product Warehousing and already exists in the table IFS
IF (SELECT COUNT(*)
FROM [SRV-HML].[MP-IFS].[dbo].[C_INVENTORY_QUANT_INT]
WHERE INT_RECNO = @RECNO) = 0
-- If not, creates the record in the table C_INVENTORY_QUANT_INT
BEGIN
INSERT INTO [SRV-HML].[MP-IFS].[dbo].[C_INVENTORY_QUANT_INT]
([INT_PART_NO]
,[INT_DESCRIPTION]
,[INT_QTD_ONHAND]
,[INT_LOCAL_NO]
,[INT_SERIAL_NO]
,[INT_LOT_BATCH_NO]
,[INT_INVENTORY_VALUE]
,[INT_TYPE]
,[INT_RECNO])
SELECTdbo.SB1010.B1_COD,
dbo.SB1010.B1_DESC,
dbo.SB2010.B2_QATU,
dbo.SB2010.B2_LOCAL,
'',
'',
dbo.SB2010.B2_CM1,
'1',
dbo.SB2010.R_E_C_N_O_
FROMdbo.SB2010
INNER JOIN dbo.SB1010 ON dbo.SB1010.B1_COD=dbo.SB2010.B2_COD
WHEREdbo.SB2010.D_E_L_E_T_ <> '*' AND dbo.SB1010.D_E_L_E_T_ <> '*'
AND dbo.SB2010.R_E_C_N_O_ = @RECNO
END
ELSE
-- If exitir Performs the update of stock and average cost in the Item
BEGIN
UPDATE [SRV-HML].[MP-IFS].[dbo].[C_INVENTORY_QUANT_INT]
SET INT_QTD_ONHAND = B2_QATU,
INT_INVENTORY_VALUE = B2_CM1,
INT_TYPE = '2'
FROM [SRV-HML].[MP-IFS].[dbo].[C_INVENTORY_QUANT_INT]
INNER JOIN dbo.SB2010 ON dbo.SB2010.R_E_C_N_O_ = INT_RECNO
INNER JOIN dbo.SB1010 ON dbo.SB1010.B1_COD=dbo.SB2010.B2_COD
WHERE dbo.SB2010.D_E_L_E_T_ <> '*' AND dbo.SB1010.D_E_L_E_T_ <> '*'
AND dbo.SB2010.R_E_C_N_O_ = @RECNO
END
END
September 19, 2014 at 7:57 am
The biggest problem you have is that your trigger cannot handle multiple row operations. You are setting a scalar value from inserted. If an update affects more than 1 row your trigger is not going to work correctly. You need to use set based logic in triggers. I would be concerned about the other two triggers you wrote having this same issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply