Triggers

Trigger là một loại stored procedure đặc biệt tự động thực thi (kích hoạt) khi xảy ra một sự kiện nhất định trên database. Trigger không được gọi trực tiếp - chúng phản ứng với các events.

Cảnh báo: Trigger là công cụ mạnh nhưng dễ lạm dụng. Luôn cân nhắc kỹ trước khi dùng.

1. Loại Triggers

SQL Server Triggers
├── DML Triggers (Data Manipulation Language)
│   ├── AFTER (FOR) Trigger
│   │   ├── AFTER INSERT
│   │   ├── AFTER UPDATE
│   │   └── AFTER DELETE
│   └── INSTEAD OF Trigger
│       ├── INSTEAD OF INSERT
│       ├── INSTEAD OF UPDATE
│       └── INSTEAD OF DELETE
├── DDL Triggers (Data Definition Language)
│   ├── Database-level (CREATE, ALTER, DROP objects)
│   └── Server-level (CREATE DATABASE, etc.)
└── Logon Triggers
    └── LOGON event

2. DML Triggers: inserted và deleted Tables

Khi DML trigger kích hoạt, SQL Server tạo hai bảng ảo (pseudo-tables):

• inserted: Chứa rows mới (sau INSERT hoặc sau UPDATE)
• deleted: Chứa rows cũ (trước DELETE hoặc trước UPDATE)

-- AFTER INSERT Trigger: Audit khi thêm đơn hàng
CREATE TRIGGER trg_Orders_AfterInsert
ON dbo.Orders
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.OrderAuditLog (
        OrderId, 
        CustomerId, 
        TotalAmount, 
        Action, 
        ActionBy, 
        ActionDate
    )
    SELECT 
        i.OrderId,
        i.CustomerId,
        i.TotalAmount,
        'INSERT',
        SYSTEM_USER,
        GETDATE()
    FROM inserted i; -- inserted có thể chứa NHIỀU rows!
END;
GO

-- AFTER DELETE Trigger: Soft delete / archive
CREATE TRIGGER trg_Products_AfterDelete
ON dbo.Products
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Lưu vào archive trước khi mất
    INSERT INTO dbo.ProductsArchive (
        ProductId, ProductName, Price, CategoryId, DeletedBy, DeletedDate
    )
    SELECT 
        d.ProductId,
        d.ProductName,
        d.Price,
        d.CategoryId,
        SYSTEM_USER,
        GETDATE()
    FROM deleted d;
END;
GO

-- AFTER UPDATE Trigger: Track thay đổi
CREATE TRIGGER trg_Employees_AfterUpdate
ON dbo.Employees
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- So sánh inserted (mới) và deleted (cũ)
    INSERT INTO dbo.EmployeeChangeLog (
        EmployeeId,
        ColumnChanged,
        OldValue,
        NewValue,
        ChangedBy,
        ChangedDate
    )
    SELECT
        i.EmployeeId,
        'Salary',
        CAST(d.Salary AS NVARCHAR(50)),
        CAST(i.Salary AS NVARCHAR(50)),
        SYSTEM_USER,
        GETDATE()
    FROM inserted i
    INNER JOIN deleted d ON i.EmployeeId = d.EmployeeId
    WHERE i.Salary <> d.Salary; -- Chỉ log khi Salary thực sự thay đổi
END;
GO

3. INSTEAD OF Triggers

INSTEAD OF trigger thay thế (intercept) DML gốc. DML gốc không chạy trừ khi trigger tự thực hiện nó.

INSTEAD OF trên View (Updatable Views)

-- View join nhiều bảng - mặc định không update được
CREATE VIEW dbo.vw_EmployeeDetails
AS
SELECT 
    e.EmployeeId,
    e.FirstName,
    e.LastName,
    e.Salary,
    d.DepartmentName,
    d.DepartmentId
FROM Employees e
INNER JOIN Departments d ON e.DepartmentId = d.DepartmentId;
GO

