For an instructor lead, in-depth look at learning SQL click below.
A trigger in Structured Query Language (SQL) is a special kind of stored procedure that automatically runs or fires when an event happens in the database. Triggers are mainly used to maintain the integrity of the data in the database. They come in handy when there’s a need to evaluate an operation against some business rule or logic. Triggers are also useful for auditing changes in a database and can aid in custom error messages.
Types of SQL Triggers
Essentially, there are two types of triggers in SQL: DML triggers and DDL triggers.
DML (Data Manipulation Language) triggers fire when a user tries to modify data through a data manipulation language (DML) event i.e., INSERT, UPDATE, or DELETE statement.
DDL triggers run in response to a variety of data definition language (DDL) events, such as the creation of a table (CREATE_TABLE) or altering a table (ALTER_TABLE).
Syntax for a Trigger
|
1 2 3 4 5 6 |
CREATE TRIGGER trigger_name ON table_name [FOR|AFTER|INSTEAD OF] [INSERT [OR] | UPDATE [OR] | DELETE] trigger body |
Creating a Simple Trigger
A simple trigger could be to update a ‘last_modified’ field every time an update is made to a row in a given table. Let’s consider a table ‘students’ with fields ‘id’, ‘name’, ‘grade’ and ‘last_modified’ where ‘last_modified’ is of type datetime and is intended to hold the most recent time a row was updated.
Creating the Trigger
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TRIGGER update_last_modified ON students FOR UPDATE AS BEGIN UPDATE students SET last_modified = GETDATE() FROM students s INNER JOIN inserted i ON s.id = i.id; END |
The ‘inserted’ table is a special table used in triggers to hold the new rows that have been inserted or the new values of the rows that are being updated. This simple trigger will update the ‘last_modified’ field to the current date and time every time an update is made to a row in the ‘students’ table.
Some Considerations
Note that triggers operate at the row level, not at the table level. This means a trigger would fire for each row that an event occurs on.
One other important thing to note is that triggers can slow down the operation of the database since they function on a per-row basis, so be careful to use them wisely.
In summary, while triggers can be powerful tools when used appropriately, they should not be used to enforce all types of business rule or logic due to some of their limitations, particularly their performance costs.