July 10, 2012 at 1:03 am
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
July 10, 2012 at 11:59 am
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
July 11, 2012 at 8:51 am
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply