Audit Trigger on Row with Text Column

keithmdw · June 27th, 2004, 04:48 PM

I am very new to SQL, so forgive me if this is a dumb question.

I am trying to write a simple trigger to copy the entire row from one table to an audit table on update and delete. Someone on usenet gave me a great sample which worked, but some of my tables have text columns in which also need auditing.

It has been suggested that I use an INSTEAD OF trigger to do this auditing, but I don't know how to use this.

I also have a couple of additional columns in my audit tables (to insert into what the action was and who did it - some of which is going to be supplied from parameters if this is possible). For instance, I was going to have my app (ASP) prompt the user for a reason for modifiying a row when they tried to make teh change, then insert this reason into the audit table along with the original record.

Can someone please post some simple sample of how I could do this?

I was going to do the auditing from my ASP app I am building but thought it would be much better to do it as a trigger on SQL (much less traffic etc.).

happygv · June 27th, 2004, 11:57 PM

Hi there,

Triggers cannot take parameters, as you say you want to pass the reason for update/delete along with.

So maybe you can create a procedure that does update/delete and then insert the reason, timestamp, etc... into the audit table. Call the procedure when you trigger the update event from the ASP. You can pass all the data(to be updated/deleted and reason... etc) as parameters to that procedure and let the procedure do that for you. this way again the traffic can be much less than you doing that as 2 separate transactions(Run update/delete statement, then insert the data into audit table).

This is what I can think for now.
Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection

keithmdw · June 28th, 2004, 03:00 AM

Thank you for your reply.

That sounds like a good solution.

Would it be possible for you to post some sample code of how to do this as I am very new to SQL and I am struggling to do this without a sample to look at at least.

Thanks

happygv · June 28th, 2004, 03:27 AM

Code:

Create procedure ProcedureName
(
@IdField varchar(255)=NULL, -- Used for Update/Delete
@Col1 varchar(255)=NULL, -- For update alone
@Col2 varchar(10)=NULL,  -- For Update alone
@Reason varchar(512)=NULL, -- Used for Inserting into Audit table.
@Operation Bit=0  -- used to decide Update/Delete, 0=update, 1=delete, default is set to UPDATE
)
as
BEGIN TRAN
    If @IdField is NULL
        BEGIN
            Rollback Tran
            SELECT 1 as Result -- any nonzero value is unsuccessful transaction.
            Return         --Cannot do anything further
        End

    If @Operation=0    -- Update code
        If @Col1 is NULL or @Col2 is NULL
            BEGIN
                Rollback Tran
                Select 2 as Result
                Return
            END
        ELSE -- Can run update here.
            BEGIN
                Update YourTable set Col1=@Col1, Col2=@col2 where IDFIELD=@IdField
                --Use your Insert code for audit table
                Insert AuditTable Values(@Reason, Getdate, @IdField, 'Update')...
                If @@ERROR<>0
                    BEGIN
                        Rollback Tran
                        Select 3 as Result -- ZERO means successful transaction here.
                        Return
                    END
            END

    If @Bit=1  -- Delete Code
        BEGIN
            Delete From YOURTABLE where IDFIELD=@IdField
                --Use your Insert code for audit table
                Insert AuditTable Values(@Reason, Getdate, @IdField, 'Delete')...
                If @@ERROR<>0
                    BEGIN
                        Rollback Tran
                        Select 4 as Result -- ZERO means successful transaction here.
                        Return
                    END
        END

    Commit Tran
    Select 0 as Result -- ZERO means successful transaction here.
    return

In your ASP page, you can call this as follows.

In your delete page it should look like...

[code]idFieldVal = Request.Form("IdField")

'define your connection object and recordset object
StrSql="Execute YourProcedure @Operation=1, @IdField=" IdFieldVal
Rsobject = connectionObj.Execute
Result=RsObject("Result")
If cint(Result) = 0 Then Response.write "Deleted"
If cint(Result) = 1 Then Response.write "Delete Failed, Missing IDField value."
If cint(Result) = 4 Then Response.write "Delete Failed, Please try again."

In your delete page it should look like...

[code]idFieldVal = Request.Form("IdField")
Col1Val = Request.Form("Col1Val")
Col2Val = Request.Form("Col2Val")

'define your connection object and recordset object
StrSql="Execute YourProcedure @Operation=0, @IdField=" & IdFieldVal & ", @Col1='" & Col1Val & "', @Col2='" & Col2Val & "'"
Rsobject = connectionObj.Execute
Result=RsObject("Result")
If cint(Result) = 0 Then Response.write "Updated"
If cint(Result) = 1 Then Response.write "Delete Failed, Missing IDField value."
If cint(Result) = 2 Then Response.write "Update Failed, Missing Col1 and Col2 value."
If cint(Result) = 3 Then Response.write "Update Failed, Please try again."

Maybe you got to do additional validations that are necessary.

Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection

happygv · June 28th, 2004, 04:07 AM

Hi There,

I had missed out something when I posted you the sample kind of code. So I have edited it again. Please revisit my previous post and follow that.

Cheers!

_________________________
-Vijay G

Strive for Perfection

keithmdw · June 28th, 2004, 04:17 AM

Thank you very much

I will have a go with that later today.

Thanks again