When I was recently troubleshooting a blog post that went missing, I ran into the following issue and thought I'd share it with y'all.
Each article or post on my blog lives as a separate row in a database table. So when I discovered the missing post, I went poking around the various tables in the database. Sure enough, there was an older copy of that table that contained the missing row, so I figured it would be easy to just copy the row back into the main table. It turned out to be difficult enough that Greg, John, and I wasted quite a bit of time trying to figure out how to do it (and I think the only reason I figured it out first was that they had more important tasks to focus on).
This table uses an IDENTITY column as the primary key. This column provides an auto-incremented value for each new posting, ensuring a unique ID for each posting. By default, you can't specify a value for an IDENITY column; you have to use the SET IDENTITY_INSERT table ON command to tell SQL Server to allow you to insert a custom value (including one that might have been previously used) into the column. Greg and John already knew about this, but we were still getting a weird result after doing so: the row appeared to insert properly, but it wasn't showing up in the table afterwards.
What I finally was able to piece together after reading three or four separate hints on the Web is that triggers and IDENTITY columns interact in strange ways. So I wondered what would happen if I disabled the triggers before trying the insert. Sure enough, that proved to be the problem -- the active triggers prevented the custom-specififed IDENTITY column value (and thus the rest of the row) from being committed.
Here's the SQL code I used:
ALTER TABLE MyTable DISABLE TRIGGER MyTrigger
SET IDENTITY_INSERT MyTable ON
INSERT INTO MyTable (IDCol, Col1, Col2, Col3, Col4)
SELECT IDCol, Col1, Col2, Col3, Col4 FROM MyTable_Backup
WHERE IDCol=<value>
SET IDENTITY_INSERT MyTable OFF
ALTER TABLE MyTable ENABLE TRIGGER MyTrigger
Note that even with this trick, you cannot UPDATE the value of an IDENTITY column. You will have to copy the data into a new row with a new custom value and delete the old one, if that's what you're trying to do.