SQL Triggers

Trigger is a statement that a system executes automatically when there is any modification to the database. In a trigger, we first specify when the trigger is to be executed and then the action to be performed when the trigger executes. Triggers are used to specify certain integrity constraints and referential constraints that cannot be specified using the constraint mechanism of SQL. 

Types of Triggers – 
We can define 6 types of triggers for each table: 

1. AFTER INSERT activated after data is inserted into the table. 
    
2. AFTER UPDATE: activated after data in the table is modified. 
    
3. AFTER DELETE: activated after data is deleted/removed from the table. 
    
4. BEFORE INSERT: activated before data is inserted into the table. 
    
5. BEFORE UPDATE: activated before data in the table is modified. 
    
6. BEFORE DELETE: activated before data is deleted/removed from the table. 

AFTER INSERT

In Microsoft SQL Server, you can use the AFTER INSERT trigger to achieve a similar result. Here's an example of an SQL Server trigger that inserts values into another table after a new record is inserted into a specific table:

Assuming you have two tables, employees and audit_log, and you want to insert a record into audit_log whenever a new employee is added to the employees table:

sql
-- Create the employees table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    age INT,
    salary DECIMAL(10, 2)
);

-- Create the audit_log table
CREATE TABLE audit_log (
    log_id INT PRIMARY KEY,
    action VARCHAR(50),
    employee_id INT,
    [timestamp] DATETIME
);

-- Create the trigger
CREATE TRIGGER after_employee_insert
ON employees
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO audit_log (action, employee_id, [timestamp])
    SELECT 'INSERT', employee_id, GETDATE()
    FROM inserted;
END;

In this example:

• after_employee_insert is the name of the trigger.
• ON employees specifies that the trigger is associated with the employees table.
• AFTER INSERT indicates that the trigger should execute after an INSERT operation on the employees table.
• AS BEGIN ... END encloses the body of the trigger, where the INSERT INTO audit_log statement is written.
• inserted is a special table that holds the newly inserted rows.

Whenever a new record is inserted into the employees table, the trigger will automatically insert a corresponding record into the audit_log table with the action set to 'INSERT', the employee ID, and the timestamp of the operation. Adjust the column names and data types according to your specific database schema.

AFTER UPDATE:

In SQL Server, you can create a trigger that captures both the old and new values before and after an update operation. The AFTER UPDATE trigger allows you to access both the deleted and inserted tables to retrieve the old and new values, respectively. Here's an example:

Assuming you have a table named employees:

sql
-- Create the employees table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    age INT,
    salary DECIMAL(10, 2)
);

-- Create the audit_log table
CREATE TABLE audit_log (
    log_id INT PRIMARY KEY,
    action VARCHAR(50),
    employee_id INT,
    old_first_name VARCHAR(255),
    new_first_name VARCHAR(255),
    old_last_name VARCHAR(255),
    new_last_name VARCHAR(255),
    old_age INT,
    new_age INT,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    [timestamp] DATETIME
);

-- Create the trigger
CREATE TRIGGER after_employee_update
ON employees
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO audit_log (
        action, 
        employee_id, 
        old_first_name, 
        new_first_name, 
        old_last_name, 
        new_last_name, 
        old_age, 
        new_age, 
        old_salary, 
        new_salary, 
        [timestamp]
    )
    SELECT 
        'UPDATE',
        i.employee_id,
        d.first_name AS old_first_name,
        i.first_name AS new_first_name,
        d.last_name AS old_last_name,
        i.last_name AS new_last_name,
        d.age AS old_age,
        i.age AS new_age,
        d.salary AS old_salary,
        i.salary AS new_salary,
        GETDATE()
    FROM 
        deleted d
    INNER JOIN 
        inserted i ON d.employee_id = i.employee_id;
END;

In this example:

• after_employee_update is the name of the trigger.
• ON employees specifies that the trigger is associated with the employees table.
• AFTER UPDATE indicates that the trigger should execute after an UPDATE operation on the employees table.
• deleted and inserted are special tables that hold the old and new values, respectively.

The trigger inserts a record into the audit_log table containing information about the update operation, including the old and new values for each relevant column. Adjust the column names and data types according to your specific database schema.