Skip to main content

12. Triggers

Objectives​

  • Hiểu được khái niệm trigger và cách sử dụng
  • Biết được các loại trigger
  • Biết được cách tạo trigger
  • Biết được cách cập nhật, xóa và vô hiệu hóa trigger

1. Trigger là gì?​

  • Trigger là một đối tượng trong SQL Server, nó được sử dụng để thực thi một tập hợp các câu lệnh SQL khi một sự kiện xảy ra. Sự kiện có thể là một câu lệnh INSERT, UPDATE hoặc DELETE. Trigger có thể được kích hoạt trước hoặc sau khi sự kiện xảy ra.
  • Không giống như stored procedure, trigger không được gọi bởi một ứng dụng hoặc một người dùng. Trigger được kích hoạt bởi một sự kiện như INSERT, UPDATE, DELETE và không thể được gọi như một stored procedure.

2. Cách sử dụng trigger​

  • Trigger có thể chứa các đoạn mã xử lý T-SQL để xử lý các yêu cầu về nghiệp vụ.

3. Các loại triggers​

3.1. DML Trigger​

Là loại trigger được kích hoạt bởi các câu lệnh DML như INSERT, UPDATE hoặc DELETE. Có hai loại DML trigger:

  • After trigger: được kích hoạt sau khi sự kiện xảy ra.
  • Instead of trigger: được kích hoạt thay thế cho sự kiện.
Lưu ý

Có 2 bảng inserted và deleted được sử dụng trong trigger. Bảng inserted chứa các bản ghi được thêm vào bởi câu lệnh INSERT hoặc UPDATE. Table deleted chứa các bản ghi bị xóa bởi câu lệnh DELETE hoặc UPDATE.

Thứ tự thực thi của các DML trigger:

  1. Các trigger INSTEAD OF được kích hoạt trước.
  2. Các trigger AFTER được kích hoạt sau.

Cách cấu hình thứ tự thực thi của các trigger:

Cấu hình thứ tự thực thi của các trigger
sp_settriggerorder [@triggername = ] 'triggername' , [@order = ] 'order' , [@stmttype = ] 'stmttype'

3.1.1. AFTER Trigger​

Tạo một trigger AFTER để cập nhật tồn kho khi có đơn đặt hàng mới
CREATE OR ALTER TRIGGER trg_OrderDetails_Update_ProductStock
ON OrderDetails
AFTER INSERT
AS
BEGIN
    UPDATE Products
        SET Stock = Stock - I.Quantity
    FROM
        Products AS P
    INNER JOIN inserted AS I ON P.Id = I.ProductID
END
Tạo một trigger AFTER để ngăn chặn việc cập nhật / xóa đơn hàng khi đơn hàng có trạng thái COMPLETED
CREATE OR ALTER TRIGGER trg_Orders_Prevent_UpdateDelete
ON Orders
AFTER UPDATE, DELETE
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted WHERE [Status] = 'COMPLETED')
    BEGIN
        PRINT 'Cannot update order having status = COMPLETED.'
        ROLLBACK
    END

    IF EXISTS (SELECT * FROM deleted WHERE [Status] = 'COMPLETED')
    BEGIN
        PRINT 'Cannot delete order having status = COMPLETED.'
        ROLLBACK
    END
END
Tạo một trigger AFTER để ngăn chặn việc cập nhật / thêm mới / xóa chi tiết đơn hàng khi đơn hàng có trạng thái COMPLETED
CREATE OR ALTER TRIGGER trg_OrderDetails_Prevent_InsertUpdateDelete
ON OrderDetails
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    IF EXISTS (
        SELECT * FROM
        inserted AS OD INNER JOIN Orders AS O ON OD.OrderId = O.Id
        WHERE O.[Status] = 'COMPLETED'
    )
    BEGIN
        PRINT 'Cannot insert or update order details having order''s status = COMPLETED.'
        ROLLBACK
    END

    IF EXISTS (
        SELECT * FROM
        deleted AS OD INNER JOIN Orders AS O ON OD.OrderId = O.Id
        WHERE O.[Status] = 'COMPLETED'
    )
    BEGIN
        PRINT 'Cannot delete order details having order''s status = COMPLETED.'
        ROLLBACK
    END
END

3.1.2. INSTEAD OF Trigger​

Tạo một trigger INSTEAD OF để ngăn chặn việc thêm dữ liệu vào bảng Customers
CREATE TRIGGER trg_Customers_PreventInsert
ON Customers
INSTEAD OF INSERT
AS
BEGIN
    PRINT 'Cannot insert data into the table.'
END

3.2. DDL Trigger​

Là loại trigger được kích hoạt bởi các câu lệnh DDL như CREATE, ALTER hoặc DROP.

Các ví dụ​

Tạo một trigger để ngăn chặn việc xóa bảng Customers
CREATE TRIGGER trg_Customers_Prevent_DropTable
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customers]') AND type in (N'U'))
    BEGIN
        PRINT 'Cannot drop the table: Customers.'
        ROLLBACK
    END
END
Tạo một trigger để ghi nhật ký sửa đổi bảng Customers
CREATE TRIGGER trg_Customers_LogAlterTable
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customers]') AND type in (N'U'))
    BEGIN
        INSERT INTO dbo.Logs ([Date], [User], [Host], [Action], [Table])
        SELECT GETDATE(), USER_NAME(), HOST_NAME(), 'ALTER TABLE', 'Customers'
    END
END