Post reply

How can I get ID and set to anouther field on instert

  • February 21, 2015 at 3:38 am

    #315493

    Hi friends,

    I want to learn if its possible to get ID in trigger and set it to another field.

    I can do it by using scope_identity and using update command but I dont want to run another update command.

    This is a very heavy loaded system that runs maybe 10-30 transactions on every second and I cannot afford that. I come up with a solution by using sequences but if I could use ID would be better.

    any idea ?

  • February 21, 2015 at 4:22 am

    #1779113

    Quick trigger example, should be sufficient to get you passed this hurdle.

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    IF OBJECT_ID(N'dbo.TBL_SECONDARY_TABLE') IS NOT NULL DROP TABLE dbo.TBL_SECONDARY_TABLE;

    IF OBJECT_ID(N'dbo.TBL_PRIMARY_TABLE' ) IS NOT NULL DROP TABLE dbo.TBL_PRIMARY_TABLE;

    CREATE TABLE dbo.TBL_PRIMARY_TABLE

    (

    PRIMARY_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_PRIMARY_TABLE_PRIMARY_ID PRIMARY KEY CLUSTERED

    ,PRIMARY_VAL INT NOT NULL

    ,PRIMARY_DTS DATETIME NOT NULL CONSTRAINT DFLT_DBO_TBL_PRIMARY_TABLE_PRIMARY_DTS DEFAULT (GETDATE())

    );

    CREATE TABLE dbo.TBL_SECONDARY_TABLE

    (

    SECONDARY_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_SECONDARY_TABLE_SECONDARY_ID PRIMARY KEY CLUSTERED

    ,PRIMARY_ID INT NOT NULL CONSTRAINT FK_DBO_TBL_SECONDARY_TABLE_PRIMARY_ID_DBO_TBL_PRIMARY_TABLE_PRIMARY_ID FOREIGN KEY REFERENCES dbo.TBL_PRIMARY_TABLE (PRIMARY_ID)

    );

    GO

    CREATE TRIGGER dbo.TRG_DBO_TBL_PRIMARY_TABLE_FK_INSERT

    ON dbo.TBL_PRIMARY_TABLE

    AFTER INSERT

    AS

    BEGIN

    INSERT INTO dbo.TBL_SECONDARY_TABLE (PRIMARY_ID)

    SELECT I.PRIMARY_ID FROM inserted I;

    END

    GO

    INSERT INTO dbo.TBL_PRIMARY_TABLE (PRIMARY_VAL)

    VALUES (223),(224),(225),(226),(227),(228),(229),(230),(231),(232),(233),(234),(235);

    SELECT

    *

    FROM dbo.TBL_PRIMARY_TABLE PT

    INNER JOIN dbo.TBL_SECONDARY_TABLE ST

    ON PT.PRIMARY_ID = ST.PRIMARY_ID;

    Results

    PRIMARY_ID PRIMARY_VAL PRIMARY_DTS SECONDARY_ID PRIMARY_ID

    ----------- ----------- ----------------------- ------------ -----------

    13 235 2015-02-21 11:24:22.170 1 13

    12 234 2015-02-21 11:24:22.170 2 12

    11 233 2015-02-21 11:24:22.170 3 11

    10 232 2015-02-21 11:24:22.170 4 10

    9 231 2015-02-21 11:24:22.170 5 9

    8 230 2015-02-21 11:24:22.170 6 8

    7 229 2015-02-21 11:24:22.170 7 7

    6 228 2015-02-21 11:24:22.170 8 6

    5 227 2015-02-21 11:24:22.170 9 5

    4 226 2015-02-21 11:24:22.170 10 4

    3 225 2015-02-21 11:24:22.170 11 3

    2 224 2015-02-21 11:24:22.170 12 2

    1 223 2015-02-21 11:24:22.170 13 1

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply