SQL (Structured Query Language) is used to access and manipulate data on a database. There are many codes used in SQL.
A trigger in SQL is a specific type of stored procedure that will automatically run whenever there is an event in a database server. Triggers are typically stored and managed by a Database Management System (DBMS).
In this article, we will be discussing what a Trigger is in SQL. We’ll also look at the purpose of a SQL trigger, an example of a Trigger in SQL, and different types of SQL Triggers.
What Is The Purpose Of A SQL Trigger?
The purpose of a SQL trigger is to maintain referential integrity of any given data by altering the data systematically.
At any time, a trigger is connected to a single, specific table within a database. That being said, there can be any number of triggers associated with any table within a database.
As they are managed by a DBMS, triggers cannot be called or executed, they are automatically fired whenever data is modified within it’s connected table.
We mentioned in the introduction that a SQL can be thought of as a specific type of stored procedure. Whilst there are some similarities between stored procedures and triggers, there are also some key differences.
The main similarity is that, on a database level, they both contain procedural logic.
Stored procedures tend to be passive, in a sense that they are not event-driven. They are also not connected to any specific table within the database, unlike triggers.
Stored procedures are explicitly executed (meaning that there is an intention to execute a stored procedure) by using a CALL to a specific procedure. In this sense, triggers are passive, as they are implicitly executed as a result of particular clauses being executed.
What Are 3 Types Of SQL Triggers?
As mentioned above, triggers are managed by a DBMS and as such are outside of interference.
If any of the three clauses below are executed within a database, a SQL trigger will fire:
UPDATE, INSERT, DELETE
Each of these clauses can be used to modify data within a given table in a database.
UPDATE: is used to modify existing values
INSERT: is used to add new rows within a table
DELETE: is used to remove rows from a table
In certain situations, triggers can contain the above (UPDATE, INSERT, DELETE) logic within itself. This means that if a trigger is fired, say because of a modification to data, it will cause a second data modification and fire another trigger. This is known as a nested trigger.
The Three Groups of Triggers
DDL Trigger
DDL stands for Data Definition Language. A DDL Trigger will usually fire as a response to DDL events within a database.
They are commands which start with Create, Alter, Drop, Deny, Revoke, Grant, or Update Statistics, for example Create_view, Drop_table, Alter_table.
DDL triggers are applicable if you want to do the following:
DML Trigger
DML is an acronym for Data Manipulation Language. DML triggers are fired as a response to command events that start with Insert, Update, and Delete statements.
In terms of application, DML triggers are used to query other tables, enforce business rules/data integrity, and include Transact-SQL statements.
There are two types of DML triggers which fall under the DML banner. These are AFTER triggers and INSTEAD OF triggers.
After Triggers are executable after the action of Insert, Update, or Delete statements.
Using an Instead Of Trigger tells the database to execute a trigger instead of a statement. An example of this is an insert trigger will execute when an event occurs instead of the statement that would place values in the table.
Logon Trigger
As the name suggests, this trigger fires stored procedures in response to a Logon event. Usually, this event is raised whenever a user session is established with an instance of SQL Server. They fire post authentication phase of logging in, but pre user session establishment.
Their most common application is to audit and control server sessions, by tracking login activity, restricting logins, or limiting the number of sessions for a particular login.
When Might You Use SQL Triggers?
As we’ve mentioned earlier, triggers have a lot in common with stored procedures. However, when compared to a stored procedure, they are limited in what triggers can do.
With this in mind, it could be easier to maintain one stored procedure for any Insert, Update, or Delete clauses and make checks/add additional actions there.
The big caveat to this is that this isn’t always possible. If, for example, you inherited a system or simply do not want to put all the logic in one stored procedure, triggers can be a solution for many problems you might encounter.
In Summary
A SQL Trigger’s main function is to maintain referential integrity of any given data by making changes to the data systematically.
They cannot be called or executed, as they are automatically executed whenever data is modified.
There are three main groups of triggers: DDL, DML, and LOGON.
Within DML, there are two subcategories which are worth repeating: After Triggers and Instead Of Triggers.
A Trigger in SQL is a procedure which runs automatically whenever there is an event within the database.
Here at Open Query I want to make it as easy as possible for you to learn about topics that might not be easily accessible elsewhere online. I don’t know about you, but I haven’t seen many other websites talking about the MariaDB Foundation and its updates.
That’s what makes Open Query truly unique. While others might assume everyone knows this information already automatically, I am filling my online resource to help.
- What Is Percentile Rank? - February 15, 2022
- How To Use Distinct In SQL - February 14, 2022
- How To Drop A Column In SQL - February 14, 2022