September 16, 2013 at 10:29 am
Hi all,
I have the following trigger after AvailPhysical field is updated.
Although I have written that table field ITEMID must start from 'HD-' or 'MM-' or 'FT-' and ITEMGROUPID must be 'parts' it works for every ITEMID and every ITEMGROUPID and not only for them that have the above criteria.
I have tried to add one more condition for ITEMID before cursor starts but again with no results.
AFTER INSERT,UPDATE
AS
IF UPDATE(AVAILPHYSICAL)
BEGIN
DECLARE @ITEMID NVARCHAR(21)
DECLARE @AVAILPHYSICAL NUMERIC(15,2)
DECLARE @INVENTLOCATIONID NVARCHAR(15)
DECLARE @ITEMGROUPID NVARCHAR(15)
DECLARE @INVENTDIMID NVARCHAR(15)
DECLARE outerCursor1 CURSOR FOR
SELECT
S.itemid, SUM(AVAILPHYSICAL),INVENTLOCATIONID
FROM INSERTED S
join inventtable IT ON IT.ITEMID=S.ITEMID AND IT.DATAAREAID='SRVC' and it.ITEMGROUPID='parts'
JOIN INVENTDIM d on S.INVENTDIMID=d.INVENTDIMID and d.DATAAREAID=S.DATAAREAID
WHERE d.DATAAREAID='001' AND d.INVENTLOCATIONID IN ('SPW100','SPN260')
AND LEFT(S.ITEMID,3) IN ('HD-','MM-','FT-')
GROUP BY S.ITEMID,d.INVENTLOCATIONID--,IT.ITEMGROUPID
OPEN outerCursor1
FETCH NEXT FROM outerCursor1 INTO @ITEMID, @AVAILPHYSICAL,@INVENTLOCATIONID
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT ITEMID,INVENTLOCATIONID FROM ESHOPINVENTSUM WHERE ITEMID = @ITEMID AND INVENTLOCATIONID=@INVENTLOCATIONID )
BEGIN
Insert into dbo.TEMPINVENTSUM (ITEMID,AVAILPHYSICAL,INVENTLOCATIONID )
Select @ITEMID,@AVAILPHYSICAL,@INVENTLOCATIONID
END
ELSE
BEGIN
UPDATE TEMPINVENTSUM
SET ESHOPINVENTSUM.ITEMID =@ITEMID
,ESHOPINVENTSUM.INVENTLOCATIONID=@INVENTLOCATIONID
,ESHOPINVENTSUM.AVAILPHYSICAL = @AVAILPHYSICAL
WHERE ESHOPINVENTSUM.ITEMID =@ITEMID AND ESHOPINVENTSUM.INVENTLOCATIONID=@INVENTLOCATIONID
END
FETCH NEXT FROM outerCursor1
INTO @ITEMID, @AVAILPHYSICAL,@INVENTLOCATIONID
END
CLOSE outerCursor1
DEALLOCATE outerCursor1
END
Please help.
Thanks
September 16, 2013 at 10:44 am
You don't need a cursor at all, just an UPDATE followed by an INSERT (or MERGE, but some people have run into performance issues with MERGE).
The table names aren't consistent, you don't identify which table the trigger is on and not all column names include the table name, so I can't do a rewrite yet. But this can definitely be done w/o the cursor.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 16, 2013 at 11:51 am
Hi,
Our ERP is Microsoft Dynamics AX (DAX). The trigger is on table InventSum
CREATE TABLE [dbo].[INVENTSUM](
[ITEMID] [nvarchar](25) NOT NULL,
[POSTEDQTY] [numeric](28, 12) NOT NULL,
[POSTEDVALUE] [numeric](28, 12) NOT NULL,
[DEDUCTED] [numeric](28, 12) NOT NULL,
[RECEIVED] [numeric](28, 12) NOT NULL,
[RESERVPHYSICAL] [numeric](28, 12) NOT NULL,
[RESERVORDERED] [numeric](28, 12) NOT NULL,
[ONORDER] [numeric](28, 12) NOT NULL,
[ORDERED] [numeric](28, 12) NOT NULL,
[QUOTATIONISSUE] [numeric](28, 12) NOT NULL,
[QUOTATIONRECEIPT] [numeric](28, 12) NOT NULL,
[INVENTDIMID] [nvarchar](20) NOT NULL,
[CLOSED] [int] NOT NULL,
[REGISTERED] [numeric](28, 12) NOT NULL,
[PICKED] [numeric](28, 12) NOT NULL,
[AVAILORDERED] [numeric](28, 12) NOT NULL,
[AVAILPHYSICAL] [numeric](28, 12) NOT NULL,
[PHYSICALVALUE] [numeric](28, 12) NOT NULL,
[ARRIVED] [numeric](28, 12) NOT NULL,
[PHYSICALINVENT] [numeric](28, 12) NOT NULL,
[CLOSEDQTY] [int] NOT NULL,
[LASTUPDDATEPHYSICAL] [datetime] NOT NULL,
[LASTUPDDATEEXPECTED] [datetime] NOT NULL,
[DATAAREAID] [nvarchar](4) NOT NULL,
[RECVERSION] [int] NOT NULL,
[RECID] [bigint] NOT NULL) ON [PRIMARY]
I want everytime AVAILPHYSICAL is modified to execute the trigger. My problem is when the trigger is being executed for multiple rows. For example a purchase order can have 50 Items.
First I used this trigger but field AVAILPHYSICAL was updated/inserted only for the first record.
Table TEMPINVENTSUM has the same fields with INVENTSUM
DECLARE @ITEMID NVARCHAR(21)
DECLARE @AVAILPHYSICAL NUMERIC(15,2)
DECLARE @INVENTLOCATIONID NVARCHAR(15)
DECLARE @ITEMGROUPID NVARCHAR(15)
DECLARE @INVENTDIMID NVARCHAR(15)
DECLARE @cmd sysname
IF ( UPDATE(AVAILPHYSICAL) or UPDATE(INVENTDIMID) ) --AND LEFT((SELECT ITEMID FROM INSERTED), 3)='HD-' )
BEGIN
select @ITEMID=S.ITEMID
,@AVAILPHYSICAL =(
select SUM(AVAILPHYSICAL) from INVENTSUM m
JOIN INVENTDIM dd on m.INVENTDIMID=dd.INVENTDIMID and dd.DATAAREAID=m.DATAAREAID
where m.ITEMID=S.ITEMID and m.DATAAREAID='001'
AND dd.INVENTLOCATIONID IN ('SPW100','SPN260')
)
,@INVENTLOCATIONID = d.INVENTLOCATIONID
,@ITEMGROUPID=IT.ITEMGROUPID
--,@INVENTDIMID=D.INVENTDIMID
From inserted S
join inventtable IT ON IT.ITEMID=S.ITEMID AND IT.DATAAREAID='SRVC' and it.ITEMGROUPID='parts'
JOIN INVENTDIM d on S.INVENTDIMID=d.INVENTDIMID and d.DATAAREAID=S.DATAAREAID
WHERE d.DATAAREAID='001' AND d.INVENTLOCATIONID IN ('SPW100','SPN260') --IN (SELECT InventLocationId FROM InventLocation WHERE DATAAREAID='001')
AND LEFT(S.ITEMID,3) IN ('HD-','MM-','FT-')
GROUP BY S.ITEMID,d.INVENTLOCATIONID,IT.ITEMGROUPID--,D.INVENTDIMID
IF @ITEMGROUPID='PARTS'
BEGIN --IF
IF NOT EXISTS (SELECT ITEMID,INVENTLOCATIONID FROM ESHOPINVENTSUM WHERE ITEMID = @ITEMID AND INVENTLOCATIONID=@INVENTLOCATIONID )
BEGIN
Insert into dbo.TEMPINVENTSUM (ITEMID,INVENTLOCATIONID,AVAILPHYSICAL)
Select @ITEMID,@INVENTLOCATIONID,@AVAILPHYSICAL
END
ELSE
BEGIN
UPDATE TEMPINVENTSUM
SET ESHOPINVENTSUM.ITEMID =@ITEMID
,ESHOPINVENTSUM.INVENTLOCATIONID=@INVENTLOCATIONID
,ESHOPINVENTSUM.AVAILPHYSICAL = @AVAILPHYSICAL
WHERE ESHOPINVENTSUM.ITEMID =@ITEMID AND ESHOPINVENTSUM.INVENTLOCATIONID=@INVENTLOCATIONID
END
END --IF
END
Thanks
September 16, 2013 at 12:08 pm
The problem now is that you can't handle multiple row transactions. You don't need all those variables. Just get the values from table directly. Remember that a triggers fires once for the entire operation not once per row. I suspect you might to take a look at MERGE for this. With no details to work with we can't offer much in the way of specific coding help.
_______________________________________________________________
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/
September 16, 2013 at 12:22 pm
UPDATE TEMPINVENTSUM
SET ESHOPINVENTSUM.ITEMID =@ITEMID
,ESHOPINVENTSUM.INVENTLOCATIONID= @INVENTLOCATIONID
,ESHOPINVENTSUM.AVAILPHYSICAL = @AVAILPHYSICAL
WHERE ESHOPINVENTSUM.ITEMID =@ITEMID AND ESHOPINVENTSUM.INVENTLOCATIONID= @INVENTLOCATIONID
I don't see table "ESHOPINVENTSUM" in the query. Is it supposed to be TEMPINVENTSUM? Or is there another table name "ESHOPINVENTSUM" that provides values for the UPDATE??
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 16, 2013 at 12:24 pm
Hi,
It is TEMPINVENTSUM.
Thanks
September 16, 2013 at 1:03 pm
I think this rewrites the UPDATE; don't have time now to do the INSERT. The code is somewhat confusing in that sometimes ITEMID is matched on and other times maybe not, or I'm reading it wrong. At any rate, see if the SQL below produces the correct totals.
IF ( UPDATE(AVAILPHYSICAL) or UPDATE(INVENTDIMID) ) --AND LEFT((SELECT ITEMID FROM INSERTED), 3)='HD-' )
BEGIN
UPDATE TEMPINVENTSUM
SET TEMPINVENTSUM.AVAILPHYSICAL = INVENTSUM_TOTAL.AVAILPHYSICAL
FROM TEMPINVENTSUM
INNER JOIN (
SELECT
S.ITEMID,d.INVENTLOCATIONID,D.INVENTDIMID,
SUM(AVAILPHYSICAL) AS AVAILPHYSICAL
FROM inserted S
INNER JOIN INVENTDIM d ON S.ITEMID = d.ITEMID AND S.INVENTDIMID=d.INVENTDIMID AND d.DATAAREAID=S.DATAAREAID
WHERE
(S.ITEMID LIKE 'HD-%' OR S.ITEMID LIKE 'MM-%' OR S.ITEMID LIKE 'FT-%') AND
S.DATAAREAID='001' AND
d.DATAAREAID='001' AND
d.INVENTLOCATIONID IN ('SPW100','SPN260') AND
EXISTS(SELECT 1 FROM inventtable IT WHERE IT.ITEMID=S.ITEMID AND IT.DATAAREAID='SRVC' AND it.ITEMGROUPID='parts')
GROUP BY S.ITEMID,d.INVENTLOCATIONID,D.INVENTDIMID
) AS INVENTSUM_TOTAL ON
INVENTSUM_TOTAL.ITEMID = TEMPINVENTSUM.ITEMID AND
INVENTSUM_TOTAL.INVENTLOCATIONID = TEMPINVENTSUM.INVENTLOCATIONID AND
INVENTSUM_TOTAL.INVENTDIMID = TEMPINVENTSUM.INVENTDIMID
--!! !!--
--!!INSERT statement goes here!!--
--!! !!--
END --IF
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 16, 2013 at 2:08 pm
Thanks, I will try Insert Statement.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply