Facing some problem in Instead of Trigger for MultiRow Insert

[santoshborfalkar]

This is the Trigger which is not working properly during Update, no any
record is going to be updated so pls help.

I am updating the requisition table when any update in quantity in
podetails table

CREATE TRIGGER trig_updateRequ istion ON ERP.DBO.TranPur chaseOrderDetai l
INSTEAD OF UPDATE

AS

IF UPDATE(Quantity )

BEGIN

Update RequisitionSlip Detail
set RequisitionSlip Detail.PoQuanti ty =

(Select PoQuantity from RequisitionSlip Detail where
ItemCode=(Selec t CAST(i.ItemCode as nvarchar(20)) from inserted as i)
and
RSlip_No=(Selec t CAST(i.RSlip_No as int) from inserted as i)
)
-

((Select Quantity from TranPurchaseOrd erDetail where Purchase_OrderN o
=
(Select CAST(i.Purchase _OrderNo as nvarchar(20)) from inserted as i))

- (Select CAST(i.Quantity as int) from inserted as i))

where RequisitionSlip Detail.ItemCode = (Select CAST(i.ItemCode as
nvarchar(20)) from inserted as i) and RequisitionSlip Detail.RSlip_No =
(Select CAST(i.RSlip_No as int) from inserted as i)

Update TranPurchaseOrd erDetail set
TranPurchaseOrd erDetail.Quanti ty =
(Select CAST(i.Quantity as int) from inserted as i)
where TranPurchaseOrd erDetail.Purcha se_OrderNo = (Select
CAST(i.Purchase _OrderNo as nvarchar(20)) from inserted as i)
and
TranPurchaseOrd erDetail.ItemCo de = (Select CAST(i.ItemCode as
nvarchar(20)) from inserted as i)
and
TranPurchaseOrd erDetail.Purcha seDetailId =
(Select PurchaseDetailI d from TranPurchaseOrd erDetail where
Purchase_OrderN o = (Select CAST(i.Purchase _OrderNo as nvarchar(20))
from inserted as i))

END

[Erland Sommarskog]

santoshborfalka r (sa************ ***@gmail.com) writes:

This is the Trigger which is not working properly during Update, no any
record is going to be updated so pls help.

I am updating the requisition table when any update in quantity in
podetails table

It's very difficult to tell what might be wrong without any knowledge of
your tables or the business rules.

But I noted a few things that appears ood.

CREATE TRIGGER trig_updateRequ istion ON ERP.DBO.TranPur chaseOrderDetai l
INSTEAD OF UPDATE
AS
IF UPDATE(Quantity )

So if the Quantity columns is not mentioned in the SET clause, then you
will not perform any update at all?

Update RequisitionSlip Detail
set RequisitionSlip Detail.PoQuanti ty =

(Select PoQuantity from RequisitionSlip Detail where
ItemCode=(Selec t CAST(i.ItemCode as nvarchar(20)) from inserted as i)

This may work, if only one row at a time is updated, but it will fail
with an error if many rows are updated. Recall that triggers fire once
per statement, not once per row.

Update TranPurchaseOrd erDetail set
TranPurchaseOrd erDetail.Quanti ty =
(Select CAST(i.Quantity as int) from inserted as i)
where TranPurchaseOrd erDetail.Purcha se_OrderNo = (Select
CAST(i.Purchase _OrderNo as nvarchar(20)) from inserted as i)
and
TranPurchaseOrd erDetail.ItemCo de = (Select CAST(i.ItemCode
as
nvarchar(20)) from inserted as i)
and
TranPurchaseOrd erDetail.Purcha seDetailId =
(Select PurchaseDetailI d from TranPurchaseOrd erDetail where
Purchase_OrderN o = (Select CAST(i.Purchase _OrderNo as nvarchar(20))
from inserted as i))

I don't understand this casting business. Why cast the columns of "inserted"
when they are the same as in the target table? A simplified version of
the above could be:

Update TranPurchaseOrd erDetail
set Quantity = i.Quantity
FROM TranPurchaseOrd erDetail T
JOIN inserted i ON T.Purchase_Orde rNo = i.Purchase_Orde rNo
AND T.ItemCode = i.ItemCode
and T.PurchaseDetai lId =

(Select PurchaseDetailI d from TranPurchaseOrd erDetail where
Purchase_OrderN o = (Select CAST(i.Purchase _OrderNo as nvarchar(20))
from inserted as i))

I did not rewrite the last bit, because, frankly, I don't understand what
it's supposed to mean. It just looks strange.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx