Facing some problem in Instead of Trigger for MultiRow Insert

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_updateRequistion ON ERP.DBO.TranPurchaseOrderDetail

INSTEAD OF UPDATE

AS

IF UPDATE(Quantity)

BEGIN

Update RequisitionSlipDetail
set RequisitionSlipDetail.PoQuantity =

(Select PoQuantity from RequisitionSlipDetail where
ItemCode=(Select CAST(i.ItemCode as nvarchar(20)) from inserted as i)
and
RSlip_No=(Select CAST(i.RSlip_No as int) from inserted as i)
)
-

((Select Quantity from TranPurchaseOrderDetail where Purchase_OrderNo
=
(Select CAST(i.Purchase_OrderNo as nvarchar(20)) from inserted as i))

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

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

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))

ENDsantoshborfalkar (santosh.borfalkar@.gmail.com) writes:

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

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?

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

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 =

Quote:

Originally Posted by

(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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Extremely Slow Trigger Problem

I have an after update trigger that is killing me and I can't figure out why
.
When I run an update query that fires the trigger and look at the execution
plan, 85% of the cost is in the OPEN __0005 statement. I'm only updating 1
row, so this makes no sense to me. The I/O cost of opening the cursor is in
the 1100 range. Anyone got any ideas - I'm fresh out.
CREATE TRIGGER trgUpdate ON ITEMTASKS
FOR UPDATE
AS
DECLARE
@.ITE_ID INT,
@.TAS_ID INT,
@.STATUS TINYINT,
@.OLDSTATUS TINYINT
IF (@.@.ROWCOUNT = 0) RETURN
IF ( UPDATE( ITS_STATUS) ) BEGIN
SET NOCOUNT ON
DECLARE __0005 CURSOR LOCAL FAST_FORWARD FOR
SELECT I.ITE_ID, I.ITS_STATUS, I.TAS_ID, D.ITS_STATUS
FROM INSERTED I, DELETED D
WHERE I.ITE_ID=D.ITE_ID AND I.TAS_ID = D.TAS_ID
OPEN __0005
FETCH NEXT FROM __0005
INTO @.ITE_ID, @.STATUS, @.TAS_ID, @.OLDSTATUS
WHILE (@.@.FETCH_STATUS = 0 ) BEGIN
IF (@.STATUS = 6 ) BEGIN
UPDATE ITEMS
SET ITE_STATUS = 6
WHERE ITE_ID = @.ITE_ID
END
ELSE IF (@.STATUS = 2) AND ((SELECT TAS_ISLAST FROM TASKS WHERE TAS_ID =
@.TAS_ID)=1) BEGIN
UPDATE ITEMS
SET ITE_STATUS = 2
WHERE ITE_ID = @.ITE_ID
END
ELSE BEGIN
UPDATE ITEMS
SET ITE_STATUS = 1
WHERE ITE_ID = @.ITE_ID
END
EXEC ADD2SUMMARY_ @.ITE_ID, @.TAS_ID, @.STATUS, @.OLDSTATUS
FETCH NEXT FROM __0005
INTO @.ITE_ID, @.STATUS, @.TAS_ID, @.OLDSTATUS
END
CLOSE __0005
DEALLOCATE __0005
SET NOCOUNT OFF
ENDI strongly recommend you don't use cursors at all in triggers. Why
can't you re-write this and the contents of Add2Summary as set-based
SQL?
If you need more help then please come back with DDL, sample data
INSERT statements and show your required end result.
David Portas
SQL Server MVP
--|||Supposing that the cursor is necesary, I have two suggestions:
First -
Declare de cursor static and read_only
Second - Use EXIST in the following statement
-- ELSE IF (@.STATUS = 2) AND ((SELECT TAS_ISLAST FROM TASKS WHERE TAS_ID =
@.TAS_ID)=1) BEGIN
ELSE IF (@.STATUS = 2) AND exists(SELECT * FROM TASKS WHERE TAS_ID =
@.TAS_ID and TAS_ISLAST = 1) BEGIN
I do not know anything about the sp being executed, but the three updates
statements can be implemented without the use of the cursor.
AMB
"Arghknork" wrote:

> I have an after update trigger that is killing me and I can't figure out w
hy.
> When I run an update query that fires the trigger and look at the executi
on
> plan, 85% of the cost is in the OPEN __0005 statement. I'm only updating
1
> row, so this makes no sense to me. The I/O cost of opening the cursor is
in
> the 1100 range. Anyone got any ideas - I'm fresh out.
> CREATE TRIGGER trgUpdate ON ITEMTASKS
> FOR UPDATE
> AS
> DECLARE
> @.ITE_ID INT,
> @.TAS_ID INT,
> @.STATUS TINYINT,
> @.OLDSTATUS TINYINT
>
> IF (@.@.ROWCOUNT = 0) RETURN
> IF ( UPDATE( ITS_STATUS) ) BEGIN
> SET NOCOUNT ON
> DECLARE __0005 CURSOR LOCAL FAST_FORWARD FOR
> SELECT I.ITE_ID, I.ITS_STATUS, I.TAS_ID, D.ITS_STATUS
> FROM INSERTED I, DELETED D
> WHERE I.ITE_ID=D.ITE_ID AND I.TAS_ID = D.TAS_ID
> OPEN __0005
> FETCH NEXT FROM __0005
> INTO @.ITE_ID, @.STATUS, @.TAS_ID, @.OLDSTATUS
> WHILE (@.@.FETCH_STATUS = 0 ) BEGIN
> IF (@.STATUS = 6 ) BEGIN
> UPDATE ITEMS
> SET ITE_STATUS = 6
> WHERE ITE_ID = @.ITE_ID
> END
> ELSE IF (@.STATUS = 2) AND ((SELECT TAS_ISLAST FROM TASKS WHERE TAS_ID =
> @.TAS_ID)=1) BEGIN
> UPDATE ITEMS
> SET ITE_STATUS = 2
> WHERE ITE_ID = @.ITE_ID
> END
> ELSE BEGIN
> UPDATE ITEMS
> SET ITE_STATUS = 1
> WHERE ITE_ID = @.ITE_ID
> END
> EXEC ADD2SUMMARY_ @.ITE_ID, @.TAS_ID, @.STATUS, @.OLDSTATUS
> FETCH NEXT FROM __0005
> INTO @.ITE_ID, @.STATUS, @.TAS_ID, @.OLDSTATUS
> END
> CLOSE __0005
> DEALLOCATE __0005
> SET NOCOUNT OFF
> END
>|||Agreed, but I inherited the code and it's in production this way. It has
been working okay up until about a w ago, and I think there is an
underlying problem here I'm not seeing. Even with a cursor, it shouldn't
cost so much to open that cursor with 1 row in the inserted/deleted tables.
"David Portas" wrote:

> I strongly recommend you don't use cursors at all in triggers. Why
> can't you re-write this and the contents of Add2Summary as set-based
> SQL?
> If you need more help then please come back with DDL, sample data
> INSERT statements and show your required end result.
> --
> David Portas
> SQL Server MVP
> --
>