SQL Server Trigger Basic Overview

Trigger in SQL

Trigger in sql server is a special type of stored procedure ,trigger is an event which will be fired automatically when we execute commands. trigger is used to monitor user activities and store the information.
A trigger is invoked automatically when a data modification event occurs against a table, which is the primary difference between a trigger and a procedure. Conversely, one needs to call a stored process directly. When a row is added to a certain table or when particular table columns are modified, for instance, a trigger may be invoked. In simple words, A trigger, to put it simply, is a group of specific-named SQL statements kept in system RAM.
When we use triggers?
Triggers are particularly useful when you need to automatically execute specific actions in response to events on a database table, ensuring that certain processes happen consistently without requiring manual intervention.
Scenarios Where Triggers are Helpful
Automatic Execution: Automates tasks in response to events like data insertions, updates, or deletions, ensuring actions are consistently taken without manual intervention.

Audit Trails: Logs changes automatically, tracking who made changes, when, and what was altered, aiding in compliance and security.

Data Synchronization: Keeps related tables in sync, such as updating inventory after a sale, ensuring data consistency.

Enforcing Business Rules: Enforces complex rules or data integrity, like preventing negative values or automatically adjusting invalid data.

Change Monitoring: Monitors and logs specific changes (e.g., salary updates), inserting details into a separate table for historical tracking.

Types of Triggers:

1.DML Triggers : DML Trigger stand for Data manipulation language commands like insert,delet update
2.DDL Triggers :Data Definition Language command like alter,drop,create

sql server support after triggers only i.e after insert,after update

CREATE TRIGGER trigger_name
ON table_name
AFTER {INSERT | UPDATE | DELETE}
AS
BEGIN
-- Trigger body: SQL statements to be executed
END;
Key Components:

trigger_name: Name of the trigger.
ON table_name: Specifies the table the trigger is associated with.
AFTER {INSERT | UPDATE | DELETE}: Defines the event(s) that fire the trigger. SQL Server uses AFTER (default) or INSTEAD OF.
    AFTER: The trigger is executed after the event.
    INSTEAD OF: The trigger is executed instead of the actual event.
AS: Begins the trigger body where SQL statements are executed.
Trigger Body: Contains the logic to execute when the trigger is fired.

DML Triggers: DML stand for Data manipulation Language like INSERT, UPDATE, and DELETE on tables or views.
Example:

This DML trigger logs updates made to the employees table into an audit table after an UPDATE operation:

Steps to Set Up a Trigger to Fire on Update:
In this step we need to ste up process how we can create a tble and procedure and check process

Create a Table: we need to Define the table on which the trigger will be set up.
Create the Trigger: we need to Define an AFTER UPDATE or INSTEAD OF UPDATE trigger on the table.
Test the Trigger: Perform an UPDATE operation to verify that the trigger fires.

Step1:

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
position VARCHAR(50),
salary DECIMAL(10, 2)
);

Step 2:

Create other table
CREATE TABLE employee_audit (
audit_id INT IDENTITY(1,1) PRIMARY KEY,
employee_id INT,
old_salary DECIMAL(10, 2),
new_salary DECIMAL(10, 2),
update_date DATETIME
);

Create a Trigger

CREATE TRIGGER trgAfterEmployeeUpdate
ON employees
AFTER UPDATE
AS
BEGIN
-- Insert old and new salary details into the audit table
INSERT INTO employee_audit (employee_id, old_salary, new_salary, update_date)
SELECT
deleted.employee_id,
deleted.salary AS old_salary,
inserted.salary AS new_salary,
GETDATE() AS update_date
FROM inserted
JOIN deleted ON inserted.employee_id = deleted.employee_id;
END;

Step 4: Check and verify

Insert an employee
INSERT INTO employees (employee_id, name, position, salary)
VALUES (1, ‘John Doe’, ‘Manager’, 60000);

Update the salary of the employee
UPDATE employees
SET salary = 65000
WHERE employee_id = 1;

Verify Trigger:
SELECT * FROM employee_audit;

DDL Trigger (Data Definition Language) DDL triggers are fired in response to DDL events like CREATE, ALTER, and DROP commands. These triggers are often used to enforce policies on database schema changes.

CREATE TRIGGER trgPreventDropTable
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
PRINT ‘You are not allowed to drop tables in this database!’;
ROLLBACK;
END;

Explanation: This trigger is created on the DATABASE level and fires when someone attempts to drop a table. It prints a message and rolls back the DROP TABLE action, effectively preventing the table from being dropped.

Real time Use case:

Audit Logging:

Use Case: Consistently record modifications to private information like salary or personal details.
Trigger: An AFTER UPDATE trigger records old and new values in an audit table when salary or employee details are updated.

Maintaining Data Consistency

Use Case: Update inventory levels in response to orders, for example, to ensure data consistency across linked tables.
Trigger: An AFTER INSERT or AFTER DELETE trigger adjusts the stock in the inventory table when sales or returns are recorded in the orders table.

Preventing Unauthorized Changes

Use Case: Prevent unauthorized DELETE operations on critical tables, such as financial data.
Trigger: An INSTEAD OF DELETE trigger blocks any DELETE operation and raises an error if the user is not authorized.