14. Triggers & Audit Logging

How Triggers Work

When SQL Server executes an INSERT, UPDATE, or DELETE on a table that has a trigger, the trigger fires as part of the same transaction. The triggering statement and the trigger's code succeed or fail together — you cannot have one without the other.

Inside a trigger's code, two special virtual tables are available: INSERTED and DELETED. These contain the row data involved in the triggering operation.

For an INSERT: the INSERTED table contains the newly added rows. DELETED is empty.

For a DELETE: the DELETED table contains the rows that were just removed. INSERTED is empty.

For an UPDATE: the DELETED table contains the rows as they were before the update. The INSERTED table contains the rows as they are after the update. This is how you compare old values to new values inside a trigger.

Creating an Audit Log Table

Before writing the trigger, create the audit table it will write into. Open a New Query in SSMS while connected to VoidX_Academy:

CREATE TABLE StudentGradeAudit (
  AuditID INT IDENTITY(1,1) PRIMARY KEY,
  StudentID INT,
  ChangedBy VARCHAR(100) DEFAULT SYSTEM_USER,
  ChangeDate DATETIME DEFAULT GETDATE(),
  OldGPA DECIMAL(3,2),
  NewGPA DECIMAL(3,2)
);

This table records who changed what and when. SYSTEM_USER is a SQL Server built-in function that returns the current database login name. GETDATE() returns the current date and time. Both defaults fill in automatically whenever a row is inserted — no application code is needed to supply them.

Creating an AFTER UPDATE Trigger

Now create a trigger that fires after any UPDATE on the Student table. It will record the old and new GPA values into the audit table:

CREATE TRIGGER TR_Student_GPA_Audit
ON Student
AFTER UPDATE
AS
BEGIN
  INSERT INTO StudentGradeAudit (StudentID, OldGPA, NewGPA)
  SELECT
    i.StudentID,
    d.GPA AS OldGPA,
    i.GPA AS NewGPA
  FROM INSERTED AS i
  INNER JOIN DELETED AS d ON i.StudentID = d.StudentID
  WHERE i.GPA <> d.GPA;
END;

Read this carefully. ON Student tells SQL Server which table this trigger watches. AFTER UPDATE means it fires after any UPDATE statement completes on that table. Inside the body, a SELECT joins the INSERTED and DELETED virtual tables — pairing each updated row's new values (from INSERTED) with its old values (from DELETED). The WHERE clause ensures only rows where GPA actually changed produce an audit record, avoiding noise from updates that touched other columns.

Press F5 to create the trigger. It appears in Object Explorer under your Student table, inside the Triggers folder.

Testing the Trigger

Run an UPDATE that changes a student's GPA and then verify the audit record was captured automatically:

UPDATE Student
SET GPA = 3.75
WHERE StudentID = 1;

Now query the audit table — you did not insert anything into it manually. The trigger did it for you:

SELECT
  AuditID,
  StudentID,
  ChangedBy,
  ChangeDate,
  OldGPA,
  NewGPA
FROM StudentGradeAudit;

You will see one row showing the StudentID, who made the change (your Windows login), the exact timestamp, the old GPA value, and the new GPA value. Every subsequent GPA update generates another row, building a complete history of every change ever made.

An AFTER INSERT Trigger

Triggers can also watch for INSERT operations. This trigger logs every new student registration:

CREATE TABLE RegistrationLog (
  LogID INT IDENTITY(1,1) PRIMARY KEY,
  StudentID INT,
  RegisteredAt DATETIME DEFAULT GETDATE(),
  RegisteredBy VARCHAR(100) DEFAULT SYSTEM_USER
);

CREATE TRIGGER TR_Student_Registration
ON Student
AFTER INSERT
AS
BEGIN
  INSERT INTO RegistrationLog (StudentID)
  SELECT StudentID FROM INSERTED;
END;

Insert a new student and the trigger fires automatically, recording the registration in the log table without any additional application code.

Viewing and Managing Triggers

To see all triggers on a table, expand the table in Object Explorer and open the Triggers folder. You can also query the system catalog:

SELECT
  name AS TriggerName,
  type_desc AS TriggerType,
  is_disabled
FROM sys.triggers
WHERE parent_id = OBJECT_ID('Student');

To temporarily disable a trigger without deleting it:

DISABLE TRIGGER TR_Student_GPA_Audit ON Student;

To re-enable it:

ENABLE TRIGGER TR_Student_GPA_Audit ON Student;

To permanently remove a trigger:

DROP TRIGGER TR_Student_GPA_Audit;

How to Use the Trigger Audit Lab

The lab shows you a live Operators table and an Audit_Log table side by side. You write a trigger in the SQL editor on the right, then click Compile Trigger. Once the trigger is compiled, a Simulate Unauthorized Update button becomes active on the left panel.

When you click that button, the Operators table flashes red showing a suspicious XP change. A fraction of a second later, a new row appears in the Audit_Log table — your trigger caught the change and recorded it automatically.

Here is the pattern the lab expects, using a student scenario as a reference:

CREATE TRIGGER LogGPAChanges
  AFTER UPDATE ON Students
  FOR EACH ROW
  EXECUTE FUNCTION log_gpa_change();

Apply this same structure. The trigger name must be LogXPChanges. It must fire AFTER UPDATE on the Operators table. It must execute the function log_xp_change(). The lab checks all four elements independently and gives specific error messages identifying exactly which part of your syntax needs correction.