Trigger in DBMS

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. 

Example of how the trigger works: 

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.

1. DDL Triggers

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. 

2. DML Triggers

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.

3. Logon Triggers

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.

Trigger Based on Execution 

In this, triggers can be categorised into two types based on their execution timing:

BEFORE Trigger: 

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.

BEFORE INSERT Trigger

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.

BEFORE UPDATE Trigger

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.

BEFORE DELETE Trigger

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.

AFTER Trigger: 

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.

AFTER INSERT Trigger

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.

AFTER UPDATE Trigger

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.

AFTER DELETE Trigger

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.

Types of Trigger in DBMS on the basis of Event or Action

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:

• BEFORE and AFTER triggers: are fired before or after events like inserting, updating, or deleting data in a table.
• DDL triggers: are used to track changes in the database schema, such as creating or modifying tables.
• Logon triggers: monitor when a user logs into the database.‍
• INSTEAD OF triggers: replace standard operations on views with custom actions. These triggers help automate tasks and ensure data consistency across the database.

Consider this trigger in dbms example where we have a table named student. It contains the attributes Student_id, Name, Address and Scores

Student

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

• Keep it Simple: Avoid complex logic in triggers to maintain performance and readability.
• Use Triggers for Data Integrity: Ensure consistency by using PL SQL triggers in DBMS to enforce business rules and constraints.
• Minimise Trigger Usage: Limit the number of triggers to avoid unnecessary overhead and performance issues.
• Avoid Recursive Triggers: Prevent triggers from calling themselves, as it can lead to infinite loops or excessive resource consumption.
• Log Trigger Activity: Implement logging within PL SQL triggers in DBMS to track actions for debugging and auditing purposes.‍
• Test Thoroughly: Test triggers in different scenarios to ensure they function as intended without unintended effects.

Here are the advantages and disadvantages of database triggers in DBMS: 

Advantages of Triggers in DBMS

Triggers offer several benefits in DBMS:

• They ensure data consistency and accuracy by enforcing rules and constraints automatically.
• They simplify complex business logic by handling multiple steps or queries in a single operation.
• Triggers can log database changes, creating an audit trail for compliance, debugging, or analysis.
• They automate tasks, reducing manual intervention and improving efficiency and accuracy in database operations.

Disadvantages of Triggers in DBMS

Despite their advantages, triggers also have some downsides:

• Triggers can negatively impact database performance, particularly when dealing with complex operations or frequent events.
• They may introduce hidden logic, making the database harder to understand and maintain.
• Debugging can become challenging, as triggers run automatically and can be difficult to trace.
• If not properly designed or tested, triggers can create security risks, potentially allowing unauthorised access or data modification.

Here are some alternatives to triggers that can be used: 

1. Stored Procedures: These are used for complex logic, and manual execution can be used for more control over when actions occur.
2. Application-Level Logic: Implement business rules in the application code to avoid database overhead.
3. Views with INSTEAD OF Triggers: Replace standard operations on views with custom actions. These offer flexibility without traditional triggers.
4. Event-Based Scheduling: Utilize scheduled jobs (e.g., cron jobs) to perform tasks at specific intervals.
5. Constraints: Use primary, foreign key, and check constraints to enforce rules at the database level without triggers.
6. Computed Columns: Use computed columns to automatically calculate and store derived values.

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. 

1. What is the meaning of trigger in the database?

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. 

2. What kind of triggers exist in DBMS?

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. 

3. What makes triggers distinct from procedures?

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.

4. What are the use cases of triggers?

Indexes go hand in hand with consistency, constraints, auditing, cascading, and enforcing business rules within a database.

5. With triggers, what are the potential limitations?

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.