De-mystifying SQL Update Triggers

I've been looking at the murky world of SQL Server 2000 Update triggers recently, and none of it's as straightforward as I'd imagined. There seems to be unnecessary confusion around the columns_updated() function, which is all explained brilliantly in this article. I realised where I was going wrong with my user update trigger - because the user table had so many columns, the standard "catch-all" function to see if anything had changed, only referenced the first eight columns of the table. This all runs at single bit level, so 8 pieces of data is all that will be calculated in a single reference to the columns_updated() function. Therefore this is fine for a database table with up to eight columns:

IF (substring(columns_updated(), 1, 1)) > 0
-- Do stuff...

However I needed four of these statements to include all the user columns:

IF (substring(columns_updated(), 1, 1)) > 0 OR (substring(columns_updated(), 2, 1)) > 0 OR (substring(columns_updated(), 3, 1)) > 0 OR (substring(columns_updated(), 4, 1)) > 0
-- Something has changed, therefore update the record...

Rather laborious, but preferable to checking each column's status separately. Do read the article, it's quite excellent, hats off to Andy Warren.

Auto-suggest without ColdFusion 8

I've been Googling on auto-suggest frameworks in ColdFusion, and EVERYONE assumes that you're using CF8. If you're maintaining an old site, and don't have the luxury of CF8, I can recommend this list of auto-suggest frameworks.

I had a good look at "mgBox", which is neat, but the data source aspect is a bit on the annoying side, being rather too verbose to seem elegant. I much prefer "Wick", which is easy to customise, and just uses a plain Javascript file as its data source. It only took a couple of hours to get it working with the .js file being generated from a SQL query via ColdFusion. All rather impressive! If you've seen any other frameworks that you like, then let me know.

Michael Vaughan, My Lord... Michael Vaughan

It it with great sadness that Michael Vaughan today resigned as the England cricket captain. He's had a bad run of form this year, and losing the current series 2 - 0 to South Africa has made up his mind - it's time to go. Personally I think he should have waited until next week's final test match has finished, but he is clearly in a state of mental disarray, and it would be wrong of him to continue playing if he felt unable to perform or to lead the team.

Vaughan carried on the good work where Nasser Hussain left off in 2003 - adding some genuine fighting spirit and pride to an historically erratic and ineffective England team. There have been ups and downs, but his finest moment was recapturing the Ashes in the summer of 2005. That will live on as one of the finest Test series ever played, a truly inspiring performance from a very happy and committed team. Wonderful stuff. For that, I thank Michael Vaughan from the bottom of my heart.

On to the subject of his replacement - who should lead the team? Kevin Pietersen is being tipped as the obvious choice, but personally I wouldn't choose him, purely because he has no captaincy experience at any level. Being a world-conquering batsman is one thing, but grasping the nuances of the game and knowing how to handle individual players is a rare gift, and there's no evidence so far that he possesses it. My greatest fear is that Pietersen will gain the captaincy, and that it will slowly destroy his confidence, and with it his batting ability.

For my money, Chris Read is the man to take over from Vaughan. He is a truly gritty and determined cricketer, and arguably the finest wicket-keeper in the world. Currently in charge of Nottinghamshire, who he had lead to the top of Division 1 in the county championship, he would solve the keeping option, AND the captaincy option, while adding his services as a steady and often attacking batsman.

All will be revealed tomorrow.... watch this space.

Colbourne's First Law of Pavement Dynamics

Right, it's about time I revealed a theory that I've been postulating for the last few months. It may be peculiar to Mid Sussex - I'm not sure, as the pavements in Brighton are so over-crowded. I need to road-test it in other parts of the country.

The theory is this:
"Given a long, empty pavement, which only you and one other person are using, you can bet your bottom dollar that you and said person are competing for exactly the same piece of pavement, to the point where you're almost walking in each others' shoes."

I see this in action almost every day, and considering that there could be 300 metres of empty pavement in either direction, just what are the chances of it happening? Maybe it's time to start buying Lottery tickets.

Vista memory management - SuperBad, or SuperFetch?

My mate Simon constantly goes on and on about how much of a "resource hog" Windows Vista is. In his day-to-day working life he's building and maintaining desktop PCs for hundreds of imaptient users, and as a result "You can never have enough RAM" has become his constant mantra. Up until now I've always agreed with him. That is, until this morning, when I read an excellent article explaining exactly what Vista does with your PC's RAM. I was startled to find that actually, Microsoft have thought long and hard about memory management, and that Vista actually does things rather well - and I quote: "Vista treats system memory like a cache much more aggressively and effectively than any other version of Windows".

There's a new process called "SuperFetch" (a background task), which constantly fetches data that it thinks you're likely to need (based on your previous program selections and menu commands), and buffers it to RAM. The goal is to treat RAM like a system cache, which means that almost all of it should be in use - rather than older versions of Windows, which would regularly leave large swathes of it untouched, just waiting for a user command to trundle off and hit the hard disk for a large chunk of data.

This is really clever stuff - I take my hat off to Microsoft. The only downside is that SuperFetch is always running, so occasionally when you want to focus all the system resources on just one intensive task, you'll get a little irritated that the disk is busy, filling the cache with things that you know you won't need in the immediate future. Best option? Get a fast hard disk, like raided SATA. Maybe my next system will be a Vista installation... whatever next!

Rising costs re-visited

In a previous post, I mentioned that petrol cost £1.10 a litre, and that we could expect it to rise to £1.50 by September. Last night I filled up at the Esso station in Haywards Heath, and it cost me £1.20 a litre, so we've got some way to go, but the general upward trend is pretty clear. In the same period, I've seen a litre of Soya Milk rise from £1.00 to £1.39 in Sainsbury's, and the car parking charge at Haywards Heath station rise from £4.50 per day to £6.00. I'm due to be paying more for my road tax this year, up from the current £170 to £205, which at 20-odd % isn't that bad, considering that my Soya Milk has gone up 39% in the last 3 months. I know that inflation in the UK is at an 11-year high, but I do feel that they've forgotten to carry the 1... 38% would be more like it, 3.8% is just a flight of fancy.

High-definition photography

Chatting to a friend yesterday evening, he casually mentioned that he's getting heavily into "HD (high definition) photography". What the bleep's that, I thought... so he showed me a series of seven photos taken at the village Church. Each photo had been over-exposed on a different setting, to bring out one colour or another - so in one photo all the reds would be violently red, all the blues would be massively "in your face", etc. The idea is that you take a series of photos of exactly the same thing (using a tripod, otherwise you wouldn't be guaranteed to get exactly the same photo each time), all differently exposed, then you use a piece of software to merge them all together into one. The end result - a jaw-droppingly beautiful photo. I was amazed at the results he'd got, from an off-the-shelf digital SLR, and a £33 piece of software called "Photomatix". Check out the user gallery on the Photomatix website - some of the best photos I've ever seen, and they tell you how they took them. I'm itching to give this a try, just need to save a few pennies and get a Nikon D80, or similar.