SQL Server Performance

TRIGGER

Discussion in 'SQL Server 2005 General DBA Questions' started by ELLIOT ANTWI, Jun 1, 2013.

  1. ELLIOT ANTWI New Member

    I want SQL TRIGGER that can update stock table by subtracting the inserted value of Product table from the Stock table. Please it is the Quantity column of the Product table I want to subtract from the Qauntity_Instock on the Stock table any time values are inserted into the Product table. Below is the table codes

    create table Stock
    (
    Item_Code nvarchar(25)not null,
    Item_Name varchar(50)not null,
    Date dateTime not null,
    Qauntity_Instock int
    )

    create table Product
    (
    Product_Name nvarchar(50)not null,
    Product_Code nvarchar(25)not null,
    Sales_Date dateTime not null,
    Quantity int not null,
    Unit_Price decimal not null,
    )
    Please kindly factor in multiple columns.
    Any help would be appreciated. Thanks
  2. FrankKalis Moderator

    Welcome to the forums!
    This suspiciously looks like a homework assignment, which you really should solve yourself. For a start, search for "INSERTED" and "DELETED". The rest is not all that hard to figure out.
  3. ELLIOT ANTWI New Member

    CREATE TRIGGER [trgUpdateStock]
    ON [dbo].[Transaction_Details]
    FOR INSERT
    AS
    BEGIN

    declare @ProductCode varchar(25)
    declare @Qty smallint
    declare @TransactionID varchar(25)
    declare @StockItemQuantity as smallint

    select @ProductCode = inserted.Product_Code from inserted
    select @Qty = inserted.Quantity from inserted


    Update dbo.Stock
    set Qauntity_Instock = Qauntity_Instock - @Qty
    where Product_Code = @ProductCode

    PRINT 'Done'
    END

    For now this is what I have done but when I insert values into the
    transaction table is not able to deduct the inserted values from the
    Stock table. So please modify the code for me and is not home work.
    Thanks.
  4. FrankKalis Moderator

    Okay, I'm not entirely sure, that your trigger fires on the correct event. I would have assumed that you would want an AFTER UPDATE trigger or something like that. Also, your trigger can handle only one product at a time, which may or may not be what you want. If you need to handle multiple products at once, you need something like this:

    Code:
    UPDATE
        S
    SET
        S.Quantity_Stock = S.Quantity_Stock - I.Quantity
    FROM
        dbo.Stock S
        JOIN INSERTED I ON S.Product_Code = I.Product_Code;

Share This Page