creating an audit

[acorn]

Hi.

i need to write code that will:
1. check my dataset to see if anything has changed.
2. if there are changes, write the old value, new value to a table in oracle.

I only need to know about rows that have changed. I know somewhere I can check something called rowstate but I don't know how do that.

So far I have the following code:

Private Sub WTBBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WTBBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.WTBBindingSource.EndEdit()
        'audit. 
        Dim dschanges As DataSet
        dschanges = Me.BQDS.GetChanges()

I can see that dschanges haschanges is true...
can you point me in the right direction?

thanks.

 

[jmcilhinney]

The RowState of each DataRow in each DataTable of your DataSet will be either Unchanged, Added, Modified or Deleted. Are you saying that you're only interested in the Modified ones, or all but the Unchanged ones?

 

[cjard]

You'd be better off doing this in oracle with an Update trigger:

CREATE or REPLACE TRIGGER trigger_name
BEFORE UPDATE
    ON my_million_data_rows
    FOR EACH ROW
DECLARE

BEGIN
    IF :new.COLUMN_A != :old.COLUMN_A THEN
      INSERT INTO audit VALUES('COLUMN_A', :old.COLUMN_A, :new.COLUMN_A);
    END IF;
    IF :new.COLUMN_B != :old.COLUMN_B THEN
      INSERT INTO audit VALUES('COLUMN_B', :old.COLUMN_B, :new.COLUMN_B);
    END IF;

EXCEPTION
    WHEN ...
    -- exception handling
END;

Warning. This will make your database updates supremely slow if you run a query like this:

UPDATE my_million_data_rows SET column_a = column_a || 'suffix';


Turn the trigger off before you do table-wide updates!

 

[acorn]

Audit

Hi.
I was thinking about the trigger in oracle previously, but changed my mind because I need to keep track of the user that's making the changes. The user info is stored in another table that I've created... I present a login form before the main input form appears.
Sorry, I should have mentioned that before.

 

[acorn]

The RowState of each DataRow in each DataTable of your DataSet will be either Unchanged, Added, Modified or Deleted. Are you saying that you're only interested in the Modified ones, or all but the Unchanged ones?

I'm only interested in the all the modified ones.

 

[jmcilhinney]

You can specify a RowState when calling GetChanges to get only certain types of changes, so you can specify Modified. Just make sure that you save your audit records and your updates in a transaction.

 

[acorn]

You can specify a RowState when calling GetChanges to get only certain types of changes, so you can specify Modified. Just make sure that you save your audit records and your updates in a transaction.

Hi again. So i've managed to figure out how extract the modified records and the newly added records too. But how do i test by field? I would like to loop through the modified dataset and for each record, id the old / new values.
Thanks. Here's the code so far.

Private Sub WTBBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WTBBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.WTBBindingSource.EndEdit()
     
        'audit. 

        If Not BQDS.HasChanges() Then
            Exit Sub
        End If

        If BQDS.HasChanges(DataRowState.Modified) Then
            ' Create temporary DataSet variable and
            ' GetChanges for modified rows only.
            Dim tempDataSet As DataSet = _
                BQDS.GetChanges(DataRowState.Modified)

            ' Check the DataSet for errors.
            If tempDataSet.HasErrors Then
                ' Insert code to resolve errors.
            End If
            Dim r As DataRow
            For Each r In BQDS.Tables("Baseline").Rows
                   'check by column - what's changed?
            Next

        End If

My audit table looks like this:

ChangeDate
UserID
ColumnName
OldValue
NewValue

 

[jmcilhinney]

First up, there's no point using HasChanges. Just call GetChanges and if it's Nothing then there's no changes.

As for the question, when you get a field value from a DataRow it will get the current value by default. You can also specify a particular version, so you'd specify the original version of each field and compare that to the current version.

 

[cjard]

Hi.
I was thinking about the trigger in oracle previously, but changed my mind because I need to keep track of the user that's making the changes. The user info is stored in another table that I've created... I present a login form before the main input form appears.
Sorry, I should have mentioned that before.

The v$session table contains the windows logon ID of the user that is connected, and oracle maintains a list of variables about the current session. We use this for audit tracking (windows logon name):

SELECT SYS_CONTEXT('USERENV','OS_USER') FROM DUAL;

We prohibit john.smith windows user logging in to our app as mary.jones - a user cannot log in to windows as one id and then into the app as another. Actually, you may be better off integrating your OS logins into the app, so that the windows login is used; it's more secure than anything you can write quickly, and can be managed by anyone with AD knowledge

You really will struggle to get this done effectively in the client side, because even just navigating rows can cause them to become Modified when they havent actually had any changes. This could cause your audit table to fill up with junk. It further doesnt catch use of apps other than your own, to make changes whereas a trigger would

 

[cjard]

ChangeDate
UserID
ColumnName
OldValue
NewValue

CREATE or REPLACE TRIGGER trigger_name
BEFORE UPDATE
    ON my_million_data_rows
    FOR EACH ROW
DECLARE

