Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ASA Audit Trigger -- Get Column Name

Posted on 2007-09-28
6
Medium Priority
?
2,163 Views
Last Modified: 2008-01-09
I've been tasked to create an audit trigger on our Sybase DB. I want to put the data in a table like

user_id,
Field_Changed,
New_Value,
Original_Value,
Changed_By,
Index_Num <--- identity field Autofill
Date_Changed <----Current TimeStamp Autofill

This is what I have so far:
-----------------------------------------------------
CREATE TRIGGER "User_Change" BEFORE UPDATE OF "Amt_1", "Amt_2", "Amt_3", "Approve_1", "Approv_2", "Mgr_1", .....)
      ORDER 1 ON "DBA"."USER_INFO"
      REFERENCING OLD AS Orig NEW AS NewVal */
      FOR EACH ROW
      /* WHEN ( search-condition ) */
BEGIN
      Insert into Narc_USER_INFO_Change (user_id, Field_Changed, New_Value, Original_Value,
                                                     Changed_By)
    Values(Orig.user_id,
;
END
-----------------------------------------------------

Does anyone have a good way to track the values, fields changed, and the user_ID of the person doing the update without having to do 16 individual triggers for each field I need to track?

I've done similar triggers in T-SQL and PL/SQL, but in Sybase I'm a newbie.
0
Comment
Question by:Jim P.
  • 3
  • 3
6 Comments
 
LVL 19

Expert Comment

by:grant300
ID: 19981731
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
0
 
LVL 38

Author Comment

by:Jim P.
ID: 19981960
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
0
 
LVL 19

Expert Comment

by:grant300
ID: 19982594
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 38

Author Comment

by:Jim P.
ID: 19990621
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.
0
 
LVL 19

Accepted Solution

by:
grant300 earned 1000 total points
ID: 19991515
O.K.  You should be able to create that report from the Audit table, however, you should define the Before and After columns as VARCHAR and convert numeric datatypes so that you don't need to deal with different datatypes in different columns.

The INSERT / SELECT / UNION ALL / SELECT ... syntax should work fine except you will have to add the appropriate CONVERT(varchar,...) functions.

BTW, I understand that you don't want to store a bunch of unchanged data.  What you have to weigh is the performance versus storage issues.  The time to insert and commit a row is relatively constant, or at least very insensitive to row size until you get very large.  If you have plenty of disk, you don't have to worry about the extra storage.  If you have a decent performance margin, you can insert multiple rows.  Your system might be lightly loaded enough you don't have to be too concerned about performance.  Only you can make this judgment call.

Bottom line is that as long as the application always modifies one row at a time and your OLTP load is not too high, your scheme will work just fine.

If you have batch applications modifying the rows in bulk, the combination of the implied cursor on the NEW and OLD tables, the multiple inserts per modified row, and the commit for each modified row may present too much of a performance hit to ignore.

Let us know how it works for you.

Regards,
Bill
0
 
LVL 38

Author Comment

by:Jim P.
ID: 20225516
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;
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this article, we’ll look at how to deploy ProxySQL.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question