SQL triggers are essential in database management systems (DBMS). They enable SQL statements to run when specific database events occur such as when someone adds, changes, or removes data. Triggers are commonly used to maintain data integrity, track changes, and apply business rules automatically, without needing manual input.
This article will break down SQL triggers covering their types how to write them, and real-world examples. We will look at different kinds of triggers such as DML triggers, DDL triggers, and logon triggers. We'll also explore how SQL triggers work in various database systems like SQL Server MySQL, and Oracle.
What is SQL Triggers?
A trigger is a stored procedure in a database that automatically invokes whenever a special event in the database occurs. By using SQL triggers, developers can automate tasks, ensure data consistency, and keep accurate records of database activities. For example, a trigger can be invoked when a row is inserted into a specified table or when specific table columns are updated.
In simple words, a trigger is a collection of SQL statements with particular names that are stored in system memory. It belongs to a specific class of stored procedures that are automatically invoked in response to database server events. Every trigger has a table attached to it.
Syntax
create trigger [trigger_name]
[before | after]
{insert | update | delete}
on [table_name]
FOR EACH ROW
BEGIN
END;
Key Terms
- trigger_name: The name of the trigger to be created.
- BEFORE | AFTER: Specifies whether the trigger is fired before or after the triggering event (INSERT, UPDATE, DELETE).
- {INSERT | UPDATE | DELETE}: Specifies the operation that will activate the trigger.
- table_name: The name of the table the trigger is associated with.
- FOR EACH ROW: Indicates that the trigger is row-level, meaning it executes once for each affected row.
- trigger_body: The SQL statements to be executed when the trigger is fired.
Why Should You Use SQL Triggers?
Using SQL triggers brings several benefits to the table:
- Automation: Triggers handle repetitive tasks, saving our time and effort.
- Consistency & Data Integrity: Automatically enforcing rules ensures that our data remains clean and accurate.
- Business Rules Enforcement: Triggers can help ensure that changes to our database follow your business logic.
- Audit Trails: Track changes automatically, making it easier to monitor and record data updates.
Now that you know why triggers are important, let’s look at how to create and use them effectively.
Types of SQL Triggers
Triggers can be categorized into different types based on the action they are associated with:
1. DDL Triggers
The Data Definition Language (DDL) command events such as Create_table, Create_view, drop_table, Drop_view, and Alter_table cause the DDL triggers to be activated. They allow us to track changes in the structure of the database. The trigger will prevent any table creation, alteration, or deletion in the database.
Example: Prevent Table Deletions
CREATE TRIGGER prevent_table_creation
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
PRINT 'you can not create, drop and alter table in this database';
ROLLBACK;
END;
Output
2. DML Triggers
DML triggers fire when we manipulate data with commands like INSERT
, UPDATE
, or DELETE
. These triggers are perfect for scenarios where we need to validate data before it is inserted, log changes to a table, or cascade updates across related tables.
Example: Prevent Unauthorized Updates Let’s say you want to prevent users from updating the data in a sensitive students
table. We can set up a trigger to handle that:
CREATE TRIGGER prevent_update
ON students
FOR UPDATE
AS
BEGIN
PRINT 'You can not insert, update and delete this table i';
ROLLBACK;
END;
Output
DML Trigger3. Logon Triggers
These triggers are fired in response to logon events. Logon triggers are useful for monitoring user sessions or restricting user access to the database. As a result, the PRINT statement messages and any errors generated by the trigger will all be visible in the SQL Server error log.
Authentication errors prevent logon triggers from being used. These triggers can be used to track login activity or set a limit on the number of sessions that a given login can have in order to audit and manage server sessions.
Example: Track User Logins
CREATE TRIGGER track_logon
ON LOGON
AS
BEGIN
PRINT 'A new user has logged in.';
END;
Real-World Use Cases of SQL Triggers
Triggers can automatically perform tasks, like updating related tables when data changes. Imagine we have a database for students, where the student_grades
table holds individual subject grades. If the grade of a student is updated, we may also need to update the total_scores
table
CREATE TRIGGER update_student_score
AFTER UPDATE ON student_grades
FOR EACH ROW
BEGIN
UPDATE total_scores
SET score = score + :new.grade
WHERE student_id = :new.student_id;
END;
This ensures that every time a student's grade is updated, the total score in the total_scores
table is automatically recalculated.
2. Data Validation (Before Insert Trigger Example)
Triggers can be used to validate data before it is inserted into a table, ensuring that the data follows specific business rules. For instance, we may want to ensure that the grades being inserted are within a valid range (say 0 to 100).
CREATE TRIGGER validate_grade
BEFORE INSERT ON student_grades
FOR EACH ROW
BEGIN
IF :new.grade < 0 OR :new.grade > 100 THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid grade value.');
END IF;
END;
The trigger checks if the inserted grade is valid. If not, it throws an error and prevents the insertion.
Viewing and Managing Triggers in SQL
If we are working with many tables across multiple databases, we can use a simple query to list all available triggers in our SQL Server instance. This is helpful for tracking and managing triggers, especially when dealing with tables that have similar names across databases.
SELECT name, is_instead_of_trigger
FROM sys.triggers
WHERE type = 'TR';
Key Terms
- name: The name of the trigger.
- is_instead_of_trigger: Whether the trigger is an INSTEAD OF trigger.
- type = 'TR': This filters the results to show only triggers.
The SQL Server Management Studio makes it very simple to display or list all triggers that are available for any given table. The following steps will help us accomplish this: Go to the Databases menu, select the desired database, and then expand it.
- Select the Tables menu and expand it.
- Select any specific table and expand it.
- We will get various options here. When we choose the Triggers option, it displays all the triggers available in this table.
BEFORE and AFTER Triggers
SQL triggers can be specified to run BEFORE or AFTER the triggering event.
- BEFORE Triggers: These run before the action (INSERT, UPDATE, DELETE) is executed. They’re great for data validation or modifying values before they are committed to the database.
- AFTER Triggers: Execute after the SQL statement completes. Useful for logging or cascading updates to other tables.
Example: Using BEFORE Trigger for Calculations
Given Student Report Database, in which student marks assessment is recorded. In such a schema, create a trigger so that the total and percentage of specified marks are automatically inserted whenever a record is inserted. Here, a trigger will invoke before the record is inserted so BEFORE Tag can be used.
Query
mysql>>desc Student;
Student DatatypeBelow SQL statement will create a trigger in the student database in which whenever subjects marks are entered, before inserting this data into the database, the trigger will compute those two values and insert them with the entered values. In this way, triggers can be created and executed in the databases.
Stud_marksOutput
OutputAdvantages of Triggers
- Data Integrity: Triggers help enforce consistency and business rules, ensuring that data follows the correct format.
- Automation: Triggers eliminate the need for manual intervention by automatically performing tasks such as updating, inserting, or deleting records when certain conditions are met.
- Audit Trail: Triggers can track changes in a database, providing an audit trail of INSERT, UPDATE, and DELETE operations.
- Performance: By automating repetitive tasks, triggers improve SQL query performance and reduce manual workload.
Conclusion
SQL triggers are a powerful feature for automating and enforcing rules in our database management system. Whether we're ensuring data integrity, automating updates, or preventing unauthorized changes, triggers can save time and improve consistency in database operations. With various types of triggers available, including DML triggers, DDL triggers, and logon triggers, they can be tailored to a wide range of use cases. Understanding and implementing SQL triggers effectively is important for maintaining a strong and efficient database.
Similar Reads
SQL Interview Questions Are you preparing for a SQL interview? SQL is a standard database language used for accessing and manipulating data in databases. It stands for Structured Query Language and was developed by IBM in the 1970's, SQL allows us to create, read, update, and delete data with simple yet effective commands.
15+ min read
SQL Tutorial SQL is a Structured query language used to access and manipulate data in databases. SQL stands for Structured Query Language. We can create, update, delete, and retrieve data in databases like MySQL, Oracle, PostgreSQL, etc. Overall, SQL is a query language that communicates with databases.In this S
11 min read
SQL Commands | DDL, DQL, DML, DCL and TCL Commands SQL commands are crucial for managing databases effectively. These commands are divided into categories such as Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Data Query Language (DQL), and Transaction Control Language (TCL). In this article, we will e
7 min read
Introduction of ER Model The Entity-Relationship Model (ER Model) is a conceptual model for designing a databases. This model represents the logical structure of a database, including entities, their attributes and relationships between them. Entity: An objects that is stored as data such as Student, Course or Company.Attri
10 min read
DBMS Tutorial â Learn Database Management System Database Management System (DBMS) is a software used to manage data from a database. A database is a structured collection of data that is stored in an electronic device. The data can be text, video, image or any other format.A relational database stores data in the form of tables and a NoSQL databa
7 min read
SQL Joins (Inner, Left, Right and Full Join) SQL joins are fundamental tools for combining data from multiple tables in relational databases. Joins allow efficient data retrieval, which is essential for generating meaningful observations and solving complex business queries. Understanding SQL join types, such as INNER JOIN, LEFT JOIN, RIGHT JO
6 min read
TCP/IP Model The TCP/IP model (Transmission Control Protocol/Internet Protocol) is a four-layer networking framework that enables reliable communication between devices over interconnected networks. It provides a standardized set of protocols for transmitting data across interconnected networks, ensuring efficie
7 min read
Normal Forms in DBMS In the world of database management, Normal Forms are important for ensuring that data is structured logically, reducing redundancy, and maintaining data integrity. When working with databases, especially relational databases, it is critical to follow normalization techniques that help to eliminate
7 min read
Basics of Computer Networking A computer network is a collection of interconnected devices that share resources and information. These devices can include computers, servers, printers, and other hardware. Networks allow for the efficient exchange of data, enabling various applications such as email, file sharing, and internet br
14 min read
ACID Properties in DBMS In the world of DBMS, transactions are fundamental operations that allow us to modify and retrieve data. However, to ensure the integrity of a database, it is important that these transactions are executed in a way that maintains consistency, correctness, and reliability. This is where the ACID prop
8 min read