ASA Audit Trigger -- Get Column Name

First, the easy stuff.  You can use the USER, CURRENT_USER, or LAST_USER special values to return the string representation of the user id.

Now for the harder stuff.  Yes, you can do this in an individual trigger however, it is not going to be pretty.  Your audit table design of one record per modified field makes this much harder than it has to be.  It demands what you do what is called a pivot operation.  Not only is the code more complex and difficult to maintain but you are also going to take a performance hit if/when the application updates more than one column in the base table.  If you are in a position to change the design, I would do it now and save yourself a bunch of trouble down the road.

BTW, the design I would use would have the audit table contain the Key field(s), the user id, and the old and new values for each of the columns of interest.  The trigger could be structured to insert a single record with nulls for all of the values except those that changed.  Also, in your audit table definition, it appears that the columns USER_ID and CHANGED_BY may be redundant.

--------------------------------------------------------------------------------------------------------------
The trick to creating your trigger is that you have to create (potentially) multiple rows of audit data for each row that comes in.  Additionally, if you have any hope of not bringing the server to it's knees when updates occur, you will need to do this in SQL, not procedurally with cursors.

There are two ways to go about this.  The first is to create an INSERT/SELECT where the SELECT is actually a UNION of a bunch of SELECTS; one for each field of interest.  Something like.....

INSERT INTO Narc_USER_INFO_Change
     (CHANGED_BY, FIELD_CHANGED, NEW_VALUE, OLD_VALUE)
SELECT LAST_USER, 'Amt_1', NEW.Amt_1, OLD.Amt_1 WHERE NEW.Amt_1 != OLD.Amt_1
UNION ALL
SELECT LAST_USER, 'Amt_1', NEW.Amt_2, OLD.Amt_2 WHERE NEW.Amt_2 != OLD.Amt_2
UNION ALL
SELECT LAST_USER, 'Amt_1', NEW.Amt_3, OLD.Amt_3 WHERE NEW.Amt_3 != OLD.Amt_3
UNION ALL
etc.......

This should run fairly quickly and is not completely awful to maintain; still not pretty though.  In fact, I'm thinking about the helper table technique is so complicated here that it is not worth going into.

As I said before, if you can redesign now, do so.  Otherwise, give this a shot and let us know how it works for you.

Regards,
Bill

I didn't make some things  clear in the Q. Sorry and thanks for pointing it out. This auditing is coming up because we our App/User area is saying our IS area is changing various approval amounts on loan officers. We are trying to track who is at fault.

The User_Id is the PK in the "DBA"."USER_INFO" table (essentially the logins tables for the app). The typical end-user is not supposed to be able to change that value. In fact it is a FK in a bunch of tables.  The Changed_By is the person doing the updating.

The update would, supposedly, be a single row at a time change, not mass changes. That shouldn't be too bad.

The reason that I'm looking at the Pivot is to me it makes it easier to build the audit reporting in the long run. If I go an old/new in a table, then I have to build a massively recursive query to dump the results.

I'll take a swing at your method Monday.

Thanks

Got you on the User_id / Changed_By deal.  It makes perfect sense.

Can you talk a little about what you want your report to look like?  I am not sure how having one row per record updated would require a "massively recursive query", (heck, I'm not even sure I understand what you mean 8-) but perhaps it would become clearer if we started at the end point and worked our way back.

BTW, I won't tell the App/User area that a DBA can fiddle with auditing and hide changes if you won't ;-)

You know, it occurs to me that what you might want to do instead of building this auditing system is to simply start keeping history on the table.  You can do that quite simply by creating a history table that has the same PK as the production table and each of the fields you want to keep track of.  Your UPDATE trigger then inserts a copy of the OLD version of the record into the history table along with the LAST_USER and an END_DT field.  If you want to be really clever, you can also have a BEGIN_DT field that is populated by looking at the prior record in history.  What you wind up with is a linked list of records that are much harder to fool with than simply deleting audit records would be.

Just a thought.

Regards,
Bill

The way I'm reading your thought is to have the audit table as something like:
----------------------------------------
User_ID, Changed_By, Date_Updated, "Amt_1_Orig", "Amt_2_Orig", "Amt_3_Orig", "Approve_1_Orig", "Approv_2_Orig", "Amt_1_New", "Amt_2_New", "Amt_3_New", "Approve_1_New", "Approv_2_New"......
----------------------------------------
So I would have to account for all nulls that haven't changed in a particular update per row.  

