Skip to main content

Simple Record-Level Auditing in SQL Server & ArcSDE

I've had simple auditing set up in my databases for years thanks to our old DBA. This is not robust transaction logging, it just answers the questions "Who created this?", "When was this created?", "Who last edited this?" and "When was this last edited?".

After witnessing a brand new table audit itself without triggers, I set about to understand the setup. This seemed curious to me, so I thought it might also be curious to you.

It turned out to be a two step system which rendered my insert triggers superfluous (at least I think so, tell me if I'm missing something and I need insert triggers after all).

Our solution uses a combination of two methods. For inserts we are going to tie our audit fields to User Defined Data Types which inherit Defaults we have specified. For updates we will build a trigger to log the changes. We will use the following fields in our tables to track inserts and updates:
AUD_NUser - Creator
AUD_NDate - Creation Date
AUD_EUser - Editor
AUD_EDate - Edit Date


STEP 1: Create Defaults
Defaults, according to the SQL Server Help, specify what values are used in a column if you do not specify a value for the column when inserting a row. Often we will manually specify a default (e.g. "0" for an integer field to hold counts)

In SQL Server, right-click on "Defaults" and select "New Default".

Default for logging Insert User
Name: "DEF_UserName"
Value: "system_user"


Default for logging Insert Date
Name: "DEF_Date"
Value: "GetDate()"


NOTE: If the user is logged into SQL Server using Windows Authentication, SYSTEM_USER returns the Windows 2000 or Windows NT 4.0 login (e.g. "MYDOMAIN\myusername"). If the user is logged in to SQL Server using SQL Server Authentication, SYSTEM_USER returns the SQL Server login (e.g. "sa").


STEP 2: Create User-Defined Data Types
User-defined data types allow one to build custom specifications beyond the generic integer, decimal, varchar options. For example, one could create a custom character field of length 5 to store zip codes.

In SQL Server, right-click on "User Defined Data Types" and select "New User Defined Data Type".


User-Defined Data Type for storing Usernames
Name: "AUD_UserName"
Data type: "char"
Length: "50" (note this may need to be longer depending on your domain and usernames)
Allow NULLS: Check
Rule: "(none)"
Default: "dbo.DEF_UserName"


User-Defined Data Type for storing Timestamps
Name: "AUD_Timestamp"
Data type: "smalldatetime"
Length: "4"
Allow NULLS: Check
Rule: "(none)"
Default: "dbo.DEF_Date"



STEP 3: Creating Audit Fields in the Database Table

Add the following audit fields to your database table. Set the Data Types to your new User Defined Types, note the Length, Default Value and Allow Nulls fields will be configured automatically.
Field       Data Type
AUD_NUser AUD_UserName
AUD_NDate AUD_Timestamp
AUD_EUser AUD_UserName
AUD_EDate AUD_Timestamp




Congratulations, inserts are now covered! And the best part is, for any future table you only have to repeat Step 3 (we'll look into automating that too in a future post).

On to updates....



STEP 4: Create a Trigger to log Updates

In SQL Server, right-click on your a Table with Audit Fields and select "All Tasks" and then "Manage Triggers". The Trigger Properties dialog will open with "<new>" selected as the name. Below this in the "Text:" textbox enter the following substituting [tablename] and [primarykey] to match your table.


Create Trigger [tablename]_UTrig ON dbo.[tablename] For Update As
SET NOCOUNT ON
UPDATE a SET a.AUD_EUser = System_User, a.AUD_EDate = getdate()
FROM [tablename] a JOIN Inserted b ON a.[primarykey] = b.[primarykey]


You now have basic auditing in your database that works no matter where the edits take place be in SQL Server, MS Access, a custom application or even ArcGIS.

Yes, ArcGIS! And not only when editing a linked SQL Server data table, but also with integrated security now part of ArcGIS, you can add these edit fields to an SDE Table using the same steps described above and log edits to an SDE feature layer.

Comments

steve said…
How did you get ArcMap to recognize the new fields?
Brent said…
Steve, after many headaches, I have abandoned using this method in ArcSDE. Sorry for misleading you!

In repayment, I've asked my coworker if he could document the template he built to add similar logging functionality to ArcSDE. I'll get that posted as soon as he gets back to me. Should be sometime Wednesday, sorry!
steve said…
Thanks, I got the update to work no problem but not the insert. It seems that the geodatabase does not recognize the defaults.
Brent said…
Steve,

If you got updates working you can accomplish the insert auditing with triggers as well!

Here's the code you'll need for the insert trigger:

CREATE Trigger [tablename]_ITrig ON dbo.[tablename] For Insert As
SET NOCOUNT ON
a.AUD_NUser = System_User, a.AUD_NDate = getdate(), a.AUD_EUser = System_User, a.AUD_EDate = getdate() FROM [tablename] a JOIN Inserted b ON a.[primarykey] = b.[primarykey]


Please let me know if you get this to work!

Popular posts from this blog

Simple HTTP Redirect with Querystring in IIS7

HTTP Redirect seems simple enough. Always was in IIS6 and in IIS7 there's even a button labeled HTTP Redirect that promises relative redirects.  It looks like it'll be as easy Apache finally.  That is until you try to redirect a querystring.  Then everything bombs.

Turns out it still is relatively easy, except you have to know that Microsoft changed $S$Q to $V$Q. Why? $Ss and $Gs I suspect.

And How.
In our example we'll redirect all pages under http://olddomain.com/content to http://mydomain.com/content.
Pick the virtual directory you want to redirect. e.g. http://olddomain.com/content Click HTTP Redirect under IIS in the IIS management console.In the HTTP Redirect Dialog:
Check Redirect requests to this destinationEnter your new path ending with $V$Q.  e.g. http://mydomain.com$V$QCounter-intuitively check Redirect all request to exact destination (instead of relative destination)Choose the appropriate Status Code (Permanent or Temporary)Apply Changes and Test

Maintaining Cross-Database Referential Integrity

It is often convenient to house your master tables in a separate database from application specific databases so that primary keys are copasetic and multiple databases have access to the same lookups.

Common examples would include employees, counties, regions and other lookup tables.

Once these tables are in a separate database however it is no longer possible to simply drag-and-drop a relationship between them to maintain referential integrity.

Here's a Solution
For this example we'll use an Observation table, tied to a master list of Sex, that's right, Sex (we could call it Gender to be all PC, but these are animals, not Pat in the Personnel Department.)

Step 1:
We create a new view called PIC_Sex in our Application Database by querying the table PIC_Sex in our Master Database:

CREATE VIEW dbo.PIC_Sex AS
SELECT SexID, Sex, Rank
FROM Master.dbo.PIC_Sex
ORDER BY Rank
Yes, that's right, Male and Female! You don't even want to go down that road.

Step 2:
We create a new user-defi…

Serving up KML in IIS 6

To serve up KML in IIS 6, you have to add a few MIME Types. The easiest way to do this is to apply new MIME Type settings globally by changing the properties on your server's "Web Sites" folder in IIS.


Google Earth reads KML and KMZ files. The MIME type for KML files is

* application/vnd.google-earth.kml+xml

The MIME type for KMZ files is

* application/vnd.google-earth.kmz

Source: Google KML Tutorial


To add a MIME type to a Web site or directory

1. In IIS Manager, right-click the Web site or Web site directory for which you want to add a MIME type, and click Properties.

2. Click the HTTP Headers tab.

3. Click MIME Types.

4. Click New.

5. In the Extension box, type the file name extension.

6. In the MIME type box, type a valid MIME type. If you define a MIME type that has already been defined at a higher level, you are prompted to select the level where the MIME type should reside.

To create a MIME type for an undefined MIME type, type an asterisk (*) in the Extension box, an…