T-SQL TRIGGER问题 [英] T-SQL TRIGGER QUESTION

问题描述

如何加入INVENTORY表以从INSERTED表中获取stockqty？目前，它给我一个错误？：关键字附近的语法不正确

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

推荐答案

试试这个....

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  

问候，

GVPrabu

Regards,
GVPrabu

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

这篇关于T-SQL TRIGGER问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！