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.