Trigger in DBMS

Last Updated on March 22, 2023 by Prepbytes

A database management system (DBMS) is a software application that is used to create and manage databases. To perform any kind of operations in the database we use trigger in dbms. In this article, we will discuss triggers in more detail.

What is the Trigger in DBMS

Trigger in DBMS is a special type of stored procedure that is automatically executed in response to certain database events such as an INSERT, UPDATE, or DELETE operation. Triggers can be used to perform actions such as data validation, enforcing business rules, or logging. They can be defined to execute before or after the triggering event and can be defined to execute for every row or once for each statement. Triggers are a powerful feature of dbms that allow developers to define automatic actions based on database events.

Types of Trigger in DBMS on Basis of their Execution

Triggers in Dbms can be classified into two types based on when they are executed:

• BEFORE Trigger: A BEFORE trigger is executed before the triggering event (such as an insert, update, or delete) is executed on the table. This type of trigger is often used to modify the data before it is inserted, updated, or deleted.
• AFTER Trigger: An AFTER trigger is executed after the triggering event has been executed on the table. This type of trigger is often used to perform actions that depend on the changes made to the table due to the triggering event.

Types of Trigger in DBMS on Basis of Event or Action Triggering Them

Triggers can also be classified based on the event or action that triggers them. The most commonly used types of triggers based on this classification are:

• INSERT Trigger: This type of trigger occurs when new data is inserted into a table.
• UPDATE Trigger: This type of trigger occurs when existing data in a table is updated.
• DELETE Trigger: This type of trigger occurs when data is deleted from a table.
• INSTEAD OF Trigger: This type of trigger is used to replace the regular insert, update, or delete operation on a view with a custom operation. It is fired instead of the regular operation.

Creating a Trigger in DBMS:

Let us create a trigger in DBMS:

CREATE TRIGGER trigger_name
[BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON table_name
[FOR EACH ROW]
BEGIN
  -- code to be executed when the trigger is activated
END;

Let’s break down each part of this code:

• CREATE TRIGGER: This is the SQL command used to create a trigger.
• trigger_name: This is the name of the trigger. You can choose any name you like, as long as it follows the naming conventions of your DBMS.
• [BEFORE|AFTER]: This specifies whether the trigger should be activated before or after the triggering event.
• [INSERT|UPDATE|DELETE]: This specifies the event that will trigger the execution of the trigger. You can choose one or more of these options depending on the specific trigger you want to create.
• ON table_name: This specifies the table that the trigger will be associated with.
• [FOR EACH ROW]: This specifies that the trigger will be executed once for each row that is affected by the triggering event.
• BEGIN and END: This is where you will put the code that should be executed when the trigger is activated. The code inside the BEGIN and END blocks can be any SQL statements that you want to execute when the trigger is activated. The exact syntax and functionality of triggers can vary depending on the specific DBMS you are using.

Example of Trigger in DBMS:

Suppose we have a table named "employees" with columns "id", "name", "age", and "salary". We want to ensure that whenever a new employee is added to the table, the age of the employee must be greater than or equal to 18. We can create a BEFORE INSERT trigger to enforce this rule. Here’s how the trigger might look:

Code of Trigger in DBMS:

CREATE TRIGGER age_check
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.age < 18 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Employees must be at least 18 years old';
    END IF;
END;

Explanation of Trigger in DBMS:
This trigger will be fired before any new row is inserted into the "employees" table. It checks the age of the new employee being inserted, and if it is less than 18, it raises an error message using the SIGNAL statement, which prevents the insertion of the new row. This trigger ensures that the age constraint is enforced automatically every time a new employee is added to the table, reducing the chances of data inconsistency and improving data quality.

Advantages of Trigger in DBMS:

Some of the advantages of trigger in DBMS:

• They can help ensure that data remains consistent and accurate by enforcing rules and constraints on the data.
• These can simplify the implementation of complex business logic that requires multiple steps or queries to be executed.
• It can be used to log changes to the database, providing an audit trail that can be used for compliance, debugging, or analysis purposes.
• Triggers can automate tasks that would otherwise require manual intervention, improving the efficiency and accuracy of database operations.

Disadvantages of Trigger in DBMS:

Some of the disadvantages of trigger in DBMS:

• Triggers in DBMS can impact database performance, especially when they involve complex operations or are triggered frequently.
• They can introduce hidden logic into the database, which can make it more difficult to understand and maintain.
• These can make it harder to debug issues in the database because they are executed automatically and can be difficult to trace.
• It can introduce security vulnerabilities if they are not properly designed or tested, potentially allowing unauthorized access or modification of data.

Conclusion:
In this article, we have learned about Trigger in dbms are automatic actions that are executed in response to specific database events. They can enforce business rules, validate data, or perform auditing tasks. It is important to use triggers carefully, as they can impact database performance and cause unintended consequences if not designed and tested properly.

Frequently Asked Questions(FAQS):

1. What is a trigger in DBMS?
Ans: A trigger is a special type of stored procedure that is automatically executed in response to a specific event or action that occurs in a database. Triggers are used to enforce business rules, perform complex validations, and automate certain tasks in a database.

2. What are the different types of trigger in DBMS?
Ans: There are two types of triggers in DBMS: Before Triggers and After Triggers. Before Triggers are executed before the triggering statement is executed, whereas After Triggers are executed after the triggering statement has been executed.

3. What are the advantages of using trigger in DBMS?
Ans: Triggers can be used to enforce business rules and data integrity constraints, automate certain tasks, and provide an audit trail of changes made to a database. They can also improve performance by reducing the need for complex queries and reducing the amount of code that needs to be written.

4. What are some common uses for trigger in DBMS?
Ans: Triggers can be used for a variety of purposes, including enforcing referential integrity constraints, validating input data, generating automatic values for fields, and auditing changes to a database.

5. What are some best practices for using trigger in DBMS?
Ans: It's important to use triggers judiciously, as they can add complexity to a database and can be difficult to debug. Some best practices include limiting the number of triggers in a database, carefully testing and validating triggers before deployment, and documenting the purpose and behavior of each trigger.