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

• aykut canturk

  SSC Eights!

  Points: 870

  More actions

  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 ?

• Eirikur Eiriksson

  SSC Guru

  Points: 182943

  More actions

  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