-- INSTEAD OF INSERT: Cho phép INSERT qua view
CREATE TRIGGER trg_vw_EmployeeDetails_InsteadOfInsert
ON dbo.vw_EmployeeDetails
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate
    IF EXISTS (SELECT 1 FROM inserted WHERE Salary < 0)
    BEGIN
        THROW 50001, 'Salary không được âm', 1;
    END;

    -- Thực hiện INSERT vào base table
    INSERT INTO dbo.Employees (FirstName, LastName, Salary, DepartmentId)
    SELECT i.FirstName, i.LastName, i.Salary, i.DepartmentId
    FROM inserted i;
END;
GO

-- INSTEAD OF UPDATE: Xử lý update phức tạp
CREATE TRIGGER trg_vw_EmployeeDetails_InsteadOfUpdate
ON dbo.vw_EmployeeDetails
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Chỉ cho phép update các cột employee, không cho phép update department tên
    UPDATE e
    SET 
        e.FirstName = i.FirstName,
        e.LastName  = i.LastName,
        e.Salary    = i.Salary
    FROM Employees e
    INNER JOIN inserted i ON e.EmployeeId = i.EmployeeId;
    
    -- Nếu muốn update DepartmentId, xử lý riêng
    UPDATE e
    SET e.DepartmentId = i.DepartmentId
    FROM Employees e
    INNER JOIN inserted i ON e.EmployeeId = i.EmployeeId
    WHERE e.DepartmentId <> i.DepartmentId;
END;
GO

INSTEAD OF DELETE với Soft Delete

-- Thay vì xóa thật, mark IsDeleted = 1
CREATE TRIGGER trg_Orders_InsteadOfDelete
ON dbo.Orders
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE o
    SET 
        o.IsDeleted = 1,
        o.DeletedBy = SYSTEM_USER,
        o.DeletedDate = GETDATE()
    FROM dbo.Orders o
    INNER JOIN deleted d ON o.OrderId = d.OrderId;

    -- Không INSERT/DELETE thật → records không bị xóa
END;
GO

4. Trigger Context: @@ROWCOUNT, COLUMNS_UPDATED(), UPDATE()

CREATE TRIGGER trg_OrderDetails_Update
ON dbo.OrderDetails
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- @@ROWCOUNT: Số rows bị ảnh hưởng
    -- Nếu 0 rows thì không cần xử lý
    IF @@ROWCOUNT = 0 RETURN;

    -- UPDATE(column): TRUE nếu column được include trong UPDATE statement
    -- Lưu ý: TRUE cả khi giá trị không đổi!
    IF UPDATE(Quantity) OR UPDATE(UnitPrice)
    BEGIN
        -- Recalculate LineTotal
        UPDATE od
        SET od.LineTotal = od.Quantity * od.UnitPrice
        FROM dbo.OrderDetails od
        INNER JOIN inserted i ON od.OrderDetailId = i.OrderDetailId;
    END;

    -- COLUMNS_UPDATED(): Bitmask của columns được update
    -- ít dùng vì phụ thuộc vào ordinal position của column
    -- Column 1 = bit 1 (1), Column 2 = bit 2 (2), Column 3 = bit 3 (4)...
    -- COLUMNS_UPDATED() & 4 = 4 → Column 3 được update
    DECLARE @ChangedColumns VARBINARY(10) = COLUMNS_UPDATED();
    IF (@ChangedColumns & 2) = 2 -- Column 2 (Quantity)
    BEGIN
        INSERT INTO AuditLog (TableName, Event, ChangedAt)
        VALUES ('OrderDetails', 'Quantity Changed', GETDATE());
    END;
END;
GO

5. Nested và Recursive Triggers

-- NESTED TRIGGERS: Trigger A fires trigger B
-- Controlled bởi: sp_configure 'nested triggers', 1/0
-- Mặc định: ON (nested triggers enabled)

-- Ví dụ nested: 
-- UPDATE Orders → trg_Orders_Update → UPDATE OrderSummary → trg_OrderSummary_Update

