February 21, 2015 at 3:38 am
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
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