Let say
i have created 1 store procedure
for GRN entry
grn no
grn no srno
1 1
1 2
and so on
i am pasting some code of GRN update store procedure
UPDATE [tblGRNEntry]
SET
[GRN_Date] =@GRN_Date,
PO_No=@PO_No,
[supl_id] = @supl_id ,
[item_code] = @item_code
,[receipt_qty] = @receipt_qty
,[passed_qty] = @passed_qty
,[UOM] = @UOM
,[batch_no] = @batch_no
,[expiry_date] = @expiry_date
,[modusername] = @addusername
,[moddate] = @adddate
WHERE [GRN_No] = @GRN_No
and [sr_no] = @sr_no
if @@ROWCOUNT >0
begin
UPDATE [TestelERP].[dbo].[tblGRNEntry]
SET
[GRN_Date] =@GRN_Date , supl_id=@supl_id
WHERE [GRN_No] = @GRN_No
now i am maintaining audit trail
for this
create TRIGGER trgGRNUpdate
ON dbo.tblGRNEntry AFTER UPDATE
AS
INSERT INTO dbo.tblAuditTrail(Doc_No,Doc_Date,Sr_No,item_code,batch_no, oldvalue, newvalue,type,modify_by,modify_date,menuname)
SELECT i.GRN_No,i.GRN_Date,i.Sr_No,i.item_code,i.batch_no
,'GRN_Date:'+CAST(d.GRN_Date AS VARCHAR(10)) +';PO_No: '+cast(d.PO_No as varchar(9))+';Supl_Id:'+d.supl_id+';Item_Code:'+cast(d.item_code as varchar(6))+';Receipt_Qty:'+cast(d.receipt_qty as varchar(20))+';Passed_Qty:'+cast(d.passed_qty as varchar(20))+';UOM:'+d.UOM+';Batch_No:'+d.batch_no+';Expiry_Date:'+cast(d.expiry_date as varchar(30))
,'GRN_Date:'+CAST(i.GRN_Date AS VARCHAR(10)) +';PO_No: '+cast(i.PO_No as varchar(9))+';Supl_Id:'+i.supl_id+';Item_Code:'+cast(i.item_code as varchar(6))+';Receipt_Qty:'+cast(i.receipt_qty as varchar(20))+';Passed_Qty:'+cast(i.passed_qty as varchar(20))+';UOM:'+i.UOM+';Batch_No:'+i.batch_no+';Expiry_Date:'+cast(i.expiry_date as varchar(30))
,'Updated',i.modusername,i.moddate,'GRN Data Entry'
FROM Inserted i
INNER JOIN Deleted d
ON i.GRN_No = d.GRN_No and i.sr_no=d.sr_no
when update runs out i should get 1 record but i am getting 3 record
including all diff Sr_no of same Grn_no
how to sort out this
2 update take place in one table but i want only 1st one carried out trigger run but not for next update