Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
How can I join the INVENTORY table to get stockqty from INSERTED table as well? Currently, it gives me an error?: Incorrect syntax near the keyword
IF EXISTS (SELECT name FROM SYSOBJECTS WHERE name = 'OrderitemsInsertTRG')
    BEGIN DROP TRIGGER OrderitemsInsertTRG; END;
GO
 
CREATE TRIGGER OrderitemsInsertTRG
ON ORDERITEMS
FOR INSERT
AS
 
DECLARE  
@qty INT,  --In the ORDERITEMS table
@Partid INT,
@Stockqty INT
BEGIN 
    -- get new values for qty and partid from the INSERTED table
    -- get current (changed) StockQty for this PartID
    -- UPDATE with current (changed) StockQty 
    
     SELECT @Partid =(SELECT partid FROM INSERTED)
     SELECT @qty = (SELECT qty FROM INSERTED)
   
    INNER JOIN  INVENTORY I   -----stockQty is in the INVENTORY table
    ON I.partid = @Partid
              
              UPDATE INVENTORY
    SET stockqty = stockqty - qty
    WHERE partid = @Partid; 
   
END;   
GO
 
-- testing blocks for OrderItemsInsertTrg  
BEGIN
END;
GO
Posted 12-Mar-13 17:42pm
7prince409
Edited 12-Mar-13 18:15pm
v5
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
Try this....
IF OBJECT_ID('OrderitemsInsertTRG') IS NOT NULL DROP TRIGGER OrderitemsInsertTRG
GO
 CREATE TRIGGER OrderitemsInsertTRG
ON ORDERITEMS
FOR INSERT
AS
    DECLARE  @qty INT,  --In the ORDERITEMS table
		   @Partid INT,
		   @Stockqty INT
BEGIN 
    -- get new values for qty and partid from the INSERTED table
    -- get current (changed) StockQty for this PartID
    -- UPDATE with current (changed) StockQty 
    
     SELECT @Partid = partid, @qty = qty
     FROM INSERTED
 
     UPDATE INVENTORY SET stockqty = stockqty - qty 
     WHERE partid = @Partid
END  
Regards,
GVPrabu
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

IF EXISTS (SELECT name FROM SYSOBJECTS WHERE name = 'OrderitemsInsertTRG')
    BEGIN DROP TRIGGER OrderitemsInsertTRG; END;
GO
 
CREATE TRIGGER OrderitemsInsertTRG
ON ORDERITEMS
FOR INSERT
AS
 
DECLARE  
@qty INT,  --In the ORDERITEMS table
@Partid INT,
@Stockqty INT
BEGIN 
    -- get new values for qty and partid from the INSERTED table
    -- get current (changed) StockQty for this PartID
    -- UPDATE with current (changed) StockQty 
    
     SELECT @Partid =i.partid FROM INSERTED i)
     SELECT @qty =i.qty FROM INSERTED i)
 
    UPDATE INVENTORY
    SET stockqty = stockqty - qty
    WHERE partid = @Partid; 
   
END;   
GO
 
-- testing blocks for OrderItemsInsertTrg  
BEGIN
END;
GO
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 400
1 Jochen Arndt 200
2 Richard MacCutchan 135
3 DamithSL 95
4 Maciej Los 95
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,032
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,220


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 13 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100