Hi,

I have created a new table with name 'testjohn '

CREATE TABLE [dbo].[testjohn](

[ITEMID] [nvarchar](25) NOT NULL,

[AVAILPHYSICAL] [numeric](28, 12) NOT NULL,

[INVENTDIMID] [nvarchar](20) NULL,

[INVENTLOCATIONID] [nvarchar](10) NOT NULL,

[MODIFIEDDATETIME] [datetime] NOT NULL,

CONSTRAINT [I_ITEMINVENTIDX] PRIMARY KEY NONCLUSTERED

(

[ITEMID] ASC,

[INVENTLOCATIONID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

and a new trigger but it seems that code in bold( that checks the record existence) runs only for the first record.

CREATE TRIGGER [dbo].[TR_INVETSUM_new] ON [dbo].[INVENTSUM]

AFTER INSERT,UPDATE

AS

IF UPDATE(AVAILPHYSICAL)

BEGIN

IF NOT EXISTS(SELECT J.ITEMID FROM testjohn J

JOIN INSERTED I ON I.ITEMID=J.ITEMID

JOIN INVENTTABLE IT ON I.ITEMID=IT.ITEMID AND IT.DATAAREAID='SRVC' and it.ITEMGROUPID='parts'

JOIN INVENTDIM d on i.INVENTDIMID=d.INVENTDIMID and d.DATAAREAID='001'

WHERE IT.ITEMID=i.ITEMID

)[/I]

BEGIN

insert into testjohn (ITEMID,INVENTLOCATIONID,AVAILPHYSICAL,MODIFIEDDATETIME)

select I.ITEMID,D.INVENTLOCATIONID,SUM(I.AVAILPHYSICAL),GETDATE()

from

Inserted i inner join

INVENTTABLE IT ON I.ITEMID=IT.ITEMID

JOIN INVENTDIM d on i.INVENTDIMID=d.INVENTDIMID and d.DATAAREAID='001'

WHERE IT.ITEMID=i.ITEMID AND IT.DATAAREAID='SRVC' and it.ITEMGROUPID='parts'

AND d.INVENTLOCATIONID IN ('SPW100','SPN260')

AND LEFT(i.ITEMID,3) IN ('HD-','MM-','FT-')

GROUP BY I.ITEMID,D.INVENTLOCATIONID

END

ELSE

BEGIN

UPDATE t

SET t.ITEMID =i.ITEMID

,t.INVENTLOCATIONID=d.INVENTLOCATIONID

,t.AVAILPHYSICAL =

( select SUM(AVAILPHYSICAL) from INVENTSUM m

JOIN INVENTDIM dd on m.INVENTDIMID=dd.INVENTDIMID and dd.DATAAREAID=m.DATAAREAID

where m.ITEMID=I.ITEMID and m.DATAAREAID='001'

AND dd.INVENTLOCATIONID IN ('SPW100','SPN260') )

from testjohn t

INNER JOIN inserted i ON t.ITEMID =i.ITEMID

join inventtable IT ON IT.ITEMID=i.ITEMID --AND IT.DATAAREAID='SRVC' and it.ITEMGROUPID='parts'

JOIN INVENTDIM d on i.INVENTDIMID=d.INVENTDIMID and d.DATAAREAID='001'

WHERE d.INVENTLOCATIONID IN ('SPW100','SPN260')

AND LEFT(i.ITEMID,3) IN ('HD-','MM-','FT-')

END

thanks