Introduction
B U I L D I N G A N D O P TI M I Z I N G TR I G G E R S I N S Q L S E R V E R
Etibar Vazirov
Slides credit: Florin Angelescu
What is a trigger?
Special type of stored procedure
Executed when an event occurs in the database server
BUILDING AND OPTIMIZING TRIGGERS IN SQL SERVER
Types of trigger (based on T-SQL commands)
Data Manipulation Language (DML) triggers
INSERT , UPDATE or DELETE statements
Data De nition Language (DDL) triggers
CREATE , ALTER or DROP statements
Logon triggers
LOGON events
BUILDING AND OPTIMIZING TRIGGERS IN SQL SERVER
Types of trigger (based on behavior)
AFTER trigger
The original statement executes
Additional statements are triggered
Examples of use cases
Rebuild an index after a large insert
Notify the admin when data is updated
BUILDING AND OPTIMIZING TRIGGERS IN SQL SERVER
Types of trigger (based on behavior)
INSTEAD OF trigger
The original statement is prevented from execution
A replacement statement is executed instead
Examples of use cases
Prevent insertions
Prevent updates
Prevent deletions
Prevent object modi cations
Notify the admin
BUILDING AND OPTIMIZING TRIGGERS IN SQL SERVER
Trigger definition (with AFTER)
-- Create the trigger by giving it a descriptive name
CREATE TRIGGER ProductsTrigger
-- The trigger needs to be attached to a table
ON Products
-- The trigger behavior type
AFTER INSERT
-- The beginning of the trigger workflow
AS
-- The action executed by the trigger
PRINT ('An insert of data was made in the Products table.');
BUILDING AND OPTIMIZING TRIGGERS IN SQL SERVER
Trigger definition (with INSTEAD OF)
-- Create the trigger by giving it a descriptive name
CREATE TRIGGER PreventDeleteFromOrders
-- The trigger needs to be attached to a table
ON Orders
-- The trigger behavior type
INSTEAD OF DELETE
-- The beginning of the trigger workflow
AS
-- The action executed by the trigger
PRINT ('You are not allowed to delete rows from the Orders table.');
BUILDING AND OPTIMIZING TRIGGERS IN SQL SERVER
AFTER vs. INSTEAD OF
CREATE TRIGGER MyFirstAfterTrigger CREATE TRIGGER MyFirstInsteadOfTrigger
ON Table1 ON Table2
-- Triggered after -- Triggered instead of
-- the firing event (UPDATE) -- the firing event (UPDATE)
AFTER UPDATE INSTEAD OF UPDATE
AS AS
{trigger_actions_section}; {trigger_actions_section};
BUILDING AND OPTIMIZING TRIGGERS IN SQL SERVER
Let's practice!
B U I L D I N G A N D O P TI M I Z I N G TR I G G E R S I N S Q L S E R V E R
How DML triggers
are used
B U I L D I N G A N D O P TI M I Z I N G TR I G G E R S I N S Q L S E R V E R
Etibar
FlorinVazirov
Angelescu
Slides credit: Florin Angelescu
Instructor
Why should we use DML triggers?
Initiating actions when manipulating data
Preventing data manipulation
Tracking data or database object changes
User auditing and database security
BUILDING AND OPTIMIZING TRIGGERS IN SQL SERVER
Deciding between AFTER and INSTEAD OF
CREATE TRIGGER MyFirstAfterTrigger CREATE TRIGGER MyFirstInsteadOfTrigger
ON Table1 ON Table2
-- Triggered after -- Triggered instead of
-- the firing event (UPDATE) -- the firing event (UPDATE)
AFTER UPDATE INSTEAD OF UPDATE
AS AS
{trigger_actions_section}; {trigger_actions_section};
BUILDING AND OPTIMIZING TRIGGERS IN SQL SERVER
Deciding between AFTER and INSTEAD OF
AFTER trigger INSTEAD OF trigger
Initial event fires the trigger Initial event fires the trigger
Initial event executes Initial event is not executed anymore
The trigger actions execute The trigger actions execute
BUILDING AND OPTIMIZING TRIGGERS IN SQL SERVER
AFTER trigger usage example
Data is inserted into a sales table CREATE TRIGGER SalesNewInfoTrigger
Start a data cleansing procedure ON Sales
AFTER INSERT
Generate a table report with the procedure
AS
results
EXEC sp_cleansing @Table = 'Sales';
Notify the database administrator EXEC sp_generateSalesReport;
EXEC sp_sendnotification;
BUILDING AND OPTIMIZING TRIGGERS IN SQL SERVER
Practice questions
B U I L D I N G A N D O P TI M I Z I N G TR I G G E R S I N S Q L S E R V E R
Trigger alternatives
B U I L D I N G A N D O P TI M I Z I N G TR I G G E R S I N S Q L S E R V E R
Etibar
FlorinVazirov
Angelescu
Slides credit: Florin Angelescu
Instructor
Triggers vs. stored procedures
Triggers Stored procedures
Fired automatically by an event Run only when called explicitly
-- Will fire an INSERT trigger -- Will run the stored procedure
INSERT INTO Orders [...]; EXECUTE sp_DailyMaintenance;
Don't allow parameters or transactions Accept input parameters and transactions
Cannot return values as output Can return values as output
BUILDING AND OPTIMIZING TRIGGERS IN SQL SERVER
Triggers vs. stored procedures
Triggers Stored procedures
Used for: Used for:
auditing general tasks
integrity enforcement user-specific needs
BUILDING AND OPTIMIZING TRIGGERS IN SQL SERVER
Triggers vs. computed columns
Triggers Computed columns
calculate column values calculate column values
use columns from other tables for use columns only from the same table for
calculations calculations
INSERT or UPDATE used to calculate calculation defined when creating the table
-- Column definition
-- Used in the trigger body
[...]
[...]
TotalAmount AS Price * Quantity
UPDATE
[...]
SET TotalAmount = Price * Quantity
[...]
BUILDING AND OPTIMIZING TRIGGERS IN SQL SERVER
E xample 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]
);
BUILDING AND OPTIMIZING TRIGGERS IN SQL SERVER
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;
BUILDING AND OPTIMIZING TRIGGERS IN SQL SERVER
Let's compare them
in practice!
B U I L D I N G A N D O P TI M I Z I N G TR I G G E R S I N S Q L S E R V E R
