SQL Server post Insert Trigger

I mostly want to document how to do this so I don't forget.

Here's the problem space: you've got a table (call it kjh) with some element (a). Whenever a new row is inserted into kjh, you want to insert a row into another table (kjh2), with some of the data (lets say column a) being duplicated.

This can be useful for a lot of things. You could use this to audit the values in a table, for example. In my case, I needed to copy off some of the data, so that the data could be modified, but the original data would be left alone for reporting purposes.

There 2 small issues. First, how do you get the data from the newly inserted row into the copy? Second, how do you trap and ignore the error that comes from trying to insert a duplicate row into the kjh2 table. This matters, in my case, because there are other things that can insert into kjh2, so there's a race condition. In the vast majority of cases, the trigger should insert cleanly. But it's possible that other processes could insert. Since there are no foreign key constraints here -- and since the other processes may legitimately have the right to do the insert to kjh2 before kjh -- I run into the possibility of this error.

One of the things that perplexed me is that this seems like it would be such a common problem. But multiple web searches basically lead no where. I finally found a kind soul on msdn to help.

On to some code. First the tables, just to show I have nothing up my sleeve.

CREATE TABLE [dbo].[kjh](

[a] [int] NULL,

[b] [int] NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[kjh2](

[c] [int] NULL

) ON [PRIMARY]

Now the trigger:

CREATE TRIGGER trinsert ON kjh

AFTER INSERT
AS

IF ((select a from inserted) between 2 and 19)

BEGIN TRY

INSERT INTO kjh2(a)
SELECT a
FROM Inserted ;

END TRY

BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; DECLARE @ErrorNumber INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorNumber = ERROR_NUMBER();

if (@ErrorNumber <> 2627)

-- raise the error

END CATCH

Just a couple notes.

"Inserted" is a temp table that SQL automatically creates which contains all the freshly inserted rows.

The error number mentioned is the error for a unique constraint violation.

and... *poof*... i've pulled a trigger out of a hat.

Missing Technical Phrases

Each season seems to bring a slew of new buzz words. Synergy, paradigm, core competencies and others have all graced our ears.

I'd like to officially propose some new, more down-to-earth ones. Please feel free to leave a comment with your own additions. Perhaps, we can create a list to present to those on high at Google and truly establish some meaningful phrases.

Inverse Redundancy:

Rather than putting one application on multiple servers in order to add stability and scalability, this is the practice of putting every application and service on the same server(s) in an apparent effort to increase clutter and decrease stability.

It has a related term...

Inverse Staff Redundancy

this is the condition of having the same person assigned to multiple roles on many projects. Typically, a project team or manager will present an org chart with many layers and lots of boxes. But looking closely, you'll see that most of the boxes below the management layer have the same resource. This is also known as "Office Space Syndrome", in a reference to the guy in the movie Office Space, who had 14 bosses.

Psychotic Optimism

This is the view that things are going well in spite of substantial evidence to the contrary. You've been on that project. I know I have. It's 4 days from delivery. You're 7 weeks behind schedule and have no resources actually working on it anyway. And your executive manager declares that the project is going well and will be delivered ahead of schedule.

Security By Annoyance

This is the practice of making security policies that serve no real purpose except to annoy the users, thereby creating the illusion of security. Technical examples include things like password restrictions that are so complex, MIT students can't understand them, sessions that conveniently expire right before work is committed, and locally-running software (hard drive encryption, virus checkers, etc.) that consume 60% of PC resources.

The primary value of this is three-fold. First and foremost, it safeguards the jobs of the security team, since their work is visible and seemingly important. Second, it drives off would-be hackers by making the common functionality too painful to bother hacking. And third, it decreases system load as common users simply give up and resort to pencil and paper.

It should be noted that this is not strictly an IT term, but can be seen in any airport security checkpoint.

Feel free to add your own.

bitlocker

I figured that since today is the official release date of Windows7 (I think?), I'd add an entry about one of its features.

Windows 7 now has drive encryption built in, in an application called bitlocker:

http://en.wikipedia.org/wiki/BitLocker_Drive_Encryption

I've been using it for a short time, and I have to say, it's pretty slick. It's just about what you'd want it to be. I have my sd card encrypted and it requires a password to view it the first time, then, once opened, allows free access until the computer restarts or the card is removed and re-inserted.

In general, I haven't really found anything with Windows 7 I don't like. I've got occasional annoyances with Microsoft because they seem to like to move things. You look for something in a menu or control panel section and it's not there. But you find it in a new section -- same functionality, same look and feel, just randomly moved.

I think one of the things about Vista was that Microsoft wanted to make it more userfriendly to people who weren't used to computers. At least, that's what they told me at the presentation I went to on it. I think there are 2 funny things about that. First, what is their target market? the 2 people in the mountains of Colorado who haven't looked at a computer in 12 years? I mean, computers are so key to our culture now that schoolkids grow up on them. Why target the computer illiterate when there are so few left. (Perhaps their real target is in China or other places where PCs aren't so common?)

The other thing about this is that, while a few things have become easier, most haven't. In fact, (to the point) Windows7 is more closely tied to Server 2008 than anything else. So, depending on what you do, you may end up having to mess with user roles and group policies. Personally, I think this is great. I love having it broken down like that. But for the computer nubie, they'll get lost.

