DDL and Logon Triggers in SQL Server: Auditing Schema Changes and Login Restrictions

Beyond DML, SQL Server triggers can fire on schema changes (CREATE/ALTER/DROP — Fundamentals Chapter 2’s DDL statements) and even login attempts — powerful, and in the case of logon triggers, genuinely risky if you get it wrong, in a way DML triggers never are.

DDL Trigger: Auditing Schema Changes

CREATE TRIGGER trg_LogSchemaChanges
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @data XML = EVENTDATA();
    INSERT INTO dbo.SchemaChangeLog (event_type, object_name, changed_by)
    VALUES (
        @data.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(200)'),
        @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(100)')
    );
END;

EVENTDATA() returns an XML document describing exactly what changed and who changed it — a genuinely useful audit trail for compliance-sensitive environments, where “who dropped that table, and when” is a question that needs a real answer, not a guess from backup timestamps.

-- Trigger it and see the audit row appear
CREATE TABLE dbo.Scratch_DDLTest (id INT);
SELECT * FROM dbo.SchemaChangeLog ORDER BY changed_by DESC; -- your CREATE TABLE is logged
DROP TABLE dbo.Scratch_DDLTest;

Logon Triggers: Powerful, and Genuinely Dangerous

A logon trigger fires when a login session is established — used for things like restricting logins by time of day or capping concurrent sessions. Unlike every other trigger type in this chapter, a logon trigger sits between you and the ability to connect at all — an error in its logic, or a bug that always evaluates to “deny,” locks out every single login attempt, with no normal way back in.

CREATE TRIGGER trg_RestrictOffHoursLogin ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS
BEGIN
    IF DATEPART(HOUR, GETDATE()) NOT BETWEEN 6 AND 22
       AND ORIGINAL_LOGIN() NOT IN ('sa', 'app_admin')
        ROLLBACK; -- rejects the connection
END;

Test extremely carefully, typically with that recovery plan via DAC in case something goes wrong — this is one of the very few features in this entire course where “just try it and see” is genuinely bad advice.