Question

State the use of database trigger and also list types of trigger.

Answer 1

Of course. Here is a clear explanation of the use of database triggers and a list of their different types.

---

The Use of a Database Trigger

A database trigger is a special type of stored procedure that automatically executes (or "fires") in response to a specific event occurring on a database table or view. The event is typically a Data Manipulation Language (DML) statement, such as an INSERT, UPDATE, or DELETE.

The primary use of a trigger is to enforce complex business rules, maintain data integrity, and automate actions that cannot be handled by standard database constraints (like PRIMARY KEY, FOREIGN KEY, or CHECK).

Think of a trigger as a "listen-and-respond" mechanism. It "listens" for a specific action on a table, and when that action happens, it automatically "responds" by executing a predefined block of code.

Key Uses and Scenarios:

1. Auditing and Logging Changes:
   * This is one of the most common uses. A trigger can automatically record who changed what data and when. For example, a trigger on an Employees table can insert a record into an Audit_Log table every time an employee's salary is updated.

2. Enforcing Complex Business Rules:
   * Some business rules are too complex for a simple CHECK constraint. For example, "A customer's credit limit cannot be increased by more than 50% in a single update." A trigger can inspect the old and new values and prevent the update if the rule is violated.

3. Maintaining Data Integrity and Synchronization:
   * Triggers can be used to keep redundant or summary data in sync. For example, when a new order is inserted into an Orders table, a trigger can automatically update a TotalSales column in a related Customers table.

4. Preventing Invalid Operations:
   * A trigger can be used to prevent certain operations from happening under specific conditions. For example, preventing a user from deleting a critical record or making updates outside of normal business hours.

5. Automating Actions:
   * A trigger can automatically perform a follow-up action. For example, when a new employee is added to the Employees table, a trigger could automatically generate a default email account and add them to a "New Hires" group.

---

Types of Triggers

Triggers can be classified based on several criteria: the event that fires them, the level at which they operate, and when they fire in relation to the event.

1. Based on the Triggering Event (DML Triggers)

These are the most common types of triggers and are tied to DML operations.
INSERT Trigger: Fires when a new row is inserted into a table.
UPDATE Trigger: Fires when an existing row is modified in a table.
* DELETE Trigger: Fires when a row is deleted from a table.

You can also combine these, for example, creating a trigger that fires ON INSERT OR UPDATE.

2. Based on the Level of Action (Row-Level vs. Statement-Level)

This determines how many times the trigger fires for a given DML statement.

• Row-Level Trigger:
  This trigger fires once for each individual row that is affected by the DML statement.
  It is specified using the FOR EACH ROW clause.
  It has access to both the old and new values of the row being changed (using special qualifiers like :OLD and :NEW in Oracle or inserted and deleted tables in SQL Server). This is essential for auditing and comparing values.
  Use Case: Logging every single salary change.

• Statement-Level Trigger:
  This trigger fires only once for the entire DML statement, regardless of how many rows are affected.
  This is the default behavior if FOR EACH ROW is not specified.
  It does not have access to the old and new values of individual rows.
  Use Case: Performing an action after a bulk data load, like sending a notification that "The Orders table has been updated."

3. Based on the Timing of Execution

This determines whether the trigger fires before or after the DML event.

• BEFORE Trigger:
  The trigger code executes before the DML operation (the INSERT, UPDATE, or DELETE) is actually performed on the table.
  Use Case:

  *   **Validation:** To check and validate data *before* it is written to the table. You can modify the data being inserted/updated or even cancel the entire operation by raising an error.
  *   **Default Values:** To automatically populate column values, like setting a `created_at` timestamp.
  

• AFTER Trigger:
  The trigger code executes after the DML operation has been completed and all constraints have been checked.
  Use Case:

  *   **Auditing:** To log the change after it has successfully occurred.
  *   **Cascading Actions:** To perform follow-up actions on other tables (like updating a summary table) after the primary change is confirmed.
  

Other Types of Triggers (Less Common)

Beyond DML triggers, some database systems support other types:
DDL Triggers: Fire in response to Data Definition Language events like CREATE, ALTER, or DROP. Used for auditing schema changes.
Logon/Logoff Triggers: Fire when a user connects to or disconnects from the database. Used for monitoring user sessions.
INSTEAD OF Triggers: These are special triggers that can be defined on views (which cannot have regular DML triggers). They execute instead of* the original DML statement, allowing you to define custom logic for updating complex, non-updatable views.