BEGIN
    IF :new.COLUMN_A != :old.COLUMN_A THEN
      INSERT INTO audit VALUES(SYSDATE, SYS_CONTEXT('USERENV','OS_USER'), 'COLUMN_A', :old.COLUMN_A, :new.COLUMN_A);
    END IF;
    IF :new.COLUMN_B != :old.COLUMN_B THEN
      INSERT INTO audit VALUES(SYSDATE, SYS_CONTEXT('USERENV','OS_USER'), 'COLUMN_B', :old.COLUMN_B, :new.COLUMN_B);
    END IF;

EXCEPTION
    WHEN ...
    -- exception handling
END;

 

[acorn]

The v$session table contains the windows logon ID of the user that is connected, and oracle maintains a list of variables about the current session. We use this for audit tracking (windows logon name):



We prohibit john.smith windows user logging in to our app as mary.jones - a user cannot log in to windows as one id and then into the app as another. Actually, you may be better off integrating your OS logins into the app, so that the windows login is used; it's more secure than anything you can write quickly, and can be managed by anyone with AD knowledge

You really will struggle to get this done effectively in the client side, because even just navigating rows can cause them to become Modified when they havent actually had any changes. This could cause your audit table to fill up with junk. It further doesnt catch use of apps other than your own, to make changes whereas a trigger would

hey... thanks so much for this useful tip. i didn't know that navigation of a dataset causes records to be modified...
when you say that you prohibit john.smith from logging in as mary.jones, i guess you're just doing a quick check against the Environment.UserName variable...
is that right?
thanks again for your help!

 

[cjard]

Yep, when the app starts they type their username and password for the app. The username has to be the windows user name, so it could in theory be omitted (and the app just gets it from windows), but we do like to log users attempting to log in as other users and also some users do have the ability to fake their identity

In creating a logging table we use the id found in v$session as a more accurate reflection of who really is doing the action, rather than who has faked their id. A table records logins and outs of id fakers for audit purposes.

SOme users do give away their username and password for windows but we find that reminding people that this allows anyone to see their emails, notes, calendar etc discourages them

 

[acorn]

log in strategy

kewl. that's a great idea.
thanks. i'll try to implement all this (without having to ask more questions!)
thanks again for the ideas / tips.

 

[acorn]

question about trigger & new records, or deleting old ones.

hi again.
i finally finished creating my trigger - it took a while because this table is massive. Over 200 fields.
In any case, it seems to be working on when i modify an existing record - the changes are captured and written out to the audit!
kewl!
but ... when i delete a record, or create a new one, nothing is being written out to the audit table.
Here's what the trigger looks like:

create or replace
TRIGGER AUDITCHANGES
BEFORE INSERT OR DELETE OR UPDATE 
  ON WTBse
  FOR EACH ROW
  DECLARE
  
BEGIN
  IF :new."InterviewDate" != :old."InterviewDate" THEN
    INSERT INTO WTBAudit (DTCHG,FLDNAME, userid, oldval, newval) 
    VALUES (SYSDATE, 'InterviewDate', SYS_CONTEXT('USERENV','OS_USER'), :old."InterviewDate",:new."InterviewDate");
  END IF;
  IF :new.BNP7 != :old.BNP7 THEN
    INSERT INTO WTBAudit (DTCHG,FLDNAME, userid, oldval, newval) 
    VALUES (SYSDATE, 'BNP7', SYS_CONTEXT('USERENV','OS_USER'), :old.BNP7,:new.BNP7);
  END IF;

Assuming i can get it to work for new records, what / how does it handle fields that are NULL values? Will it still record an entry in the audit and just write out NULL for the new value?
Thanks.

 

[cjard]

Heheh, well, here we come to the interesting point about NULL

NULL cannot be compared to anything else. When a record is being inserted, :OLD.COLUMN_NAME is NULL because there is no old value for a new record, so your compare of "IF new!=old THEN" is FALSE because:

'ABC' != NULL

is FALSE

This is an inviolable Oracle rule on the handling of nulls. Nulls cannot be compared to anything, even other nulls, and return TRUE. Comparison with NULL always results in a false

Thankfully your other triggers are simpler: Write just one more trigger for INSERT OR DELETE

Then use
IF INSERTING THEN
...
END IF

and
IF DELETING THEN
...
END IF;


To simply record that user X inserted or deleted the record. There is no need to record what values were inserted because in the absence of any UPDATE in your audit, the values inserted were those as present in the table now. Likewise, you can't delete values, only rows.. So just record that user X deleted the row


You may now wish to revisit the update trigger, knowing that if the user sets the value to NULL of any column, or if the value was null then it wont be audited

For all columns that allow nulls, here's your code:

IF ld.column_name IS NULL or :new.column_name IS NULL or ld.column_name != :new.column_name THEN



-

remember you can get SQL to write this code for you:

SELECT REPLACE('
  IF :old.{cn} IS NULL or :new.{cn} IS NULL or :old.{cn} != :new.{cn} THEN
    INSERT INTO audit(...blah...) VALUES (...blah..., :old.{cn}, :new.{cn});
  END IF;
'
, '{cn}', lower(column_name))
from
  user_tab_cols
where table_name = 'WHATEVER TABLE NAME' and nullable = 'Y'

Yes, those are line breaks in the string. Try it

See how we basically write some code inside a string and then get oracle to replace our placeholder {cn} with the actual column name for all the nullable columns in WHATEVER TABLE NAME table.

Try it; bet you wish I'd told ya before you embarked on a mission to code up for 200 columns? (oops)