Triggers play a key role in database management systems by automating actions primarily based on particular events. They help preserve the database integrity and lower the manual work in managing database systems. Triggers are broadly utilised in various eventualities like logging changes, enforcing business regulations, and ensuring information integrity.
This blog will help you explore the actual usage of triggers, how they ultimately work, and the different types of triggers available in DBMS. You will also learn to create your own triggers in various database systems, along with practical examples and the best practices to avoid any common mistakes.
What is a Trigger?
A trigger is a special kind of program in a database that can automatically run when any events occur. These events commonly involve changes to the data, along with inserting, updating, or deleting data. Triggers help automate tasks that want to be finished every time a change occurs in the database, making it more efficient and consistent. Unlike any other regular database queries, triggers aren’t run manually; they get activated while the specified event takes place.
The main task of a trigger is to ensure that certain actions happen automatically whenever the database changes. For example, a trigger can be used to log any updates made to a specific table, put in the data rules and policies, or calculate new values. This helps maintain data integrity and prevents mistakes, as the database can perform checks or actions without needing extra user intervention.
Triggers are typically installed with the help of database administrators or developers and can be described as responses before or after any event takes place. The timing of the trigger is important, as it determines how the database processes the modifications. With various types available, triggers can be tailored to fit different needs and scenarios, making them versatile tools for managing databases effectively.
Here are some reasons why triggers are used in database management:
Automate repetitive tasks: Triggers can perform routine actions, like updating a timestamp or calculating totals, without requiring manual input.
Enforce business rules: They help ensure that certain rules are followed, such as not allowing a sale if stock is out.
Maintain data integrity: Triggers can check for conditions and prevent invalid data from being added or modified.
Audit changes: They can log changes to important records, creating a history of updates for security and tracking purposes.
React to data changes in real-time: Triggers can initiate actions, like sending notifications or updating related tables, as soon as changes occur.
Ensure data consistency across tables: They help in keeping related tables in sync by performing updates or checks automatically.
How Do Triggers Work in DBMS?
A trigger usually has a specific structure that defines when it should activate and what actions it should perform. The basic components include:
Trigger Name: This is the unique identifier for the trigger within the database.
Triggering Event: The event that activates the trigger, such as an INSERT, UPDATE, or DELETE operation on a specific table.
Trigger Timing: This indicates when the trigger should run in relation to the event. It can be set as BEFORE or AFTER the event occurs.
Target Table or View: The table or view where the event takes place, causing the trigger to activate.
Trigger Body: This contains the SQL statements that will be executed when the trigger is fired. It may include conditions, actions, or a combination of both.
These components work together to define how the trigger responds to changes in the database, allowing for automated tasks to be performed.
Triggers are fired automatically when the specified event occurs on the target table or view. For example, if a trigger is set to fire BEFORE INSERT on a table, it will activate whenever a new record is about to be added. The database engine detects the event and then executes the trigger according to its defined timing and conditions.
If multiple triggers are associated with the same event on the same table, they will be executed in a sequence determined by the database. Since triggers are built to run without user input, they help keep the database consistent by reacting to changes as they happen, enforcing rules, updating other tables, or logging events.
How to Create Triggers in Different DBMSs?
Here’s how to create triggers in different DBMSs with detailed explanations for each line of code. We’ll cover popular databases like SQL Server, MySQL, Oracle, and PostgreSQL.
1. Creating Triggers in SQL Server
In SQL Server, triggers are created using the CREATE TRIGGER statement. Below is an example of a trigger that fires after a record is inserted into the employees table.
Example:
CREATE TRIGGER trg_after_insert
ON employees
AFTER INSERT
AS
BEGIN
INSERT INTO employee_log (employee_id, action, log_date)
SELECT inserted.employee_id, 'Inserted', GETDATE()
FROM inserted;
END;
Explanation:
CREATE TRIGGER trg_after_insert: This creates a new trigger named trg_after_insert.
ON employees: Specifies the table where the trigger is attached, in this case, the employees table.
AFTER INSERT: Indicates that this trigger will execute after an INSERT operation.
AS BEGIN … END: The code block inside BEGIN and END defines the trigger’s actions.
INSERT INTO employee_log …: The trigger adds a new entry to the employee_log table.
SELECT inserted.employee_id, ‘Inserted’, GETDATE() FROM inserted: The inserted table holds the newly inserted rows. Here, it takes the employee_id from inserted, sets the action to ‘Inserted’, and logs the current date.
2. Creating Triggers in MySQL
MySQL uses a similar approach but has some different syntax requirements. Below is an example of a trigger that fires before updating the products table.
Example:
CREATE TRIGGER before_update_product
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
IF NEW.price < 0 THEN
SET NEW.price = 0;
END IF;
END;
Explanation:
CREATE TRIGGER before_update_product: Creates a trigger named before_update_product.
BEFORE UPDATE ON products: Indicates that this trigger will run before any UPDATE operation on the products table.
FOR EACH ROW: Specifies that the trigger will execute for each affected row in the table.
BEGIN … END: This block contains the logic of the trigger.
IF NEW.price < 0 THEN SET NEW.price = 0;: The NEW keyword represents the new values being updated. If the new price is less than zero, it sets the price to zero, ensuring no negative values are saved.
3. Creating Triggers in Oracle
Oracle’s trigger syntax is slightly different and allows for more complex trigger configurations. Here’s an example of an AFTER DELETE trigger in Oracle.
Example:
CREATE OR REPLACE TRIGGER trg_after_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO orders_audit (order_id, action, action_date)
VALUES (:OLD.order_id, 'Deleted', SYSDATE);
END;
Explanation:
CREATE OR REPLACE TRIGGER trg_after_delete: Creates a new trigger or replaces an existing one with the name trg_after_delete.
AFTER DELETE ON orders: Specifies that the trigger will execute after a DELETE operation on the orders table.
FOR EACH ROW: Ensures the trigger runs for each row affected by the delete operation.
BEGIN … END: This block contains the trigger’s action logic.
INSERT INTO orders_audit … VALUES (:OLD.order_id, ‘Deleted’, SYSDATE): The :OLD keyword refers to the old values that existed before the delete. Here, it logs the order_id, action as ‘Deleted’, and the current date using SYSDATE into the orders_audit table.
4. Creating Triggers in PostgreSQL
PostgreSQL also supports creating triggers, but you need to define a function that performs the trigger’s actions, and then link that function to a trigger.
Example: First, create a function:
CREATE OR REPLACE FUNCTION log_employee_insert()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO employee_log (employee_id, action, log_date)
VALUES (NEW.employee_id, 'Inserted', NOW());
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Then, create the trigger:
CREATE TRIGGER trg_after_insert
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION log_employee_insert();
Explanation:
Creating the function (log_employee_insert):
CREATE OR REPLACE FUNCTION log_employee_insert(): Creates a new function named log_employee_insert.
RETURNS TRIGGER: Specifies that this function is a trigger function.
AS $$ … $$ LANGUAGE plpgsql;: Defines the function’s body using the PL/pgSQL language.
Inside the function, INSERT INTO employee_log … VALUES (NEW.employee_id, ‘Inserted’, NOW()): Inserts a new record into employee_log. NEW represents the newly inserted row.
RETURN NEW;: Allows the function to complete successfully.
2. Creating the trigger (trg_after_insert):
CREATE TRIGGER trg_after_insert: Creates a new trigger named trg_after_insert.
AFTER INSERT ON employees: Specifies that this trigger fires after an insert operation on the employees table.
FOR EACH ROW: Executes for each new row added.
EXECUTE FUNCTION log_employee_insert(): Calls the log_employee_insert function when the trigger fires.
These examples show how different databases implement triggers with slight variations in syntax, but all serve to automate tasks and enforce rules in the database.
Different Trigger Types in DBMS
Here are the main types of triggers in a DBMS, along with example syntax and explanations:
1. Before Triggers
A Before Trigger runs automatically before the event (like INSERT, UPDATE, or DELETE) is carried out on a table. It is typically used to validate or modify data before it is added or changed. For example, you might use a BEFORE INSERT trigger to check if a value meets certain criteria before inserting it into a table.
Example Syntax:
CREATE TRIGGER before_insert_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF NEW.salary < 0 THEN
SET NEW.salary = 0;
END IF;
END;
In this example, the before_insert_trigger is set to run before a new row is inserted into the employees table. It checks if the salary value is less than zero, and if so, it sets the salary to zero. This ensures that no negative salary values are inserted.
2. After Triggers
An After Trigger executes automatically after the event (like INSERT, UPDATE, or DELETE) is completed. It is often used for tasks like logging changes or updating related tables. Since it runs after the event, the data has already been modified in the table.
Example Syntax:
CREATE TRIGGER after_update_trigger
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
INSERT INTO order_audit (order_id, modified_date)
VALUES (NEW.order_id, NOW());
END;
Here, the after_update_trigger fires after an update is made on the orders table. It logs the updated order’s ID and the current date into the order_audit table, creating a record of the changes.
3. Instead Of Triggers
An Instead Of Trigger is used with views rather than tables, and it runs instead of the event. This allows custom handling of events like INSERT, UPDATE, or DELETE on a view that wouldn’t normally support these operations directly.
Example Syntax:
CREATE TRIGGER instead_of_update_trigger
INSTEAD OF UPDATE ON view_sales
FOR EACH ROW
BEGIN
UPDATE sales
SET total_amount = NEW.total_amount
WHERE sale_id = OLD.sale_id;
END;
In this example, the instead_of_update_trigger allows updates to be made on the view_sales view. When an update occurs, the trigger updates the sales table with the new total_amount. This makes it possible to work with complex views that normally wouldn’t allow direct updates.
4. Row-Level vs. Statement-Level Triggers
Triggers can also be classified based on whether they execute for each affected row or once per statement.
Row-Level Trigger: Executes once for each row affected by the event. The examples given above (FOR EACH ROW) are row-level triggers, which means the trigger runs individually for each record.
Statement-Level Trigger: Executes once per SQL statement, regardless of how many rows are affected.
Example Syntax for Statement-Level Trigger:
CREATE TRIGGER after_delete_statement_trigger
AFTER DELETE ON products
BEGIN
INSERT INTO product_log (message, log_date)
VALUES ('A delete operation was performed', NOW());
END;
In this case, the after_delete_statement_trigger runs after any DELETE statement on the products table. It logs a message indicating that a delete operation was performed. It runs only once per delete statement, even if multiple rows are deleted.
These different trigger types give you flexibility in handling database changes based on the timing and scope of events, making it easier to automate tasks and enforce rules.
Let’s start by using a sample table called employees. We’ll be using this table to demonstrate various trigger examples. The table contains employee information in the following columns:
employee_id
first_name
last_name
department
salary
hire_date
1
John
Doe
HR
50000
2021-05-15
2
Jane
Smith
IT
70000
2020-08-21
3
Mark
Taylor
Sales
60000
2019-04-30
4
Lisa
Brown
Finance
75000
2018-11-12
5
David
Johnson
HR
48000
2022-03-18
6
Sarah
Miller
IT
72000
2021-09-25
7
Chris
Davis
Sales
55000
2020-06-13
8
Emily
Wilson
Finance
78000
2017-07-19
9
Michael
Garcia
HR
52000
2022-01-05
10
Sophia
Martinez
IT
68000
2019-10-08
We’ll use this table to create different trigger examples and show how they can automate tasks and maintain data consistency.
1. Example 1: Before Insert Trigger to Validate Salary
Problem Statement: We want to ensure that no employee can be added with a negative salary. If a negative salary is detected, it should be set to zero automatically before the record is inserted.
Code:
CREATE TRIGGER trg_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF NEW.salary < 0 THEN
SET NEW.salary = 0;
END IF;
END;
Explanation:
CREATE TRIGGER trg_before_insert: This creates a new trigger named trg_before_insert.
BEFORE INSERT ON employees: Specifies that the trigger will activate before an insert operation on the employees table.
FOR EACH ROW: Indicates that the trigger will execute for every new row being inserted.
BEGIN … END: Contains the trigger’s logic, which checks if the NEW.salary is less than zero. If true, it sets NEW.salary to zero to avoid storing negative values.
2. Example 2: After Update Trigger to Track Salary Changes
Problem Statement: Whenever a worker’s salary is updated, we need to log the old and new profit values along with the employee’s ID in a separate table known as salary_changes.
Code:
CREATE TABLE salary_changes (
change_id INT PRIMARY KEY AUTO_INCREMENT,
employee_id INT,
old_salary DECIMAL(10, 2),
new_salary DECIMAL(10, 2),
change_date DATETIME
);
CREATE TRIGGER trg_after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.salary <> NEW.salary THEN
INSERT INTO salary_changes (employee_id, old_salary, new_salary, change_date)
VALUES (OLD.employee_id, OLD.salary, NEW.salary, NOW());
END IF;
END;
Explanation:
CREATE TABLE salary_changes …: Creates a new table to track salary changes.
CREATE TRIGGER trg_after_salary_update: Creates a new trigger named trg_after_salary_update.
AFTER UPDATE ON employees: Indicates the trigger will fire after an update on the employees table.
FOR EACH ROW: Executes the trigger for each updated row.
IF OLD.salary <> NEW.salary: Checks if the old salary is different from the new salary.
INSERT INTO salary_changes … VALUES (…): Inserts the employee’s ID, old salary, new salary, and current timestamp (NOW()) into the salary_changes table.
3. Example 3: After Delete Trigger to Archive Deleted Employees
Problem Statement: This is the case where you delete an employee form the employees table and then you must archive the deleted record in an employee_archive table for log and history purposes.
Code:
CREATE TABLE employee_archive (
archive_id INT PRIMARY KEY AUTO_INCREMENT,
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2),
hire_date DATE,
deletion_date DATETIME
);
CREATE TRIGGER trg_after_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_archive (employee_id, first_name, last_name, department, salary, hire_date, deletion_date)
VALUES (OLD.employee_id, OLD.first_name, OLD.last_name, OLD.department, OLD.salary, OLD.hire_date, NOW());
END;
Explanation:
CREATE TABLE employee_archive …: Creates a new table to store archived employee data.
CREATE TRIGGER trg_after_delete: Creates a trigger named trg_after_delete.
AFTER DELETE ON employees: The trigger fires after a record is deleted from the employees table.
FOR EACH ROW: Ensures the trigger executes for every deleted row.
INSERT INTO employee_archive … VALUES (…): Copies the deleted employee’s data into the employee_archive table, adding the current timestamp (NOW()) as the deletion date.
4. Example 4: Before Update Trigger to Enforce Department Change Rules
Problem Statement: We need to ensure that employees cannot be moved from the “HR” department to the “Finance” department. If such a change is attempted, the trigger should prevent the update.
Code:
CREATE TRIGGER trg_before_update_department
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.department = 'HR' AND NEW.department = 'Finance' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot transfer employees directly from HR to Finance';
END IF;
END;
Explanation:
CREATE TRIGGER trg_before_update_department: Defines a trigger named trg_before_update_department.
BEFORE UPDATE ON employees: The trigger runs before an update is applied to the employees table.
FOR EACH ROW: Executes for each row being updated.
IF OLD.department = ‘HR’ AND NEW.department = ‘Finance’: Checks if the department change is from “HR” to “Finance”.
SIGNAL SQLSTATE ‘45000’ …: Raises an error with a custom message, preventing the update.
5. Example 5: After Insert Trigger to Calculate Bonus for New Employees
Problem Statement: When a new employee is added to the employees table, we want to calculate a bonus based on their salary and insert the bonus amount into a bonus table.
Code:
CREATE TABLE bonus (
bonus_id INT PRIMARY KEY AUTO_INCREMENT,
employee_id INT,
bonus_amount DECIMAL(10, 2),
calculation_date DATETIME
);
CREATE TRIGGER trg_after_insert_bonus
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
DECLARE bonus_amount DECIMAL(10, 2);
SET bonus_amount = NEW.salary * 0.1;
INSERT INTO bonus (employee_id, bonus_amount, calculation_date)
VALUES (NEW.employee_id, bonus_amount, NOW());
END;
Explanation:
CREATE TABLE bonus …: Creates a table to store employee bonus information.
CREATE TRIGGER trg_after_insert_bonus: Creates a trigger named trg_after_insert_bonus.
AFTER INSERT ON employees: Specifies the trigger will fire after a new row is inserted into the employees table.
FOR EACH ROW: Executes the trigger for each inserted row.
DECLARE bonus_amount … SET bonus_amount …: Calculates the bonus as 10% of the employee’s salary.
INSERT INTO bonus … VALUES (…): This inserts the employee’s ID, calculated bonus amount, and current date into the bonus table.
Pros and Cons of Trigger
Pros of Triggers
Automates repetitive tasks: Triggers can deal with routine tasks like updating timestamps or calculating values which will save time and reduce manual work.
Maintains data integrity: Triggers can put into effect the data rules and regulations automatically, ensuring that only legitimate information gets saved in the database.
Helps in auditing changes: Triggers can log changes to essential records, developing a history of updates for protection and compliance functions in the organisation.
Enforces business rules: They assist in making positive regulations in any organisation which are accompanied in the database, preventing automatic invalid transfers or unnecessary discounts.
Improves statistics consistency: Triggers can keep related tables in sync by automatically performing updates or deletions based on changes to the main table.
Cons of Triggers
Can affect performance: Triggers may slow down the database, especially when they contain complex logic or run on large datasets.
Hard to debug: Since triggers run automatically, it can be difficult to trace issues, especially if multiple triggers are interdependent.
Complicated maintenance: Understanding and modifying database behaviour becomes challenging when there are too many triggers.
Limited control: Once a trigger is set, it runs without user intervention, which can sometimes lead to unintended consequences.
Potential for cascading issues: Triggers that cause changes in other tables may lead to a chain reaction, complicating the database state.
Best Practices for Using Triggers
Here are some best practices to follow when using triggers:
Keep triggers simple: Avoid adding complex logic to triggers. Keep the code straightforward to minimise performance issues and make the trigger easier to understand.
Use triggers only when necessary: Don’t rely on triggers for tasks that can be handled by the application layer. Use triggers for data-related automation where they add value, such as enforcing data integrity.
Avoid cascading triggers: Cascading triggers, where one trigger causes another to fire, can lead to complex situations and hard-to-trace bugs. Minimise dependencies between triggers to avoid unexpected behaviour.
Include error handling: Ensure that triggers handle errors gracefully. Use error-handling techniques to manage issues and provide useful feedback when an error occurs.
Document trigger functionality: Clearly document what each trigger does, including its purpose and any conditions it checks. This helps in understanding and maintaining the trigger in the future.
Test thoroughly before deployment: Test triggers in a development environment to ensure they behave as expected. Verify their performance and make sure they don’t cause unwanted side effects.
Monitor trigger performance: Keep an eye on the impact of triggers on database performance. Adjust trigger logic or indexing if you notice any slowdown.
Common Mistakes to Avoid When Using Triggers
Here are some common mistakes to avoid when using triggers:
Overusing triggers for complex logic: Triggers are not meant for heavy processing. Using them for complex calculations or workflows can slow down the database and make maintenance difficult.
Ignoring performance impact: Triggers can affect database performance, especially with large datasets or frequent data changes. Always consider the impact and optimise trigger logic to minimise delays.
Creating too many triggers on the same table: Having multiple triggers on the same table can lead to unpredictable behaviour, making debugging and maintenance harder. Limit the number of triggers and consolidate them when possible.
Not handling errors properly: If errors occur in a trigger and aren’t handled, they can cause the triggering operation (like an insert or update) to fail, leading to data inconsistencies.
Using triggers for tasks better suited to the application layer: Some tasks, like complex business rules or data transformations, are better handled outside the database. Keep trigger use focused on database-specific tasks.
Allowing cascading triggers: When one trigger activates another, it can lead to cascading changes that are difficult to track and troubleshoot. Avoid setting up triggers that indirectly trigger others.
Failing to document triggers: Without documentation, understanding the purpose and logic behind a trigger becomes challenging, especially for new developers or future maintenance.
Conclusion
Triggers in a DBMS play a vital role in automating tasks, enforcing rules, and maintaining data integrity. They respond to changes in the database by executing specified actions automatically, saving time and reducing manual effort. By the usage of triggers wisely, you can make sure that your database remains consistent and efficient while handling additional tasks like logging, auditing, and data validation.
However, it’s vital to follow best practices and avoid creating common mistakes to keep your database safe and its performance will be optimised. Triggers should be simple, nicely documented, and used for database-specific tasks. When implemented efficiently, triggers may be highly effective in enhancing the capability of your database management system. Want to explore DBMS in-depth? Try Hero Vired’s Accelerator Program in Business Analytics and Data Science.
FAQs
What is a trigger in DBMS?
A trigger is a program that automatically runs when specific events occur in a database.
When should triggers be used?
Triggers are best used for tasks like data validation, auditing, and enforcing rules within the database.
Can triggers slow down the database?
Yes, complex triggers can affect performance, especially when dealing with large datasets.
What is the difference between before and after triggers?
Before triggers run before an event, while triggers execute after the event has occurred.
Can triggers be used with views?
Yes, some databases support "Instead Of" triggers that can be used with views.
What are cascading triggers?
Cascading triggers occur when one trigger activates another, causing a chain reaction of events.
How can errors in triggers be managed?
Error handling can be included in the trigger code to manage and report any issues gracefully.
Deploying Applications Over the Cloud Using Jenkins
Hero Vired is a leading LearnTech company dedicated to offering cutting-edge programs in collaboration with top-tier global institutions. As part of the esteemed Hero Group, we are committed to revolutionizing the skill development landscape in India. Our programs, delivered by industry experts, are designed to empower professionals and students with the skills they need to thrive in today’s competitive job market.