My reference to a recursive query is that I would have to make a sub-query for each column to flatten it out.

Below is an example that I had tracking loan change info from a SQL DB. I want to try to emulate that.
----------------------------------------------------------------
User_ID      Time            Loan_Number      Field_Name      Data_Before      Data_After
SmithJ      16:12:57.367      1009267            NNOTE            NULL            1009267
SmithJ      16:12:57.913      1009267            PNOTENO            NULL            1009267
JonesJ      16:12:57.913      P2477            PNOTENO            P2477            NULL
SmithJ      16:40:48.837      1009267            PSPECINS1                        15200302
JonesJ      09:02:10.903      NULL            NNOTE            P2456            NULL
DoeJ      09:02:10.920      1009268            NNOTE            NULL            1009268
DoeJ      09:02:11.433      1009268            PNOTENO            NULL            1009268
JonesJ      09:02:11.433      P2456            PNOTENO                              P2456      
DoeJ      09:05:42.060      1009268            PSPECINS1      NULL            15200302
JonesJ      09:54:45.773      NULL            NNOTE                  C4357            NULL
DoeJ      09:54:45.773      1009269            NNOTE                   NULL            1009269
----------------------------------------------------------------

My idea is to store just the fields that have changed. Then I can just look down the list and see what has happened to a particular user, and or guilty party. I'm not storing unchanged information.

I found, later, the below code had been added by the vendor on the table. I modded from that.
---------------------------------------------------------------------
ORDER 1 ON "DBA"."USER_INFORMATION"
REFERENCING OLD AS oldrow NEW AS newrow
FOR EACH ROW

/*
      PR 11058  2006-06-01 Trent
      Insert these updates into USER_TRACKING_TEMP,
      then call sp_UserTempTrack to insert into
      USER_MAINTENANCE_TRACKING for audit logging.
*/
      
BEGIN
      
      Declare @ItemDesc CHAR(100);
      Declare @ItemCD INTEGER;
      Declare @TrackingTypeCD SMALLINT;
      Declare @UserID CHAR(10);
      Declare @SysUserID CHAR(10);
      Declare @OldValueStr CHAR(50);
      Declare @NewValueStr CHAR(50);
      

      //==================================================

      IF UPDATE (user_loan_limit_amt)
      AND (ISNULL(oldrow.user_loan_limit_amt, '') <> ISNULL(newrow.user_loan_limit_amt, ''))
      THEN
            SET @ItemDesc = 'Maximum Loan Amount';
            // ItemCD = column order
            SET @ItemCD = 4;
            SET @OldValueStr = FN_DollarFormat(oldrow.user_loan_limit_amt);
            SET @NewValueStr = FN_DollarFormat(newrow.user_loan_limit_amt);
      
            // note: action_ind will be treated as "I" or an insert
            INSERT INTO USER_TRACKING_TEMP
            (user_id, table_name, item_cd, item_desc, action_ind,
            tracking_type_cd, old_value, new_value)
            VALUES
            (@UserID, 'USER_INFORMATION', @ItemCD, @ItemDesc, 'I',
            @TrackingTypeCD, @OldValueStr, @NewValueStr);
      END IF;

      //==================================================

      IF UPDATE (restrict_access_ind)
      AND (ISNULL(oldrow.restrict_access_ind, '') <> ISNULL(newrow.restrict_access_ind, ''))
      THEN
            SET @ItemDesc = 'Restrict Access to Loans Created by User';
            // ItemCD = column order
            SET @ItemCD = 7;
            
            IF ISNULL(oldrow.restrict_access_ind, 0) = 0
            AND (newrow.restrict_access_ind = -1) THEN
                  SET @OldValueStr = 'No';
                  SET @NewValueStr = 'Yes';
            END IF;

            IF (oldrow.restrict_access_ind = -1)
            AND ISNULL(newrow.restrict_access_ind, 0) = 0 THEN
                  SET @OldValueStr = 'Yes';
                  SET @NewValueStr = 'No';
            END IF;

            // note: action_ind will be treated as "I" or an insert
            INSERT INTO USER_TRACKING_TEMP
            (user_id, table_name, item_cd, item_desc, action_ind,
            tracking_type_cd, old_value, new_value)
            VALUES
            (@UserID, 'USER_INFORMATION', @ItemCD, @ItemDesc, 'I',
            @TrackingTypeCD, @OldValueStr, @NewValueStr);
      END IF;