SCOPE_IDENTITY And instead of using the Insert trigger

Question

Ok, I have a table without a natural key, only an integer identifier column as the primary key. I would like to insert and get the identifier value, but also use a trigger to ensure that certain fields are always set. Initially, the design was to be used instead of insert triggers, but this violates scope_identity. The output clause in the insert statement is also interrupted instead of the insert trigger. So, I came up with an alternative plan and would like to know if there is something clearly wrong with what I intend to do:

start a far-fetched 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

Complete a contrived example.

No, I do not do this for one small date field - this is just an example.

, , , ( TestDataModInfo), . , scope_identity() ( , ). , , ?

Answer 1

, SCOPE_IDENTITY . , @@IDENTITY.

, .

AFTER , ... , .

: SCOPE_IDENTITY parallelism SQL Server 2005

Answer 2

HAve OUTPUT ?

Answer 3

:

SELECT scope_identity();

http://wiki.alphasoftware.com/Scope_Identity+in+SQL+Server+with+nested+and+INSTEAD+OF+triggers

Answer 4

INSTEAD OF , , , Scope_Identity() @@Identity :

-- Inside of trigger
SET NOCOUNT ON;
INSERT dbo.YourTable VALUES(blah, blah, blah);
SET @YourTableID = Scope_Identity();

-- ... other DML that inserts to another identity-bearing table

-- Last statement in trigger
SELECT YourTableID INTO #Trash FROM dbo.YourTable WHERE YourTableID = @YourTableID;

, , , ( ).

SET @SQL =
   'SELECT identity(smallint, ' + Str(@YourTableID) + ', 1) YourTableID INTO #Trash';
EXEC (@SQL);

, Scope_Identity() NULL INSTEAD OF , spoofing. , @@Identity. , ADP Access Access , , .

, , parallelism @@Identity Scope_Identity() , OPTION (MAXDOP 1) TOP 1 VALUES, .