-- Kiểm tra setting
SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'nested triggers';

-- Disable nested triggers
EXEC sp_configure 'nested triggers', 0;
RECONFIGURE;

-- RECURSIVE TRIGGERS: Trigger gọi lại chính nó
-- Controlled bởi: ALTER DATABASE ... SET RECURSIVE_TRIGGERS ON/OFF
-- Mặc định: OFF

ALTER DATABASE MyDatabase SET RECURSIVE_TRIGGERS ON;

-- Ví dụ recursive trigger (CẦN THẬN - dễ gây infinite loop!)
CREATE TRIGGER trg_Categories_Update
ON dbo.Categories
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Nếu không có guard condition → infinite loop!
    IF UPDATE(ParentCategoryId)
    BEGIN
        -- Update parent's child count
        UPDATE c
        SET c.ChildCount = (SELECT COUNT(*) FROM Categories WHERE ParentCategoryId = c.CategoryId)
        FROM Categories c
        WHERE c.CategoryId IN (SELECT ParentCategoryId FROM inserted WHERE ParentCategoryId IS NOT NULL);
        -- Điều này có thể trigger lại trigger này!
    END;
END;

6. Thứ Tự Trigger: sp_settriggerorder

-- Khi có nhiều AFTER trigger trên cùng một event, 
-- có thể chỉ định trigger nào chạy FIRST và LAST

-- Ví dụ: 3 triggers trên Orders AFTER INSERT
-- trg_Validate → trg_Audit → trg_Notify

EXEC sp_settriggerorder 
    @triggername = 'trg_Orders_Validate',
    @order = 'FIRST',
    @stmttype = 'INSERT';

EXEC sp_settriggerorder 
    @triggername = 'trg_Orders_Notify',
    @order = 'LAST',
    @stmttype = 'INSERT';

-- Triggers ở giữa không có guaranteed order

-- Xem trigger order
SELECT 
    t.name AS TriggerName,
    te.type_desc AS EventType,
    t.is_disabled
FROM sys.triggers t
JOIN sys.trigger_events te ON t.object_id = te.object_id
WHERE t.parent_id = OBJECT_ID('dbo.Orders')
ORDER BY t.name;

7. DDL Triggers: Audit Schema Changes

-- DATABASE-LEVEL DDL Trigger: Khi ai đó thay đổi schema
CREATE TRIGGER trg_DDL_AuditSchemaChanges
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
    CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
    CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
    CREATE_INDEX, DROP_INDEX
AS
BEGIN
    SET NOCOUNT ON;

    -- EVENTDATA(): Trả về XML mô tả sự kiện DDL
    DECLARE @EventData XML = EVENTDATA();

    INSERT INTO dbo.SchemaChangeLog (
        EventType,
        ObjectName,
        ObjectType,
        SchemaName,
        CommandText,
        LoginName,
        HostName,
        EventTime
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',    'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',   'NVARCHAR(200)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]',   'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',   'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]',  'NVARCHAR(MAX)'),
        @EventData.value('(/EVENT_INSTANCE/LoginName)[1]',    'NVARCHAR(200)'),
        HOST_NAME(),
        GETDATE();
END;
GO

-- SERVER-LEVEL DDL Trigger: Audit database creation
CREATE TRIGGER trg_Server_PreventDropDatabase
ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData XML = EVENTDATA();
    DECLARE @DbName NVARCHAR(200) = @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(200)');

    -- Chặn xóa production databases
    IF @DbName LIKE 'PROD_%'
    BEGIN
        RAISERROR('Không được phép DROP production database!', 16, 1);
        ROLLBACK; -- Hủy lệnh DROP DATABASE
    END;
END;
GO

8. Performance Impact của Triggers

Tại sao Triggers ảnh hưởng Performance?

