Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
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 --because of having large no of Srno i may need to change GRN_Date of all grn_no =1
 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
-- and i.GRN_Date=d.GRN_Date and i.PO_No=d.PO_No and i.supl_id=d.supl_id 
-- i have tried this also 

 
       
 


 
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
Posted 28-Dec-12 1:09am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

If I understood the question correctly, the trigger doesn't know which updates should be combined. It sees both updates as separate operations, thus two rows in the audit trail (you have two separate UPDATEs').
 
Based on the information you provided, it sounds like grn_no is not unique. If that is the case then you may have duplicate because of possible cartesian product between inserted and deleted rows. To correct this, use all the key columns on the join between the tables.
  Permalink  
Comments
Espen Harlinn at 28-Dec-12 7:58am
   
Sounds reasonable :-D
Mika Wendelius at 28-Dec-12 9:25am
   
Thanks Espen :)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Plz Check with this ... I hope It will be work I just use DELETED table to fill instead of INSERTED.
 
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 d.GRN_No,d.GRN_Date,d.Sr_No,d.item_code,d.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
 
Plz check and let me know....
  Permalink  
Comments
Member-515487 at 28-Dec-12 7:26am
   
no its not working
 
i gives me 2 more record i have tried
IF ((SELECT TRIGGER_NESTLEVEL()) > 1) RETURN;
or SELECT TRIGGER_NESTLEVEL()) < 2
also

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 448
1 DamithSL 218
2 Dave Kreskowiak 200
3 arvind mepani 195
4 George Jonsson 195


Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 28 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid