CODEWITHSUNDEEP
c#sql-server-2008stored-procedures
user2614235
user2614235

Reputation: 181

Stored Procedure not tracking column modifications

I am trying to create a log of the database using Stored Procedure.But the problem here is that it is not tracking multiple column updates or modifications.

Following is my stored procedure

ALTER Procedure [dbo].[Proc_TrackFollowup]
     @Followup_ID int,@FollowType nvarchar(50),@ContactPerson nvarchar(50),
     @FollowDate datetime,@FollowTime nvarchar(50),@Status nvarchar(50),@Comment nvarchar(MAX)

     AS
     DECLARE @OldFollowup_ID int
     DECLARE @OldFollowType nvarchar(50)
     DECLARE @OldContactPerson nvarchar(50)
     DECLARE @OldFollowDate datetime
     DECLARE @OldFollowTime nvarchar(50)
     DECLARE @OldStatus nvarchar(50)
     DECLARE @OldComment nvarchar(MAX)
     DECLARE @OldCompanyName nvarchar(50)
     DECLARE @audit_action varchar(100)

     SELECT @OldFollowup_ID=Followup_ID,@OldCompanyName=CompanyName,@OldFollowType=FollowType,@OldContactPerson=ContactPerson,@FollowDate=FollowDate,@OldFollowTime=FollowTime,@OldStatus=Status,@OldComment=Comment
     from FollowUpTable where Followup_ID=@Followup_ID

     UPDATE FollowUpTable SET FollowType=@FollowType,ContactPerson=@ContactPerson,FollowDate=@FollowDate,FollowTime=@FollowTime,Status=@Status,Comment=@Comment where Followup_ID=@Followup_ID

     IF @OldFollowType<>@FollowType
     SET @audit_action='Follow Type Changed From ' + Cast(@OldFollowType AS VarChar) + ' to ' + Cast(@FollowType As Varchar)  

     IF @OldContactPerson<>@ContactPerson
     SET @audit_action='Contact Person Changed From ' + Cast(@OldContactPerson AS VarChar) + ' to ' + Cast(@ContactPerson As Varchar)

     IF @OldFollowDate<>@FollowDate
     SET @audit_action='Follow Date Changed From ' + Cast(@OldFollowDate AS VarChar) + ' to ' + Cast(@FollowDate As Varchar)

     IF @OldFollowTime<>@FollowTime
     SET @audit_action='Follow Time Changed From ' + Cast(@OldFollowTime AS VarChar) + ' to ' + Cast(@FollowTime As Varchar)

     IF @OldStatus<>@Status
     SET @audit_action='Status Changed From ' + Cast(@OldStatus AS VarChar) + ' to ' + Cast(@Status As Varchar)

     IF @OldComment<>@Comment
     SET @audit_action='Comment Changed From ' + Cast(@OldComment AS VarChar) + ' to ' + Cast(@Comment As Varchar)

     INSERT INTO LoggerFollowUpTable
     (Followup_ID,CompanyName,FollowType,ContactPerson,FollowDate,FollowTime,Status,Comment,Audit_Action,Audit_Timestamp) 
     VALUES(@Followup_ID,@OldCompanyName,@FollowType,@ContactPerson,@FollowDate,@FollowTime,@Status,@Comment,@Audit_Action,getdate());

I have given update option in c# form where Type is a dropdown which I am passing to @Type.But whenever I select another item from the dropdown and pass it to the stored procedure,it does not track the modification.Even after editing multiple fields in form,I face the same issue

Please suggest changes to the Stored Procedure

Thanks

Upvotes: 0

Views: 43

Answers (2)

SanyTiger
SanyTiger

Reputation: 666

