A trigger is a procedure that executes when a database event occurs.
To create a trigger use the CREATE TRIGGER command.
Triggers can help enforce data and referential integrity in the database.
This trigger recalculates the order amount after an orderitem row is changed
CREATE TRIGGER OrderItemUpdate
ON
OrderItem
AFTER
UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @OrderId INT;
SELECT @OrderId = INSERTED.OrderId
FROM INSERTED
UPDATE [Order]
SET TotalAmount = (SELECT SUM(UnitPrice * Quantity)
FROM OrderItem
WHERE OrderId = @OrderId)
WHERE [Order].Id = @OrderId;
END
Once created, this trigger will automatically run after an OrderItem is updated.
Triggers are procedures that automatically execute when a database event occurs.
These events include INSERT, UPDATE, and DELETE operations.
A trigger can execute AFTER or INSTEAD OF the action.
INSTEAD OF overrides the INSERT, UPDATE, and DELETE that triggered it.
Syntax to create a trigger.
CREATE TRIGGER trigger_name ON table_name AFTER | INSTEAD OF [INSERT],[UPDATE],[DELETE] AS sql_statements
Syntax to change a trigger.
ALTER TRIGGER trigger_name ON table_name AFTER | INSTEAD OF [INSERT],[UPDATE],[DELETE] AS sql_statements
Syntax to remove a trigger.
DROP TRIGGER trigger_name
| ORDERITEM |
|---|
| Id |
| OrderId |
| ProductId |
| UnitPrice |
| Quantity |
CREATE TRIGGER OrderItemBlock
ON
OrderItem
INSTEAD OF
UPDATE, DELETE
AS
RAISERROR ('Cannot change existing orderitems',1,1)
| ORDERITEM |
|---|
| Id |
| OrderId |
| ProductId |
| UnitPrice |
| Quantity |
ALTER TRIGGER OrderItemBlock
ON
OrderItem
INSTEAD OF
UPDATE, DELETE
AS
RAISERROR ('Illegal to change existing orders',1,1)
| ORDERITEM |
|---|
| Id |
| OrderId |
| ProductId |
| UnitPrice |
| Quantity |
DROP TRIGGER OrderItemBlock
| ORDERITEM |
|---|
| Id |
| OrderId |
| ProductId |
| UnitPrice |
| Quantity |
DROP TRIGGER OrderItemBlock
CREATE TRIGGER OrderItemPreventChange
ON
OrderItem
INSTEAD OF
UPDATE, DELETE
AS
RAISERROR ('Cannot change existing orderitems',1,1)
Renaming a trigger is not available in SQL Server.
To rename, drop the trigger and then re-create it with a new name.
Triggers fall into 3 categories:
| Trigger | Description |
|---|---|
| DML | React to DML (Data Manipulation Language) queries, such as, INSERT, UPDATE, and DELETE |
| DDL | React to DDL (Data Definition Language) queries, such as, CREATE, ALTER, and DROP |
| Logon | Triggers when a LOGON event is detected |
DML are the triggers presented above.
DDL and Logon Triggers are discussed next.
DDL Triggers respond to changes made to the database schema.
Events include CREATE, ALTER, DROP, GRANT, DENY, REVOKE actions.
A common use is to prevent certain changes to the data model.
Another use is to audit data model changes and log these into a log-table.
Syntax to create a DDL trigger.
CREATE TRIGGER trigger_name ON DATABASE | ALL SERVER FOR event_type | event_group AS sql_statement
There are literally dozens of event_types and event_groups.
Examples of event_type include: CREATE_TABLE or DROP_INDEX.
Examples of event_group include: DDL_TABLE_EVENTS or DDL_DATABASE_SECURITY_EVENTS.
Problem: Log all database index modifications to a log table.
CREATE TRIGGER TriggerIndexChange
ON
DATABASE
FOR
CREATE_INDEX, ALTER_INDEX, DROP_INDEX
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO IndexLog (EventData, ChangedBy)
VALUES (EVENTDATA(), USER)
END
This trigger assumes a log table named IndexLog has been created.
The EVENTDATA() function returns information about the database event.
Anytime an index changes, this trigger will add a new entry to the log table.
Problem: Prevent users from making table schema changes. Rollback any attempt.
CREATE TRIGGER TriggerTableRestrictions
ON
DATABASE
FOR
CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
PRINT 'No table modifications can be made'
ROLLBACK
END
This trigger responds to all table schema changes.
An error message displays and the table change is rolled back (undone).
A logon trigger fires when a new database connection is established.
Common use cases include:
Syntax to create a Logon trigger.
CREATE TRIGGER trigger_name ON ALL SERVER FOR LOGON AS BEGIN sql_statement END
These are some use cases where triggers can be very helpful:
Enforce referencial integrity across the database
Event logging and auditing
Generate derived or calculated column values instantly
Replicate tables synchronously
Impose security authorizations
Prevent invalid transactions
Things to consider before using triggers.
Triggers can negatively affect performance.
Triggers are difficult to debug and can cause hard to solve bugs.
Debugging nested and recursive triggers is extremely hard.
Triggers are easily forgotten, leaving developers scratching their heads.
As a general rule it is best to use triggers sparingly.
This query will list all triggers in the database.
SELECT * FROM sys.triggers WHERE type = 'TR';
Triggers are easily forgotten, so a list of triggers can be useful when trying to identify a mysterious bug in the database.
