Quick And Easy Audit Tables

I find myself often needing to have some kind of audit trail for a table in SQL Server, i.e. some way to look at before-and-after data, who changed it, when, etc. I’ve set up enough of these manually that they were becoming formulaic, so I figured why not put together a procedure that creates them for me and writes the necessary trigger?

Attached is the version I’m currently using. The syntax is pretty simple. Here’s an example, a real one that I just set up:

EXEC sp_CreateDataAudit 'dbo', 'IV00200', 'audit', 'IV00200', NULL, 'tr_IV00200_Audit', 'SERLNMBR,UNITCOST,RCTSEQNM,VNDRNMBR,SERLNSLD,BIN', 1
Read more »

iOS 7 Breaks Windows Authentication in Safari

I’ve already reported this to Apple, but I’m putting it here for others’ benefit.

If you try to access a site that uses Windows authentication (NTLM), Safari will prompt you for your username/password a couple of times, then hang after loading one or two embedded resources (images, CSS files, etc).

Here are a couple of links you can use to test this. Log in with the username “Test” and the password “Test”. In most browsers, you’ll see a couple lines of text and ten icons. In Safari on iOS 7, the first link doesn’t quite stick the landing.

NTLM Test Page
Basic Auth Test Page

Possible workarounds include switching to basic authentication, but that’s really only advisable if your site is running SSL.

UPDATE:

The recent iOS 7.0.2 update has not addressed this issue. However, some folks over on the Apple discussion forums have discovered (in this thread) that the problem does not arise if you put Safari in private browsing mode. Depending on how heavily the site you need to access relies on retaining cookies between sessions, this might be an acceptable temporary workaround.

Quick Tip – Hybrid SELECT/INSERT/UPDATE/DELETE Scripts

If you're a DBA, especially one that has to deal with an ERP system that has no concept of silly things like "transactions", then you've probably got a collection of scripts to update and fix data as necessary.

And you probably (hopefully) run a SELECT before actually changing data, to visually confirm what will happen. You might do this by either having both a SELECT and UPDATE query in the script, or by commenting out the SELECT or UPDATE portions of the script as needed, or some other technique.

Here's an approach I've used for a few years now, which I've never seen mentioned elsewhere. This fictitious query would change the sales rep to 'Unassigned' for any customers with no orders in the past year.

SELECT
    cust.CustomerNumber,
    cust.CustomerName,
    cust.SalesRep
/*
UPDATE cust
SET SalesRep = 'Unassigned'
--*/
FROM Customers cust
    LEFT OUTER JOIN Orders ord
        ON cust.CustomerNumber = ord.CustomerNumber
        AND ord.OrderDate >= DATEADD(year, -1, GETDATE())
WHERE ord.OrderNo IS NULL

So what's going on? If you run the whole script, you get a SELECT query, since the UPDATE is commented out. If you highlight the line with UPDATE through the end of the query in order to execute only those lines (a nice feature of Management Studio), the script runs as an UPDATE statement. The magic happens in --*/. If you run the whole script, the -- is ignored as being inside the multi-line comment. If you run everything from UPDATE onward, the -- comments out the */, so there's no syntax error.

This also works with INSERT and DELETE statements; for an INSERT, you'll put the comment block before the SELECT list rather than after.

And if you want to be even more careful, replace UPDATE with BEGIN TRANSACTION; UPDATE.

Best of all, you don't have to maintain two identical queries, you don't have to worry about commenting/uncommenting the SELECT or UPDATE depending on what you need, and you don't have to worry about changing data if you accidentally run the whole script. Neat, huh?

Writing Well-Behaved Triggers

Triggers are an indispensible feature of SQL Server. Essentially stored procedures that are invoked automatically, a trigger can be used to perform logging, data validation and cleaning, synchronization, and notification, and implement referential integrity constraints that are too complicated for foreign keys, among, no doubt, plenty of other things.

But alas, any seasoned SQL Server DBA is probably familiar with the minefield of performance-related and other issues that can very easily stem from misuse or overuse of triggers. I've frequently been victim to (and perpetrator of) such issues myself.

So what does it take to write a good, well-behaved trigger? A surprising number of non-obvious things, that I'm going to address one at a time, by taking an example trigger from garbage to civilized. In a nutshell, here are the major concerns:

  1. Cut the chatter. Use NOCOUNT.
  2. Make sure your trigger can handle multiple rows.
  3. Prevent unbounded trigger cascading and recursion.
  4. Avoid horrible performance of the INSERTED and DELETED virtual tables.

Read more »