Still, 7 is an awesome product. It's fast, clean, visually impressive, feature rich -- it's the best OS Microsoft has every marketed. The integrated virtual machine is good (not awesome, but good), the bitlocker feature is pretty cool, the integration with Vista apps is cool -- and it actually *runs* them without crashing! All in all, it rocks.

why your systems suck

It's been a while since I posted, I know. The delay hasn’t been for lack of interest or distraction as much as it has been from the fact that I just haven’t been doing anything interesting. Most of my time lately has gone to fixing “little stuff.” You know, that old app that works most of the time but has an issue that wakes everyone up once a month. Or that emergency “gotta-have-it” report that no one will bother to look at. Or that special request that comes from the local potentate that is meaningless, but that everything stops for because the potentate wants it.

It’s given me time to think about the overall question though: why do computer systems suck so bad?

If you’re bothering to read this blog, I assume you have to be a geek. Otherwise, you’d not be wasting your time. But we all know that geeks like to waste their time, so you’re here. So, for a minute, pretend you’re not a geek. Let’s say you’re a high-powered manager in a decent organization. You were that guy who played second string on the football team in collage and drank a bit too much the night before the early morning practice. Or you were that young woman whose social calendar was filled with must-do events and whose classes just kept getting in the way.

But you got your MBA and found you had a real aptitude for understanding and managing how companies actually make money.

Now, you find yourself going in each day and looking at a spreadsheet with your morning coffee. This spreadsheet gives you a snapshot of yesterday’s performance and today’s challenges. And it sucks. The tabs aren’t right. The numbers are usually off. The thing is often late. You’ve learned to rely on this and you need it to get the competitive edge you crave. But the thing can’t even reliably calculate something as simply as a sales conversion rate.

You call your IT director and scream into your phone in what becomes a daily ritual. And you just can’t understand why creating a simple spreadsheet should be so beyond your IT department.

Why is it? You’ve got a bunch of good people who all work too many hours and really want to make it right. You’ve got a – maybe not ideal but – reasonable budget. What’s wrong?

What IT groups usually miss is the big picture and the end goal. That business manager cares about his or her spreadsheet. They couldn’t care less that their IT department just worked a 14 hour day to install a new version of a J2EE engine.

What tends to happen in IT is that you get a developer. Let’s say that developer’s name is Marvin (you know… the guy who wears white socks with black shoes and reads comic books at lunch). And he’s pretty good. The code that he writes incorporates complex logic to pull data across a variety of distributed platforms, converts data types – because we all know that duplicated data isn’t really cloned, it’s mutated – and does these complex mathematical calculations that would make a physics professor blink. And it works pretty well – say about 90% of the time. Let’s say that 3 days each month (or about 3/30) it has some issue. It could be something small, like it tried to write data, but couldn’t get a lock for one of the rows. It could be an all-out crash that was really simple to fix by just restarting the app, but required a little manual intervention. Or it could be anything in between – maybe Marvin has a bug in one of his calculations, so that if some value is less than zero he gets the wrong results. But, all in all, it only has issues 3 days each month. Not a huge deal. Someone calls Marvin, he gets out of bed, logs in, fixes the issue and republishes the data.

But suppose the server admin (Alvin) has the same success rate – 9 in 10. About 3 days each month, the server runs out of memory or disk or drops off line without warning to auto-install new patches or something.

If the failure rate of Marvin’s process is 1 in 10 and the failure rate of Alvin’s server is 1 in 10, then the sum of the failure rates is 20%. But here’s the kicker. The whole is greater than the sum of the parts, because a failure in the server can cause downstream consequences that may not be visible instantly. (This is one of the things I can’t get my infrastructure staff to see, by the way. So as part of his process, Marvin, let’s say, writes some temp files. When he gets called because the server did an unplanned restart and he needs to re-run his process, he logs in, goes to a command prompt and runs the process. But he runs it with his credentials, not the credentials of the scheduler’s account, thereby causing the scheduled process to run into privilege issues on its next run. Now he’s just created a problem that was not part of either 10% failure rate. It’s a perfectly reasonable mistake to make. It doesn’t make Marvin a bad guy.

But the end result is that the total failure rate of the end process is now 10% for Melvin’s process + 10% for Alvin’s server + X% for mistakes made during clean up + Y% for other failures in the process caused by fallout from the server crash – missing temp files, lost session data, etc.

In the end, the failure rate of the whole process may be something like 25% or 30%. And that likely doesn’t include the failure of whatever systems Melvin is pulling the data from. If those systems also have a 10% failure rate, and their failure can cause downstream problems, this can add an additional 15% or more to the overall failure of the process.

So the reason computer systems suck is that the failure rates grow geometrically with each new error.

How do you fix it?

Naturally, you need to fix the individual issues. You need to get Melvin’s 10% failure rate down to 5% and then under 2%. But the real answer is that the systems need to be more loosely coupled and self-resilient. Any cross-system dependency needs to be identified and planned for. And someone needs to own – not the architecture of the individual pieces but – the architecture of the interaction between the pieces. This is probably the most overlooked piece of the puzzle.

Someone once said: “the more complex you make the drain, the easier it is to stop up the pluming”.