1. Synchronous: Trigger chạy trong cùng transaction với DML gốc
2. Blocking: DML phải chờ trigger hoàn thành mới commit
3. inserted/deleted tables: Phải tạo copy của data vào tempdb
4. Locks: Transaction dài hơn → lock lâu hơn → deadlock potential
5. Invisible overhead: Developer không thấy trigger khi viết DML

-- Đo hiệu năng trigger
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

-- DML với trigger
UPDATE Orders SET Status = 'Shipped' WHERE OrderId = 1;
-- Xem output để thấy trigger time

-- Disable trigger để so sánh
DISABLE TRIGGER trg_Orders_AfterUpdate ON Orders;
UPDATE Orders SET Status = 'Shipped' WHERE OrderId = 1;
ENABLE TRIGGER trg_Orders_AfterUpdate ON Orders;

9. Trigger Anti-Patterns

Anti-Pattern 1: Giả sử Chỉ Có Một Row

-- ❌ SAI: Giả sử một row mỗi lần
CREATE TRIGGER trg_Bad_SingleRow ON Orders AFTER INSERT
AS
BEGIN
    -- GetDate với SCALAR - chỉ lấy một row!
    DECLARE @OrderId INT = (SELECT OrderId FROM inserted);
    DECLARE @CustomerId INT = (SELECT CustomerId FROM inserted);
    
    -- Nếu INSERT nhiều rows → lỗi hoặc lấy row tùy ý!
    
    EXEC dbo.SendOrderConfirmation @OrderId, @CustomerId;
END;

-- ✅ ĐÚNG: Xử lý multi-row
CREATE TRIGGER trg_Good_MultiRow ON Orders AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Xử lý tất cả rows trong inserted
    INSERT INTO NotificationQueue (OrderId, CustomerId, QueuedAt)
    SELECT OrderId, CustomerId, GETDATE()
    FROM inserted;
    -- SP để xử lý từng notification sẽ được gọi bởi job riêng
END;

Anti-Pattern 2: CURSOR trong Trigger

-- ❌ SAI: Cursor trong trigger = rất chậm
CREATE TRIGGER trg_Bad_CursorInTrigger ON Orders AFTER INSERT
AS
BEGIN
    DECLARE @OrderId INT;
    DECLARE cur CURSOR FOR SELECT OrderId FROM inserted;
    OPEN cur;
    FETCH NEXT FROM cur INTO @OrderId;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC dbo.ProcessOrder @OrderId; -- Gọi SP cho từng row
        FETCH NEXT FROM cur INTO @OrderId;
    END;
    CLOSE cur;
    DEALLOCATE cur;
END;

-- ✅ TỐT HƠN: Batch vào queue, xử lý async
CREATE TRIGGER trg_Good_BatchQueue ON Orders AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO ProcessingQueue (OrderId, QueuedAt, Status)
    SELECT OrderId, GETDATE(), 'Pending'
    FROM inserted;
    -- Background job sẽ xử lý queue
END;

Anti-Pattern 3: Business Logic Phức Tạp trong Trigger

-- ❌ TRÁNH: Quá nhiều logic trong trigger
-- → Khó debug, khó test, khó maintain
-- → Developer INSERT vào Orders không biết trigger làm gì

-- ✅ THAY BẰNG: Application logic hoặc Stored Procedure rõ ràng

10. Alternatives to Triggers

Computed Columns (thay vì trigger calculate)

-- Thay vì trigger tính LineTotal
-- ❌ Trigger approach
CREATE TRIGGER trg_CalcLineTotal ON OrderDetails AFTER INSERT, UPDATE
AS UPDATE od SET od.LineTotal = od.Quantity * od.UnitPrice
FROM OrderDetails od INNER JOIN inserted i ON od.OrderDetailId = i.OrderDetailId;

-- ✅ Computed Column approach (tự động, không cần trigger)
ALTER TABLE OrderDetails
ADD LineTotal AS (Quantity * UnitPrice) PERSISTED;

Filtered Indexes (thay vì trigger validate)

