DB2 Triggers (Usage of DML and DDL triggers)

Triggers are a feature in DBMS used to automatically execute certain tasks. They can be triggered by specific events, such as INSERT, UPDATE or DELETE operations.

DML Triggers

DML (Data Manipulation Language) is the language used for data manipulation, including operations like INSERT, UPDATE and DELETE. DML triggers are primarily used to handle events that occur during these operations.

INSERT Trigger

An INSERT trigger is activated when data is inserted. It can execute certain tasks such as updating related tables or checking data integrity.

UPDATE Trigger

An UPDATE trigger is activated when data is updated. It can execute certain tasks, such as updating related tables or checking data integrity.

DELETE Trigger

A DELETE trigger is activated when data is deleted. It can execute certain tasks, such as updating related tables or checking data integrity.

DDL Triggers

DDL (Data Definition Language) is the language used for defining data structures, including operations like CREATE, ALTER and DROP. DDL triggers are primarily used to handle events that occur during these operations.

CREATE Trigger

A CREATE trigger is activated when a table or index is created. It can execute certain tasks such as updating related tables or checking data integrity.

ALTER Trigger

An ALTER trigger is activated when the table structure is modified. It can execute certain tasks, such as updating related tables or checking overall data consistency.

DROP Trigger

A DROP trigger is activated when a table or index is deleted. It can execute certain tasks, such as updating related tables or checking data integrity.

Trigger Configuration

Triggers can be configured in the following ways:

  • Trigger type (DML or DDL)
  • Trigger name
  • Trigger function (INSERT, UPDATE or DELETE)
  • Trigger condition (if applicable)
  • View or program trigger

Advantages of Triggers

Triggers have the following advantages:

  • Automatic Task Execution: Triggers can be activated based on specific events to automatically execute certain tasks.
  • Improved Data Integrity: Triggers can check whether data meets expected standards, ensuring consistency and reliability.
  • Reduced Manual Maintenance: Triggers reduce the need for manual maintenance as they can automate task execution.

Disadvantages of Triggers

Triggers also have the following disadvantages:

  • Performance Impact: Triggers may impact database performance because they require additional computational resources.
  • Increased Complexity: Triggers require extra maintenance and management, which may increase system complexity.