You missed Begin and End Keywords, so try this, Open a new SQL query window by Right-Click-ing the DB and Selecting New Query, then use the code:

         ALTER Procedure Proc_TrackFollowup
         @Followup_ID int,
         @FollowType nvarchar(50),
         @ContactPerson nvarchar(50),
         @FollowDate datetime,
         @FollowTime nvarchar(50),
         @Status nvarchar(50),
         @Comment nvarchar(MAX),
         @OldFollowup_ID int,
         @OldFollowType nvarchar(50),
         @OldContactPerson nvarchar(50),
         @OldFollowDate datetime,
         @OldFollowTime nvarchar(50),
         @OldStatus nvarchar(50),
         @OldComment nvarchar(MAX),
         @OldCompanyName nvarchar(50),
         @audit_action varchar(100),

         AS
         Begin

         SELECT @OldFollowup_ID=Followup_ID,@OldCompanyName=CompanyName,@OldFollowType=FollowType,@OldContactPerson=ContactPerson,@FollowDate=FollowDate,@OldFollowTime=FollowTime,@OldStatus=Status,@OldComment=Comment
         from FollowUpTable where Followup_ID=@Followup_ID

         UPDATE FollowUpTable SET FollowType=@FollowType,ContactPerson=@ContactPerson,FollowDate=@FollowDate,FollowTime=@FollowTime,Status=@Status,Comment=@Comment where Followup_ID=@Followup_ID

         IF @OldFollowType<>@FollowType
         SET @audit_action='Follow Type Changed From ' + Cast(@OldFollowType AS VarChar) + ' to ' + Cast(@FollowType As Varchar)  

         IF @OldContactPerson<>@ContactPerson
         SET @audit_action='Contact Person Changed From ' + Cast(@OldContactPerson AS VarChar) + ' to ' + Cast(@ContactPerson As Varchar)

         IF @OldFollowDate<>@FollowDate
         SET @audit_action='Follow Date Changed From ' + Cast(@OldFollowDate AS VarChar) + ' to ' + Cast(@FollowDate As Varchar)

         IF @OldFollowTime<>@FollowTime
         SET @audit_action='Follow Time Changed From ' + Cast(@OldFollowTime AS VarChar) + ' to ' + Cast(@FollowTime As Varchar)

         IF @OldStatus<>@Status
         SET @audit_action='Status Changed From ' + Cast(@OldStatus AS VarChar) + ' to ' + Cast(@Status As Varchar)

         IF @OldComment<>@Comment
         SET @audit_action='Comment Changed From ' + Cast(@OldComment AS VarChar) + ' to ' + Cast(@Comment As Varchar)

         INSERT INTO LoggerFollowUpTable
         (Followup_ID,CompanyName,FollowType,ContactPerson,FollowDate,FollowTime,Status,Comment,Audit_Action,Audit_Timestamp) 
         VALUES(@Followup_ID,@OldCompanyName,@FollowType,@ContactPerson,@FollowDate,@FollowTime,@Status,@Comment,@Audit_Action,getdate()); 

         End

Upvotes: 0

Fred
Fred

Reputation: 5808

It would probably be easier if you approached it from a different angle. Have a table that tracks changes more like this.

CREATE TABLE [dbo].[LoggerFollowUpTable](
    [LogId] [int] IDENTITY(1,1) NOT NULL,
    [TableName] [varchar](50) NOT NULL,
    [FieldName] [varchar](50) NOT NULL,
    [RecordId] [int] NOT NULL,
    [OldValue] [varchar](50) NULL,
    [NewValue] [varchar](50) NULL,
    [DateChanged] [datetime] NOT NULL
) ON [PRIMARY]

GO

And then in your stored proc more like (repeat for each field)

IF @OldFollowType<>@FollowType
BEGIN
 INSERT INTO LoggerFollowUpTable (TableName, FieldName, RecordId, OldValue, DateChanged), NewValue) VALUES ('FollowUpTable', 'FollowType', @Followup_ID, Cast(@OldFollowType AS VarChar), Cast(@FollowType AS VarChar), GetDate())
END

You then end up with a row for each value changed.

If you want to stick with what you have you need to INSERT after each value test Something like.....

 IF (@OldFollowDate<>@FollowDate)
 BEGIN
    SELECT @audit_action='Follow Date Changed From ' + Cast(@OldFollowDate AS VarChar) + ' to ' + Cast(@FollowDate As Varchar)
     INSERT INTO LoggerFollowUpTable
 (Followup_ID,CompanyName,FollowType,ContactPerson,FollowDate,FollowTime,Status,Comment,Audit_Action,Audit_Timestamp) 
 VALUES(@Followup_ID,@OldCompanyName,@FollowType,@ContactPerson,@FollowDate,@FollowTime,@Status,@Comment,@Audit_Action,getdate());  
END

Upvotes: 1

Related Questions