views:

583

answers:

2

OK, I have a table with no natural key, only an integer identity column as it's primary key. I'd like to insert and retrieve the identity value, but also use a trigger to ensure that certain fields are always set. Originally, the design was to use instead of insert triggers, but that breaks scope_identity. The output clause on the insert statement is also broken by the instead of insert trigger. So, I've come up with an alternate plan and would like to know if there is anything obviously wrong with what I intend to do:

begin contrived example:

    CREATE TABLE [dbo].[TestData] (
    [TestId] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [Name] [nchar](10) NOT NULL)

    CREATE TABLE [dbo].[TestDataModInfo](
    [TestId] [int] PRIMARY KEY NOT NULL,
    [RowCreateDate] [datetime] NOT NULL)

    ALTER TABLE [dbo].[TestDataModInfo]  WITH CHECK ADD  CONSTRAINT
    [FK_TestDataModInfo_TestData] FOREIGN KEY([TestId])
    REFERENCES [dbo].[TestData] ([TestId]) ON DELETE CASCADE

CREATE TRIGGER [dbo].[TestData$AfterInsert]
   ON [dbo].[TestData]
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    INSERT INTO [dbo].[TestDataModInfo]
           ([TestId],
            [RowCreateDate])
     SELECT
      [TestId],
            current_timestamp
     FROM inserted

    -- Insert statements for trigger here

END

End contrived example.

No, I'm not doing this for one little date field - it's just an example.

The fields that I want to ensure are set have been moved to a separate table (in TestDataModInfo) and the trigger ensures that it's updated. This works, it allows me to use scope_identity() after inserts, and appears to be safe (if my after trigger fails, my insert fails). Is this bad design, and if so, why?

+1  A: 

As you mentioned, SCOPE_IDENTITY is designed for this situation. It's not affected by AFTER trigger code, unlike @@IDENTITY.

Apart from using stored procs, this is OK.

I use AFTER triggers for auditing because they are convenient... that is, write to another table in my trigger.

Edit: SCOPE_IDENTITY and parallelism in SQL Server 2005 cam have a problem

gbn
You can't really say that scope_identity isn't affected by trigger code - it returns null if you use an 'instead of insert' trigger.
Peter LaComb Jr.
@Peter, true, was typing too quickly.
gbn
A: 

HAve you tried using OUTPUT to get the value back instead?

HLGEM
Output also returns null when using instead of insert triggers.
Peter LaComb Jr.
well then I guess you really have no other choice but to use an after trigger.
HLGEM