-- Thay vì trigger ngăn duplicate active orders
-- ✅ Unique filtered index
CREATE UNIQUE INDEX UIX_ActiveOrders_Customer
ON Orders (CustomerId)
WHERE Status = 'Active'; -- Chỉ enforce unique khi Active

CDC (Change Data Capture) thay vì Audit Trigger

-- Enable CDC cho bảng Orders
EXEC sys.sp_cdc_enable_db; -- Enable cho database

EXEC sys.sp_cdc_enable_table
    @source_schema = 'dbo',
    @source_name   = 'Orders',
    @role_name     = NULL, -- Security role
    @capture_instance = 'dbo_Orders',
    @supports_net_changes = 1;

-- Query changes (không cần trigger!)
SELECT 
    __$operation, -- 1=DELETE, 2=INSERT, 3=before UPDATE, 4=after UPDATE
    __$start_lsn,
    OrderId,
    TotalAmount,
    Status
FROM cdc.fn_cdc_get_all_changes_dbo_Orders(
    @from_lsn, @to_lsn, 'all'
);

11. DISABLE / ENABLE TRIGGER

-- Disable một trigger cụ thể
DISABLE TRIGGER trg_Orders_AfterInsert ON dbo.Orders;

-- Enable lại
ENABLE TRIGGER trg_Orders_AfterInsert ON dbo.Orders;

-- Disable TẤT CẢ triggers trên một table
DISABLE TRIGGER ALL ON dbo.Orders;
ENABLE TRIGGER ALL ON dbo.Orders;

-- Disable TẤT CẢ DDL triggers trên database
DISABLE TRIGGER ALL ON DATABASE;
ENABLE TRIGGER ALL ON DATABASE;

-- Use case: Import dữ liệu lớn mà không muốn trigger chạy
DISABLE TRIGGER ALL ON dbo.Orders;
-- BulkInsert...
ENABLE TRIGGER ALL ON dbo.Orders;

-- Kiểm tra trigger nào đang enabled/disabled
SELECT 
    t.name,
    t.is_disabled,
    te.type_desc AS EventType,
    OBJECT_NAME(t.parent_id) AS TableName
FROM sys.triggers t
JOIN sys.trigger_events te ON t.object_id = te.object_id
WHERE t.parent_id = OBJECT_ID('dbo.Orders');

12. Logon Triggers

-- Logon Trigger: Kiểm soát ai được phép login
CREATE TRIGGER trg_LimitConnections
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    -- Giới hạn số connections của một login
    IF ORIGINAL_LOGIN() = 'ReportUser'
    BEGIN
        IF (
            SELECT COUNT(*) 
            FROM sys.dm_exec_sessions 
            WHERE login_name = 'ReportUser'
        ) > 5
        BEGIN
            RAISERROR('Quá nhiều connections cho ReportUser', 16, 1);
            ROLLBACK; -- Từ chối connection
        END;
    END;

    -- Block connections ngoài giờ làm việc
    IF ORIGINAL_LOGIN() NOT IN ('sa', 'AdminUser')
    BEGIN
        IF DATEPART(HOUR, GETDATE()) < 8 OR DATEPART(HOUR, GETDATE()) > 18
        BEGIN
            RAISERROR('Chỉ được kết nối trong giờ làm việc (8:00-18:00)', 16, 1);
            ROLLBACK;
        END;
    END;
END;
GO

-- CẢNH BÁO: Logon trigger lỗi có thể chặn TẤT CẢ connections, kể cả sa!
-- Nếu bị lock out, phải dùng Dedicated Admin Connection (DAC):
-- sqlcmd -S server -A (hoặc Admin: prefix trong SSMS)
-- DISABLE TRIGGER trg_LimitConnections ON ALL SERVER;

Tóm Tắt: Khi Nào Dùng Trigger?

Golden Rule: Trigger là công cụ cuối cùng. Nếu có thể giải quyết bằng constraints, computed columns, application logic, hoặc CDC - hãy dùng chúng thay thế.