Audit Table - Case Statement

Question

What I am trying to achieve:

When someone updates the Car table with (RegNum, VinNumber, CarMakeID etc) I need to put this in the audit table.

In the example below, I have selected CarID = 1 and ChangeItem = 1 (which I want the code to use the first part of the case).

If I was to select ChangeItem2 I would want it to update the VinNumber in the Car Table and insert the deleted.VinNumber and Inserted.VinNumber into the audit table.

DECLARE @CarID INT
DECLARE @ChangedItem INT
DECLARE @Input VARCHAR(50)


SET @CarID  = 1
SET @ChangedItem = 1
SET @Input = 'LR44 GH345'

    UPDATE CAR
(   
CASE
        WHEN @ChangedItem = 1 THEN
        SET RegNum = @Input
        OUTPUT @CarID, @ChangedItem, deleted.RegNum, inserted.RegNum INTO CarAudit  (CarID, CarAuditObjectID, OldValue ,NewValue)
        WHERE CarID = @CarID

        WHEN @ChangedItem = 2 THEN
        SET VinNumber = @input
        OUTPUT @CarID, @ChangedItem, deleted.RegNum, inserted.RegNum INTO CarAudit  (CarID, CarAuditObjectID, OldValue ,NewValue)
        WHERE CarID = @CarID

END
)

If anyone has a better way of doing this altogether please share, I am not rigid in staying with this design.

Many thanks for any assistance.

Jay.

Answer 1

I would still recommend the use of triggers, it's a bit cleaner than what you would have to do in a stored proc. However, if you want to stick with using a stored proc you could use the following pattern...(explanation below the code)

Declare @ColumnName varchar(250)
Declare @Input varchar(250)
Declare @CarID int

Select @ColumnName = 'RegNum'
Select @Input = 'LR44 GH345'
select @CarID = 1

Begin Transaction
Declare @DynamicSQL varchar(max)

Select * Into #CarAuditTemp
From Car
Where CarID = @CarID

Select @DynamicSQL = '
Update Car
Set '+ @ColumnName +' = "'+ @Input +'"
Where CarID = '+ Convert(Varchar, @CarID) +' 
'


Select @DynamicSQL
--Execute (@DynamicSQL)

Select @DynamicSQL = '
Insert CarAudit
(CarID, ChangedItem, OldValue, NewValue)
Select  '+ Convert(Varchar, @CarID) +',
    "'+ @ColumnName +'",
    #CarAuditTemp.'+ @ColumnName +',
    Car.'+ @ColumnName +'
From Car
Inner Join #CarAudit
    On Car.CarID = #CarAuditTemp.CarID
'

Select @DynamicSQL
--Execute (@DynamicSQL)
Commit Transaction

@ColumnName, @Input, and @CarID would all be parameters to your stored procedure. This will use dynamic SQL so that you can generate the necessary SQL to update the table with your value and also insert into the audit table. I commented out the executes and left selects on the dynamic sql variable so you could see how it behaves. The #CarAuditTemp temp table is used to get the original state of the record before you update anything. It would be a good idea to also do some error checking, making sure the column passed into the stored proc exists on that table, making sure the CarID passed in exists in the table as well, before doing anything else. I added a transaction because you need to rollback everything if an error occurs during the update to Car or the insert into CarAudit. This is just a general idea of what you would need to do, let me know if you have any questions.

Answer 2

For anyone that stumbled across this.

I wanted to avoid the use of triggers so putting it in as a sproc with the below code:

DECLARE @CarID INT
DECLARE @ChangedItem INT
DECLARE @Input VARCHAR(50)


SET @CarID  = 1
SET @ChangedItem = 2
SET @Input = 'PG 542DF 07GD 11'


IF @ChangedItem = 1

    BEGIN

        UPDATE CAR
        SET RegNum = @Input
        OUTPUT @CarID, @ChangedItem, deleted.RegNum, inserted.RegNum INTO CarAudit  (CarID, CarAuditObjectID, OldValue ,NewValue)
        WHERE CarID = @CarID

    END

IF @ChangedItem = 2

    BEGIN
        UPDATE CAR
        SET VINNumber = @Input
        OUTPUT @CarID, @ChangedItem, deleted.RegNum, inserted.RegNum INTO CarAudit  (CarID, CarAuditObjectID, OldValue ,NewValue)
        WHERE CarID = @CarID
    END