Posted by Erland Sommarskog on 11/12/68 11:57

santoshborfalkar (santosh.borfalkar@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_updateRequistion ON ERP.DBO.TranPurchaseOrderDetail
> 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 RequisitionSlipDetail
> set RequisitionSlipDetail.PoQuantity =
>
> (Select PoQuantity from RequisitionSlipDetail where
> ItemCode=(Select 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 TranPurchaseOrderDetail set
> TranPurchaseOrderDetail.Quantity =
> (Select CAST(i.Quantity as int) from inserted as i)
> where TranPurchaseOrderDetail.Purchase_OrderNo = (Select
> CAST(i.Purchase_OrderNo as nvarchar(20)) from inserted as i)
> and
> TranPurchaseOrderDetail.ItemCode = (Select CAST(i.ItemCode
> as
> nvarchar(20)) from inserted as i)
> and
> TranPurchaseOrderDetail.PurchaseDetailId =
> (Select PurchaseDetailId from TranPurchaseOrderDetail where
> Purchase_OrderNo = (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 TranPurchaseOrderDetail
set Quantity = i.Quantity
FROM TranPurchaseOrderDetail T
JOIN inserted i ON T.Purchase_OrderNo = i.Purchase_OrderNo
AND T.ItemCode = i.ItemCode
and T.PurchaseDetailId =
> (Select PurchaseDetailId from TranPurchaseOrderDetail where
> Purchase_OrderNo = (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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Query performence about nText
• Prev in forum: Re: logs of creating and deleteing jobs
• Thread view: Re: Facing some problem in Instead of Trigger for MultiRow Insert

[Reply to this message]