Is there any alternative for Trigger After Insert

simpledeveloper

Hi, I have two columns in a table, one is Auto Identity set, and other one should be created as a string value with Id as Pad right, I have created a trigger but my lead is saying Triggers impact the performance, and to not create, is there any alternate for Triggers that would not effect Database performance at the same time do what I am expecting it to be done. My trigger is as follows:

CREATE TRIGGER AfterInsert
ON InspectionItems
AFTER INSERT
AS
BEGIN
declare @InspectionItemNumber nvarchar(20), @InspectionItemId int

select @InspectionItemId = ins.InspectionItemId FROM INSERTED ins;

update InspectionItems set InspectionItemNumber = 'T' + RIGHT('00000'+CAST(InspectionItemId AS VARCHAR(5)),6)
where InspectionItemId = @InspectionItemId

END
GO

Is there any alternate for trigger and doing the same and preserve the performance - thanks in advance.

Richard Deeming

Use a computed column: Specify Computed Columns in a Table - SQL Server | Microsoft Docs[^] If you want to continue using a trigger, you'll need to update it to account for the fact that a trigger can be fired for multiple rows at the same time:

CREATE TRIGGER AfterInsert
ON InspectionItems
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

UPDATE
    T
SET
    InspectionItemNumber = 'T' + RIGHT('00000' + CAST(InspectionItemId AS VARCHAR(5)), 6)
FROM
    InspectionItems As T
    INNER JOIN inserted As I
    ON I.InspectionItemId = T.InspectionItemId
;

END
GO

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

simpledeveloper

Thank you so much