Published: 03 Jan 2025 | Reading Time: 7 min read
The trigger in DBMS is a set of instructions that run when particular events occur in data. For example, an insertion, modification or deletion event can trigger a table containing information. Consider it as a preventive way that assists in managing data and processes, in addition to maintaining data integrity without human oversight. Triggers help when one needs to know when and how databases are modified to make the processes fast and definitive without the need for information and manual checks. In this article, we'll learn about database triggers in DBMS and how they work.
A trigger is a special kind of stored procedure that runs automatically when a specific event occurs in a database. When it comes to define trigger in dbms you can think of it as a set of instructions that kick in without manual input whenever data changes in a table.
Triggers are is automatically executed in response to certain database events such as an INSERT, UPDATE, or DELETE operation. or even when a user logs in to the database server. They can also fire off when tables are created, altered, or dropped. Essentially, triggers help automate tasks and maintain control over database changes.
Triggers in DBMS help automate processes, control data, and follow protocol without interference. ensuring compliance with business rules and the integrity of stored data. It takes care of the fact that a certain activity should exhibit a certain behavior, and there is no need for a person to intervene. They are triggered by events, for example, an insertion of data update of data or even deletion of certain data; they carry out preprogrammed actions on the event.
Imagine a banking application inside which customer balances are stored in a table. Whenever someone makes a transaction like a withdrawal or deposit, the system has to update the balance. This is done to maintain transaction logs for auditing purposes and to keep the record.
Instead of manually coding these updates, a trigger can handle it automatically. For example, a trigger can be set to fire whenever an UPDATE event happens on the balance table. The trigger would record the transaction details in a log table every time the balance changes. This ensures that all transactions are logged correctly, preventing human error and keeping accurate records.
Triggers in DBMS are special actions that happen automatically when certain events like changes or updates occur in a database. They are useful for making sure the data stays accurate and consistent during changes, and it doesn't need extra work later. There are different types of triggers, each designed to handle specific tasks. Some triggers react when data changes, while others deal with changes to the database structure.
DDL (Data Definition Language) triggers are activated when changes are made to the structure of the database, such as creating, changing, or deleting tables or other objects. They are useful for preventing accidental or unauthorised changes to the database.
DML (Data Manipulation Language) triggers are activated by changes to the data in a table. It can happen when new data is added (INSERT), updated, or deleted. These triggers help make sure the data is correct and follow rules like automatically updating other related tables when changes are made.
Logon triggers are activated when a user logs into the database. These triggers are mainly used to control who can access the database or to monitor who is logging in.
In this, triggers can be categorised into two types based on their execution timing:
A Before trigger takes place before an event such as insertion, update or deletion on a table. It is often employed to alter or verify the information before the change is made in the database.
This trigger runs before a new record is added to a table. It is used to validate or modify the data before it is inserted. An example is ensuring certain fields have the correct format or values before the table is updated.
This trigger is activated before data in an existing record is updated. It allows users to check or adjust the data before it gets changed in the table. Therefore, it maintains consistency or enforces business rules.
This trigger activates before a record is deleted from a table. This trigger can be used to perform checks, such as preventing the deletion of certain data or archiving it before removal. It is necessary to ensure that critical data is not lost.
An After trigger operates only when the event in question has already been processed to its successful conclusion. This kind of trigger is valuable for events predicated upon the changes made within the event, as is the case in updating tables related to the event or logging the changes.
This trigger is activated after a new record is inserted into a table. It can be used to perform actions like updating related tables, sending notifications, or logging the insertion for audit purposes.
This trigger is activated after a record is updated. It is commonly used to update related data in other tables, perform calculations, or log the changes made during the update. It maintains the consistency of related data.
This trigger runs after a record is deleted from a table. It is useful for actions like archiving the deleted data, updating related tables, or logging the deletion event to maintain an audit trail of removed records.
Triggers can also be categorized according to the occurrence that sets them into operation. The most commonly used types in this category include:
INSERT Trigger: It is used when new records enter a table or when new data are inserted in the table. This can be used to either check or add to the data that is accepted before data insertion or retrieval.
UPDATE Trigger: This type of trigger is used when information in a table is updated. It is used for such operations as tracking the modifications in the figures or modification of the related records in other tables.
DELETE Trigger: A DELETE trigger happens at the time the records present in the particular table are deleted. It is often applied to truncate related data or as a means of archiving the deletion as well.
INSTEAD OF Trigger: This trigger substitutes pre-process SQL statement INSERT, UPDATE, or DELETE of data into or from a view with a custom action. It lets you specify concrete operations rather than the common ones for direct data manipulation in views. Thus, it provides more flexibility in comparison with the standard case.
Here's the pseudocode for the trigger program in DBMS:
BEGIN
-- Define the trigger
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
-- Trigger logic or actions to be performed
-- Example: Updating a related table, logging, validating data, etc.
IF (condition) THEN
-- Perform specific actions based on conditions
-- Example: Update a record, insert into another table, etc.
END IF;
END;
END;
Here, the pseudocode defines a trigger that automatically executes actions when a specific event, such as insert, update, or delete, occurs in the table. It specifies when the trigger should run (before or after the event) and which table it applies to. The FOR EACH ROW clause ensures the trigger runs for each affected row. Inside, you define actions like modifying data or inserting records, with the IF statement allowing conditional logic to control when certain actions are performed. This setup automates tasks, ensuring data integrity and reducing the need for manual intervention.
Now let's look at a more advanced trigger in dbms example:
BEGIN
-- Define the trigger for INSERT operations
CREATE TRIGGER before_insert_trigger
BEFORE INSERT
ON table_name
FOR EACH ROW
BEGIN
-- Validate data before insertion
IF (NEW.column_name IS NULL) THEN
-- Perform necessary action, e.g., log the error or set default value
SET NEW.column_name = 'Default Value';
END IF;
-- Log insertion activity for auditing
INSERT INTO audit_log (action, table_name, record_id, timestamp)
VALUES ('INSERT', 'table_name', NEW.id, CURRENT_TIMESTAMP);
END;
-- Define the trigger for UPDATE operations
CREATE TRIGGER after_update_trigger
AFTER UPDATE
ON table_name
FOR EACH ROW
BEGIN
-- Check if certain values were updated
IF (OLD.column_name != NEW.column_name) THEN
-- Perform related actions, such as updating a related table
UPDATE related_table
SET related_column = NEW.column_name
WHERE related_table.foreign_key = NEW.id;
END IF;
-- Log update action for auditing purposes
INSERT INTO audit_log (action, table_name, record_id, old_value, new_value, timestamp)
VALUES ('UPDATE', 'table_name', OLD.id, OLD.column_name, NEW.column_name, CURRENT_TIMESTAMP);
END;
-- Define the trigger for DELETE operations
CREATE TRIGGER before_delete_trigger
BEFORE DELETE
ON table_name
FOR EACH ROW
BEGIN
-- Prevent deletion under certain conditions
IF (OLD.column_name = 'Critical') THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Cannot delete critical data';
END IF;
-- Archive the data before deletion (optional)
INSERT INTO archive_table (id, column_name, deleted_at)
VALUES (OLD.id, OLD.column_name, CURRENT_TIMESTAMP);
END;
-- Define DDL Trigger (Data Definition Language Trigger)
CREATE TRIGGER ddl_trigger
AFTER CREATE OR ALTER OR DROP
ON SCHEMA
FOR EACH STATEMENT
BEGIN
-- Log schema changes for auditing purposes
INSERT INTO ddl_audit_log (action, object_type, object_name, timestamp)
VALUES (EVENT_NAME, OBJECT_TYPE, OBJECT_NAME, CURRENT_TIMESTAMP);
END;
-- Define Logon Trigger
CREATE TRIGGER logon_trigger
AFTER LOGON
ON DATABASE
FOR EACH SESSION
BEGIN
-- Log the user logon event
INSERT INTO user_activity_log (username, logon_time, ip_address)
VALUES (USER(), CURRENT_TIMESTAMP, SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
END;
-- Define an INSTEAD OF Trigger for views
CREATE TRIGGER instead_of_insert_trigger
INSTEAD OF INSERT
ON view_name
FOR EACH ROW
BEGIN
-- Perform custom logic for inserting data into the underlying base table
INSERT INTO base_table (column_name)
VALUES (NEW.column_name);
END;
-- Define an INSTEAD OF Trigger for views with update operation
CREATE TRIGGER instead_of_update_trigger
INSTEAD OF UPDATE
ON view_name
FOR EACH ROW
BEGIN
-- Perform custom logic for updating the underlying base table
UPDATE base_table
SET column_name = NEW.column_name
WHERE id = OLD.id;
END;
-- Define an INSTEAD OF Trigger for delete operation
CREATE TRIGGER instead_of_delete_trigger
INSTEAD OF DELETE
ON view_name
FOR EACH ROW
BEGIN
-- Perform custom logic for deleting records from the underlying base table
DELETE FROM base_table WHERE id = OLD.id;
END;
END;
In this advanced pseudocode, different types of triggers are used to handle various database operations. These include:
Consider this trigger in dbms example where we have a table named student. It contains the attributes Student_id, Name, Address and Scores
| Student_id | Name | Address | Score |
|---|---|---|---|
| 1 | John | Denver | 220 |
| 2 | Pete | Memphis | 190 |
| 3 | George | Austin | 180 |
Now, we need to set a trigger to add 100 to each new row of the score column every time a new student is inserted into the table.
The SQL trigger is as follows:
CREATE TRIGGER Add_score
BEFORE
INSERT
ON Student
FOR EACH ROW
SET new.Score = new.Score + 100;
Here, the new keyword is for the row that's getting affected
The query for inserting a new student in the database after creating the trigger:
INSERT INTO Student(Name, Address, Score) VALUES('Roger', 'Nashville', 110);
As an auto-increment field, the Student_id column will be automatically generated when a new record is inserted into the table.
To get the output of the final query:
SELECT * FROM Student;
| Student_id | Name | Address | Score |
|---|---|---|---|
| 1 | John | Denver | 220 |
| 2 | Pete | Memphis | 190 |
| 3 | George | Austin | 180 |
| 4 | Roger | Nashville | 210 |
Here are the advantages and disadvantages of database triggers in DBMS:
Triggers offer several benefits in DBMS:
Despite their advantages, triggers also have some downsides:
Here are some alternatives to triggers that can be used:
In conclusion, understanding triggers is crucial for anyone involved in database management. By exploring everything from define trigger in dbms, their types, functions, and real-world applications, we gain insight into how triggers help maintain data integrity, automate tasks, and enforce business rules. While they offer significant advantages in terms of efficiency and consistency, it's also important to be aware of their potential impact on performance and security. A solid grasp of triggers ensures more reliable and effective database management. To learn more and build your skills, join the CCBP Academy 4.0 program.
A trigger in the database is an action statement that gets activated whenever there is an event that takes place in the database, including the operations or activities carried on: Insert, update or delete on the table.
Triggers in DBMS are classified into two types: based on execution timing (BEFORE and AFTER) and based on events (INSERT, UPDATE, DELETE, and INSTEAD OF), each serving specific purposes in data handling.
Triggers and procedures are used for different purposes. Triggers are event-sensitive and run upon certain database events, and stored procedures are sequentially coded SQL on demand by users or applications.
Indexes go hand in hand with consistency, constraints, auditing, cascading, and enforcing business rules within a database.
While using triggers, we gain many advantages; they simultaneously create a certain level of complication in the database and can lead to the unexpected working of the database if not used with proper precautions. Triggers are practical in that they can be used to deter frequently changing data or to control data complexity, but repeated use of triggers or the use of complex logic within triggers can complicate data management and fixing.
Related Articles:
Source: NxtWave - CCBP Academy