1. SQL Triggers

What is an SQL Trigger?

A trigger is a special type of stored procedure that automatically executes in response to certain events on a table or a view. Triggers are primarily used for enforcing business rules, maintaining data integrity, and automating system tasks.

Types of Triggers in SQL Server

1. DML Triggers (Data Manipulation Language Triggers)

These triggers are fired when an INSERT, UPDATE, or DELETE operation occurs on a table.

• AFTER Trigger (FOR Trigger): Executes after the triggering action is completed.
• INSTEAD OF Trigger: Executes before the triggering action and can replace the action.

2. DDL Triggers (Data Definition Language Triggers)

These triggers fire when schema-related events occur, such as CREATE, ALTER, or DROP operations.

3. Logon Triggers

These triggers execute when a user logs into the SQL Server instance. They are useful for security and auditing purposes.

Example of a DML AFTER Trigger

CREATE TRIGGER trg_AfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO EmployeeAudit(EmployeeID, Action, ActionDate)
    SELECT ID, 'INSERT', GETDATE() FROM inserted;
END;

📌 Explanation:
Whenever a new record is inserted into the Employees table, an entry is logged into the EmployeeAudit table.

Example of an INSTEAD OF Trigger

CREATE TRIGGER trg_InsteadOfDelete
ON Employees
INSTEAD OF DELETE
AS
BEGIN
    PRINT 'Deletion is not allowed on this table!';
END;

📌 Explanation:
If someone tries to delete a record from the Employees table, the trigger will block the action and display a message instead.

---

2. SQL Stored Procedures

What is a Stored Procedure?

A stored procedure is a reusable set of SQL statements that can be executed as a unit. Stored procedures improve performance, security, and maintainability of database operations.

Advantages of Stored Procedures

✅ Improved Performance – Precompiled SQL statements reduce execution time.
✅ Code Reusability – Can be used multiple times in different parts of an application.
✅ Security – Permissions can be granted at the stored procedure level instead of table level.
✅ Transaction Management – Allows complex operations with BEGIN TRANSACTION and ROLLBACK features.

Basic Example of a Stored Procedure

CREATE PROCEDURE GetEmployeeDetails
    @EmpID INT
AS
BEGIN
    SELECT * FROM Employees WHERE ID = @EmpID;
END;

📌 Usage:

EXEC GetEmployeeDetails @EmpID = 101;

Stored Procedure with Multiple Parameters

CREATE PROCEDURE UpdateEmployeeSalary
    @EmpID INT,
    @NewSalary DECIMAL(10,2)
AS
BEGIN
    UPDATE Employees
    SET Salary = @NewSalary
    WHERE ID = @EmpID;
END;

📌 Usage:

EXEC UpdateEmployeeSalary @EmpID = 105, @NewSalary = 75000.00;

Stored Procedure with Transaction Handling

CREATE PROCEDURE TransferFunds
    @SenderID INT,
    @ReceiverID INT,
    @Amount DECIMAL(10,2)
AS
BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY
        -- Deduct from sender
        UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderID;

        -- Add to receiver
        UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        PRINT 'Transaction failed!';
    END CATCH;
END;

📌 Usage:

EXEC TransferFunds @SenderID = 101, @ReceiverID = 202, @Amount = 500.00;

📌 Explanation:
If an error occurs during the transaction, the rollback ensures data consistency.

---

3. SQL Views

What is a View?

A view is a virtual table that is derived from one or more tables but does not store data itself. Views help in simplifying complex queries, enforcing security, and improving performance.

Types of Views

1. Simple View

A view based on a single table without aggregates or joins.

2. Complex View

A view that combines multiple tables using JOIN, aggregates, and functions.

3. Indexed View (Materialized View in some DBMS)

A view that physically stores the result set and improves performance for large datasets.

Basic Example of a View

CREATE VIEW EmployeeView AS
SELECT ID, Name, Department, Salary FROM Employees;

📌 Usage:

SELECT * FROM EmployeeView;

📌 Explanation:
This view helps users query employee details without accessing the main Employees table directly.

Example of a Complex View

CREATE VIEW EmployeeDepartmentView AS
SELECT e.ID, e.Name, d.DepartmentName, e.Salary
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.ID;

📌 Usage:

SELECT * FROM EmployeeDepartmentView WHERE DepartmentName = 'HR';

📌 Explanation:
This view joins two tables (Employees and Departments) to present a meaningful dataset.

Example of an Indexed View

CREATE VIEW TotalSalesView WITH SCHEMABINDING AS
SELECT SalesPersonID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY SalesPersonID;
GO
CREATE UNIQUE CLUSTERED INDEX IDX_TotalSales ON TotalSalesView(SalesPersonID);

📌 Explanation:

• The WITH SCHEMABINDING option ensures the underlying tables cannot be modified without altering the view first.
• The indexed view improves query performance for aggregate calculations.

---

Conclusion

📌 Triggers are automatic event-based stored procedures useful for maintaining data integrity.
📌 Stored Procedures are precompiled sets of SQL statements that enhance performance and security.
📌 Views provide a simplified and secure way to access complex data structures.

---