update tigger not fire working

  • deba_20032004

    Default port

    Points: 1476

    Dear Sir,

    I have written a trigger which includes three action insert,update,delete

    when i insert a record in a table manually then the trigger fires but when i update qty of the item then it doesn't work why it's happen it any idea thanks in advance

    here is attachment of the code

  • Jack Corbett

    SSC Guru

    Points: 184380

    First of all, with the way this trigger is written you should add:

    IF @@ROWCOUNT > 1

    BEGIN;

    RAISERROR("This trigger does not allow set-based modifications", 16, 1)

    ROLLBACK TRANSACTION

    END;

    There's a small bug in the UPDATE section:

    IF @Action='U'

    --This is an Update Record Action

    --

    DECLARE @nQTY DECIMAL(8,3)

    DECLARE @QTYBAL DECIMAL(8,3)

    SELECT @nQTY FROM INSERTED

    SELECT @CO=COID,@QTY=QTY,@PRODUCTID=PRODUCTID FROM DELETED

    SET @QTYBAL=@QTY-@nQTY

    BEGIN

    UPDATE PRODUCTST SET QTYIN=QTYIN+@QTYBAL,QTYBAL=QTYBAL+@QTYBAL

    WHERE PRODUCTID=@PRODUCTID

    END

    The bolded and underlined line shows that @nQTY is never being set, so when you set @QTYBAL you get NULL

    I'd probably re-write the trigger like this (I assumed that productID was a PK or Unique):

    ALTER TRIGGER [tr_UpdateProductSt] ON [dbo].[CHALLANDETAILS]

    AFTER INSERT, UPDATE, DELETE

    AS

    IF @@ROWCOUNT = 0

    RETURN;

    SET NOCOUNT ON;

    /* Update */

    IF EXISTS ( SELECT

    1

    FROM

    INSERTED AS I

    JOIN DELETED D

    ON I.productID = D.productID )

    BEGIN;

    UPDATE

    PRODUCTST

    SET

    QTYIN = QTYIN + D.Qty - I.qty,

    QTYBAL = QTYBAL + D.Qty - I.qty

    FROM

    INSERTED AS I

    JOIN DELETED AS D

    ON I.productID = D.productID

    WHERE

    PRODUCTST.PRODUCTID = I.PRODUCTID;

    END;

    /* INSERT */

    IF EXISTS ( SELECT

    1

    FROM

    INSERTED AS I

    WHERE

    NOT EXISTS ( SELECT

    1

    FROM

    DELETED AS D

    WHERE

    I.productID = D.productID ) )

    BEGIN;

    IF EXISTS ( SELECT

    1

    FROM

    PRODUCTST AS P

    WHERE

    EXISTS ( SELECT

    1

    FROM

    INSERTED AS I

    WHERE

    P.productID = I.productID ) )

    BEGIN;

    UPDATE

    PRODUCTST

    SET

    QTYIN = QTYIN + I.qty,

    QTYBAL = I.qty

    FROM

    INSERTED AS I

    JOIN DELETED AS D

    ON I.productID = D.productID

    WHERE

    PRODUCTST.PRODUCTID = I.PRODUCTID;

    END;

    ELSE

    BEGIN;

    INSERT INTO PRODUCTST

    (

    COID,

    PRODUCTID,

    QTYIN,

    QTYBAL

    )

    SELECT

    i.COID,

    i.PRODUCTID,

    i.QTY,

    i.QTY

    FROM

    INSERTED i;

    END;

    END;

    /* DELETE */

    IF EXISTS ( SELECT

    1

    FROM

    DELETED AS D

    WHERE

    NOT EXISTS ( SELECT

    1

    FROM

    inserted AS I

    WHERE

    I.productID = D.productID ) )

    BEGIN;

    DELETE FROM

    PRODUCTST

    WHERE

    EXISTS ( SELECT

    1

    FROM

    DELETED AS D

    WHERE

    PRODUCTST.COID = D.COID AND

    PRODUCTST.PRODUCTID = D.PRODUCTID );

    END;

    RETURN

    Actually I'd probably break into 3 separate triggers, but if I kept is as 1 that's how I'd do it.

    Now if you are on 2008+ then you could do one trigger with a MERGE.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • TheSQLGuru

    SSC Guru

    Points: 134017

    1) As Jack pointed out, you REALLY need to do SET BASED processing in your triggers

    2) I would absolutely have THREE triggers for this stuff - one each for INSERT/UPDATE/DELETE. That way you can simply do the required action instead of wasting server resources trying to figure out which section of code to run.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply