Day 13: Views, Stored Procedures, and Triggers

Day 13: Views, Stored Procedures, and Triggers
 

1. Views

        A view is a virtual table based on a SQL query. It simplifies complex queries and improves data abstraction.

        1.1. Creating a View:

            SQL

        CREATE VIEW ViewName AS
            SELECT Column1, Column2
            FROM TableName
        WHERE Condition;

        1.2. Using a View: 

            SELECT * FROM ViewName;

        1.3. Updating a View: 

            Views can often be updated if based on a single table and meet certain criteria.                   

            Example:

                    UPDATE ViewName
                    SET Column1 = 'NewValue'
                    WHERE Condition;

        1.4. Dropping a View:

            DROP VIEW ViewName;

2. Stored Procedures

        A stored procedure is a set of SQL statements stored in the database and executed as a single unit.
     

    2.1. Creating a Stored Procedure: 

        SQL

        CREATE PROCEDURE ProcedureName
            AS
        BEGIN
            SELECT * FROM TableName WHERE Condition;
        END;

    2.2. Executing a Stored Procedure:

        EXEC ProcedureName;

    2.3. Stored Procedure with Parameters:

        SQL

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

        Execution:

            EXEC GetEmployeeDetails @EmployeeID = 1;

    2.4. Dropping a Stored Procedure:

        DROP PROCEDURE ProcedureName;


3. Triggers

    Triggers are SQL code automatically executed in response to specific events on a table.

    3.1. Types of Triggers:

        AFTER Trigger: Executes after an INSERT, UPDATE, or DELETE operation.

          INSTEAD OF Trigger: Replaces the triggering action.

    3.2. Creating an AFTER Trigger:

        SQL

        CREATE TRIGGER TriggerName
        ON TableName
        AFTER INSERT, UPDATE, DELETE
        AS
        BEGIN
            PRINT 'Trigger executed';
        END;

    3.3. Example: Logging Changes:

        SQL

        CREATE TRIGGER LogChanges
        ON Employees
        AFTER UPDATE
        AS
        BEGIN
            INSERT INTO AuditLog (EmployeeID, ChangeTime)
            SELECT EmployeeID, GETDATE()
            FROM Inserted;
        END;

    4.4. Dropping a Trigger:

        DROP TRIGGER TriggerName;

4. Use Cases

1. Views: Simplify reporting or provide restricted access to data.
2. Stored Procedures: Automate repetitive tasks or enforce business logic.
3. Triggers: Automatically maintain audit trails or enforce rules.

Action Steps

1. Create a view to simplify a complex query.
2. Write a stored procedure to retrieve specific data based on a parameter.
3. Create a trigger to log changes in a table.