Trigger Not Firing Sometimes

Question

question

Govind asked Jul 1, '10

Trigger Not Firing Sometimes

Thank You For Your Reply...

The following is my trigger... As i mentioned Sometimes this is not firing!

CREATE TRIGGER Update_Quantity_txPurRcptSno
ON txPurchaseReceipts_SerialNos
FOR INSERT, UPDATE, DELETE
AS
BEGIN

DECLARE @PurchaseReceiptSerialIDI AS BIGINT
DECLARE @PurchaseReceiptSerialIDD AS BIGINT
DECLARE @ItemID AS INT
DECLARE @SerialNo AS VARCHAR(50)
DECLARE @BalQuantity AS QUANTITY
DECLARE @InOut AS TINYINT
DECLARE @ModuleID AS INT
DECLARE @VoucherID AS INT
DECLARE @VoucherLineID AS INT
DECLARE @VoucherQuantity AS QUANTITY
DECLARE @SequenceNo AS INT
DECLARE @HSerialID AS INT

SELECT @PurchaseReceiptSerialIDI = PurchaseReceiptSerialID,                      
       @ItemID = ItemID,                      
       @SerialNo = SerialNo,                      
       @BalQuantity = Quantity,                      
       @InOut = 1,                      
       @ModuleID = 127,                      
       @VoucherID = VoucherID,                      
       @VoucherLineID = VoucherLineID,                      
       @VoucherQuantity = Quantity,                      
       @SequenceNo = 1,                      
       @HSerialID = ISNULL(HSerialID,0)                      
  FROM INSERTED                                
SELECT @PurchaseReceiptSerialIDD = PurchaseReceiptSerialID,                      
       @ItemID = ItemID,                      
       @SerialNo = SerialNo,                      
       @BalQuantity = Quantity,                      
       @InOut = 1,                      
       @ModuleID = 127,                      
       @VoucherID = VoucherID,                      
       @VoucherLineID = VoucherLineID,                      
       @VoucherQuantity = Quantity,                      
       @SequenceNo = 1,                      
       @HSerialID = HSerialID                      
  FROM DELETED

IF @PurchaseReceiptSerialIDI IS NOT NULL AND @PurchaseReceiptSerialIDD IS NOT NULL
BEGIN
UPDATE xsS
SET xsS.SerialNo = INSERTED.SerialNo,
xsS.VoucherQuantity = INSERTED.Quantity
FROM xsSerialHistory xsS
INNER JOIN INSERTED
ON INSERTED.HSerialID = xsS.HSerialID
END
IF @PurchaseReceiptSerialIDI IS NULL AND @PurchaseReceiptSerialIDD IS NOT NULL
BEGIN
DELETE xsS
FROM xsSerialHistory xsS
INNER JOIN DELETED
ON DELETED.HSerialID = xsS.HSerialID
END
IF @PurchaseReceiptSerialIDI IS NOT NULL AND @PurchaseReceiptSerialIDD IS NULL
BEGIN
INSERT INTO xsSerialHistory
(ItemID,
SerialNo,
BalQuantity,
InOut,
ModuleID,
VoucherID,
VoucherLineID,
VoucherQuantity,
SequenceNo)
VALUES (@ItemID,
@SerialNo,
@BalQuantity,
@InOut,
@ModuleID,
@VoucherID,
@VoucherLineID,
@VoucherQuantity,
@SequenceNo)
SET @HSerialID = IDENT_CURRENT('xsSerialHistory')
UPDATE txPurchaseReceipts_SerialNos
SET HSerialID = @HSerialID
WHERE PurchaseReceiptSerialID = @PurchaseReceiptSerialIDI
END
END

trigger

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-07-01T09:03:40Z

Squirrel 1 answered Jul 1, '10

you have highlighted the problem of the trigger yourself in question .

these 2 section

select @var = column            
from   inserted            
            
select @var = column            
from   deleted

you are assuming that the trigger will fired for each record and the inserted and deleted table only contain 1 row. This is not necessary true. The inserted and deleted table may contain more than one rows. It contains the rows affected by your insert or update query. If your update query affect 100 rows, these tables will contain 100 rows.

You will need to re-write the INSERT INTO xsSerialHistory query by selecting from the INSERTED or DELETED table

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question