IDUG Content Committee

This would mean that every time you make a change to your Fact table (LOGBOOK), and it references a row in the Dimension table (AIRFIELD), the timestamp value in the MOST_RECENTLY_USED column in AIRFIELD gets updated.

That’s a pretty simple auditing function and it doesn’t show what values changed, just that a change was made at a particular date and time. You can make this more sophisticated; include a user ID, or the values that were changed. But we’re now starting to talk about some serious performance impacts (and trigger performance is often an invisible overhead; it doesn’t, for instance, show up in explained access paths).

 FINAL Tables etc.

The issues with both of those previous solutions are that they are ‘extra’ processing (as in, use more CPU, I/O, Storage in the latter case) and that they will not necessarily be ‘believed’, either by auditors or maybe the people who requested the scripts to be run in the first place. They are, after all, effectively separate operations.

The advantage of the OLD, NEW and FINAL tables is that they are intrinsically part of the Insert, Update or Delete that you’re executing. Let me show you a couple of examples. 

Insert

I’ve got an Insert statement to run, and I want to show what was added. Not the whole thing (this is a simple table, but it might be one with hundreds of columns). So I’m going to take the Insert statement and precede it with the line in grey:

Now when I run it, I don’t just get the message “SQL command completed successfully”, I get this

I have a clear record of what was inserted, produced by the statement that did the Insert

Delete

Similarly the ‘vanilla’ Delete statement will just give the  “SQL command completed successfully” message but I want to show what was deleted. Where the INSERT referred to the NEW TABLE to show what was, well, new, the DELETE refers to the OLD TABLE to display values that have been removed. Again, it’s the line in grey:

• Red box: the select FROM FINAL TABLE clause that is largely similar to the NEW and OLD table clauses in the previous examples. The difference is the addition of two columns: OLD_BRAKES_ON and NEW_BRAKES_ON which do not exist in the source table
• Blue box: this is the definition for those two ‘new’ columns
• Green box: this is how those two new columns get populated

The result of that statement is shown below.

You now have visibility of the new values for BRAKES_ON and BRAKES_OFF, which come from the FINAL TABLE and the original values, which were set using the INCLUDE columns.

NB, of course, those columns defined in the INCLUDE are not persistent and have disappeared once the UPDATE has been executed.

Note:   
Something to notice about this type of SQL statement and the accounting trace.
The accounting trace will count this SQL statement as SELECT and so the SELECT count will be greater then zero... and UPDATE/INSERT count will be zero.  So when reviewing application accounting (in your perf db) for applications that change data by looking at quantity of INSERT/UPDATE executed... but it is zero in this case.  So you might miss it.  One must remember to look at "rows_changed" or "rows_inserted" or whatever it is called in your perf db... then you might find that this application is using SELECT to change data! 

Conclusion

This is a means of providing visibility of changes and auditability for IUD operations within the statement itself. You might have a bit of explaining to do to an auditor if they’re not from a technical background, but you can demonstrate that there is still just a single operation: a single atomic command, and the results are taken from that. No smoke and mirrors, no opportunity to fudge results.

You can find more information in the Knowledge Centre here Retrieving result sets from an SQL data change statement, or drop me a line if you want to discuss these options or need some more explanation.

E-mail:       Mark.Gillis@triton.co.uk 

Web:         http://www.triton.co.uk