CODEWITHSUNDEEP
sqlsql-servert-sql
user3832856
user3832856

Reputation: 183

How to optimize a trigger?

CREATE TRIGGER T 
ON TABLE_2 
AFTER INSERT 
AS
     DECLARE @bought_t int,
             @name_t varchar(20)

     SELECT @name_t = name_t 
     FROM inserted

     SELECT @bought_t = bought_t 
     FROM TABLE_1 
     WHERE name_t = @name_t

     IF @bought_t < 100 
     BEGIN
         UPDATE TABLE_1
         SET bought_t = @bought_t + 1
         WHERE TABLE_1.name_t = @name_t
     END
     ELSE 
        ROLLBACK TRANSACTION

The column (TABLE_1) I'm making the update to after the insert happens in the 'TABLE_2' is supposed to hold values between 50 and 100. So I'm asking If this trigger is as professional and optimized as It could be? or I have some flaws that could lead to bugs/security issues.

Upvotes: 2

Views: 1661

Answers (1)

marc_s
marc_s

Reputation: 754268

Basically, you need to completely rewrite your trigger to be set-based and to be able to work with multiple rows in the Inserted pseudo table.

Fortunately, that also makes it easier - in my opinion - try something like this:

CREATE TRIGGER T 
ON TABLE_2 
AFTER INSERT 
AS
    UPDATE T1
    SET bought_t = bought_t + 1
    FROM TABLE_1 T1
    INNER JOIN Inserted i ON i.name_t = T1.name_t
    WHERE T1.bought_t < 100

UPDATE: demo to prove this works:

-- create the two tables
CREATE TABLE TABLE_2 (ID INT NOT NULL IDENTITY(1,1), ProdName VARCHAR(50))
CREATE TABLE TABLE_1 (ProdName VARCHAR(50), Bought INT)
GO

-- create trigger on "TABLE_2" to update "TABLE_1"    
CREATE TRIGGER T2Insert
ON TABLE_2
AFTER INSERT
AS
    UPDATE T1
    SET Bought = Bought + 1
    FROM TABLE_1 T1
    INNER JOIN Inserted i ON T1.ProdName = i.ProdName
    WHERE T1.Bought < 100
GO

-- initialize TABLE_1 with some seed data
INSERT INTO dbo.TABLE_1 (ProdName, Bought)
VALUES ( 'Prod1', 0), ('Prod2', 20), ('Prod3', 40), ('Prod4', 40), ('Prod100', 100)

-- insert new values into TABLE_2
INSERT INTO dbo.TABLE_2 (ProdName)
VALUES  ('Prod1'), ('Prod100'), ('Prod2'), ('Prod4')

-- get data to check
SELECT * FROM dbo.TABLE_1

This renders output:

enter image description here

As you can easily see:

  • Prod1, Prod2, Prod4 that were inserted caused an update of the value Bought
  • Prod100 which was also inserted did not cause an update of Bought

UPDATE #2: if you need to be able to insert multiple identical values at once, you need to slightly enhance your trigger like this:

CREATE TRIGGER T2Insert
ON TABLE_2
AFTER INSERT
AS
    -- declare table variable to hold names and update counts 
    DECLARE @UpdateCount TABLE (Name VARCHAR(50), UpdCount INT)

    -- from the "Inserted" table, determine which names are being
    -- inserted how many times using GROUP BY    
    INSERT INTO @UpdateCount (Name, UpdCount)
        SELECT ProdName, COUNT(*)
        FROM Inserted
        GROUP BY ProdName

    -- now join to this temporary table, and update as many times
    -- as needed (instead of +1 for all cases)
    UPDATE T1
    SET Bought = Bought + uc.UpdCount
    FROM TABLE_1 T1
    INNER JOIN @UpdateCount uc ON uc.Name = T1.ProdName
    WHERE T1.Bought < 100
GO

Upvotes: 1

Related Questions