7.1 Creating Triggers

 A trigger is a set of instructions that are automatically executed (or "triggered") in response to specific events on a particular table or view. Triggers are powerful database objects that can be used for enforcing business rules, maintaining data integrity, and automating tasks.

7.1.1 Syntax for Creating a Trigger

CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT, UPDATE, DELETE -- Event(s) that trigger the execution
AS
BEGIN
    -- Trigger logic here
END;

7.1.2 Example: Creating an After Insert Trigger

Let's create a simple trigger that logs information whenever a new record is inserted into the "Employees" table.

-- Step 1: Create the Employees table for demonstration
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    Salary DECIMAL(10, 2)
);
-- Step 2: Create the Trigger
CREATE TRIGGER trgAfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO AuditLog (Action, TableName, DateTime)
    VALUES ('INSERT', 'Employees', GETDATE());
END;

7.1.3 Testing the Trigger

Now, let's insert a new record into the "Employees" table and observe the trigger's effect.

-- Inserting a new record
INSERT INTO Employees (EmployeeID, EmployeeName, Salary)
VALUES (1, 'John Doe', 50000.00);
-- Querying the AuditLog table to see the trigger result
SELECT * FROM AuditLog;

Trigger Test Output:

AuditID	Action	TableName	DateTime
1	INSERT	Employees	[Timestamp of Insert]

In this example, the trigger (trgAfterInsert) logs an entry into the AuditLog table whenever a new record is inserted into the Employees table.

7.1.4 Example: Creating an After Update Trigger

Now, let's create a trigger that updates the LastModified column whenever a record in the Employees table is updated.

-- Step 3: Alter the Employees table to include the LastModified column
ALTER TABLE Employees
ADD LastModified DATETIME;
-- Step 4: Create the Update Trigger
CREATE TRIGGER trgAfterUpdate
ON Employees
AFTER UPDATE
AS
BEGIN
    UPDATE Employees
    SET LastModified = GETDATE()
    FROM Employees e
    INNER JOIN inserted i ON e.EmployeeID = i.EmployeeID;
END;

7.1.5 Testing the Update Trigger

Now, let's update a record in the Employees table and check the result.

-- Updating a record
UPDATE Employees
SET Salary = 55000.00
WHERE EmployeeID = 1;
-- Querying the Employees table to see the LastModified column
SELECT * FROM Employees;

Update Trigger Test Output:

EmployeeID	EmployeeName	Salary	LastModified
1	John Doe	55000.00	[Timestamp of Update]

In this example, the trigger (trgAfterUpdate) updates the LastModified column whenever the Salary column is updated in the Employees table.

---

Feel free to customize the examples based on your specific use case and table structure. This tutorial provides a basic understanding of how to create triggers and includes test outputs in a tabular format for better readability.