T-SQL TRIGGER QUESTION

Question

0.00/5 (No votes)

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

SQL

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 16:42pm

7prince

Updated 12-Mar-13 17:15pm

v5

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

gvprabu · Accepted Answer · 2013-03-12T18:13:00

Hi,

Try this....

SQL

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

Davidduraisamy · Accepted Answer · 2013-03-12T18:40:00

SQL

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 QUESTION

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0