Trigger alternatives

Building and Optimizing Triggers in SQL Server

Florin Angelescu

Instructor

Triggers vs. stored procedures

Triggers

Fired automatically by an event

-- Will fire an INSERT trigger
INSERT INTO Orders [...];

Don't allow parameters or transactions
Cannot return values as output

Stored procedures

Run only when called explicitly

-- Will run the stored procedure
EXECUTE sp_DailyMaintenance;

Accept input parameters and transactions
Can return values as output

Triggers vs. stored procedures

Triggers

Used for:

auditing
integrity enforcement

Stored procedures

Used for:

general tasks
user-specific needs

Triggers vs. computed columns

Triggers

calculate column values
use columns from other tables for calculations

INSERT or UPDATE used to calculate

-- Used in the trigger body
[...]
UPDATE
SET TotalAmount = Price * Quantity
[...]

Computed columns

calculate column values
use columns only from the same table for calculations

calculation defined when creating the table

-- Column definition
[...]
TotalAmount AS Price * Quantity
[...]

Example of a computed column

CREATE TABLE [SalesWithPrice]
(
    [OrderID] INT IDENTITY(1,1),
    [Customer] NVARCHAR(50),
    [Product] NVARCHAR(50),
    [Price] DECIMAL(10,2),
    [Currency] NVARCHAR(3),
    [Quantity] INT,
    [OrderDate] DATE DEFAULT (GETDATE()),
    [TotalAmount] AS [Quantity] * [Price]
);

Using a trigger as a computed column

CREATE TRIGGER [SalesCalculateTotalAmount]
ON [SalesWithoutPrice]
AFTER INSERT
AS
    UPDATE [sp]
    SET [sp].[TotalAmount] = [sp].[Quantity] * [p].[Price]
    FROM [SalesWithoutPrice] AS [sp]
    INNER JOIN [Products] AS [p] ON [sp].Product = [p].[Product]
    WHERE [sp].[TotalAmount] IS NULL;

Let's compare them in practice!

Building and Optimizing Triggers in SQL Server

Preparing Video For Download...