Membuat pemicu

Pemicu adalah prosedur tersimpan khusus yang secara otomatis dijalankan saat peristiwa tertentu terjadi di database Anda. Anda menentukan pemicu untuk mempertahankan integritas data, menerapkan aturan bisnis, dan mengotomatiskan operasi database tanpa memerlukan kode tingkat aplikasi.

Memahami dasar-dasar pemicu

Pemicu merespons perubahan data atau perubahan skema dalam database Anda. Saat membuat pemicu, Anda menentukan peristiwa yang mengaktifkannya dan tindakan yang dilakukannya.

Pemicu dijalankan secara otomatis. Tidak seperti prosedur tersimpan yang Anda panggil secara eksplisit, pemicu diaktifkan sebagai tanggapan terhadap INSERT, UPDATE, DELETE, atau pernyataan DDL. Eksekusi otomatis ini membuatnya kuat untuk memberlakukan aturan yang harus berlaku secara konsisten di semua modifikasi data.

SQL Server mendukung dua kategori pemicu utama: pemicu DML (Bahasa Manipulasi Data) dan pemicuDDL (Bahasa Definisi Data). Pemicu DML merespons perubahan data tabel, sementara pemicu DDL merespons perubahan skema seperti CREATE, ALTER, atau DROP pernyataan.

Membuat pemicu DML untuk modifikasi data

Pemicu DML memantau dan merespons perubahan data dalam tabel atau tampilan. Anda menentukan mereka sebagai pemicu AFTER atau pemicu INSTEAD OF.

SETELAH pemicu dijalankan setelah pernyataan pemicu selesai. Database pertama kali melakukan modifikasi data, lalu menjalankan kode pemicu. Anda menggunakan pemicu AFTER untuk memvalidasi perubahan, memperbarui tabel terkait, atau modifikasi log:

CREATE TRIGGER tr_UpdateInventory
ON Sales.OrderDetails
AFTER INSERT
AS
BEGIN
    UPDATE Inventory.Products
    SET QuantityInStock = QuantityInStock - i.Quantity
    FROM Inventory.Products p
    INNER JOIN inserted i ON p.ProductID = i.ProductID;
END;

SEBAGAI GANTINYA pemicu menggantikan pernyataan modifikasi data asli. Kode pemicu mengeksekusi alih-alih INSERT operasi, UPDATE atau DELETE. Anda menggunakan INSTEAD OF trigger untuk memodifikasi view yang biasanya tidak menerima modifikasi langsung atau untuk menerapkan logika bisnis yang kompleks.

CREATE TRIGGER tr_UpdateOrderView
ON Sales.OrderSummaryView
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE Sales.Orders
    SET OrderStatus = i.OrderStatus,
        ModifiedDate = GETDATE()
    FROM Sales.Orders o
    INNER JOIN inserted i ON o.OrderID = i.OrderID;
END;

Dengan pemicu DML, Anda mengakses tabel pseudo yang disisipkan dan dihapus . Tabel sementara ini menyimpan salinan baris yang terpengaruh. INSERT operasi mengisi tabel yang disisipkan , DELETE operasi mengisi tabel yang dihapus , dan UPDATE operasi mengisi kedua tabel dengan nilai lama dalam nilai yang dihapus dan nilai baru dalam disisipkan.

Menerapkan pemicu untuk peristiwa tertentu

Anda menentukan peristiwa modifikasi data mana yang mengaktifkan pemicu Anda. Satu pemicu dapat merespons beberapa peristiwa dengan menggabungkan INSERT, , UPDATEdan DELETE dalam definisi pemicu.

Untuk kontrol yang tepat, Anda membuat pemicu terpisah untuk setiap operasi. Pendekatan ini menyederhanakan kode Anda dan membuat pemicu Anda lebih mudah dipertahankan:

CREATE TRIGGER tr_LogPriceChanges
ON Products.Catalog
AFTER UPDATE
AS
BEGIN
    IF UPDATE(Price)
    BEGIN
        INSERT INTO Audit.PriceHistory (ProductID, OldPrice, NewPrice, ChangeDate)
        SELECT d.ProductID, d.Price, i.Price, GETDATE()
        FROM deleted d
        INNER JOIN inserted i ON d.ProductID = i.ProductID
        WHERE d.Price <> i.Price;
    END;
END;

Pada saat yang sama, Anda mungkin menggabungkan peristiwa ketika logika yang sama berlaku untuk beberapa operasi. Misalnya, Anda dapat membuat satu pemicu audit yang merespons INSERT, , UPDATEdan DELETE:

CREATE TRIGGER tr_AuditEmployeeChanges
ON HR.Employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @Operation NVARCHAR(10);
    
    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
        SET @Operation = 'INSERT';
    ELSE IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
        SET @Operation = 'UPDATE';
    ELSE
        SET @Operation = 'DELETE';
    
    INSERT INTO Audit.EmployeeLog (EmployeeID, Operation, ChangeDate)
    SELECT COALESCE(i.EmployeeID, d.EmployeeID), @Operation, GETDATE()
    FROM inserted i
    FULL OUTER JOIN deleted d ON i.EmployeeID = d.EmployeeID;
END;

Fungsi ini UPDATE() membantu Anda menentukan kolom mana yang diubah. Anda memeriksa kolom tertentu untuk menghindari pemrosesan yang tidak perlu ketika hanya bidang tertentu yang penting untuk logika bisnis Anda.

Menerapkan praktik terbaik pemicu

Pemicu memengaruhi performa database karena dijalankan dengan setiap operasi yang memenuhi syarat. Anda menulis kode pemicu yang efisien untuk meminimalkan dampak pada throughput transaksi.

Jaga agar logika pemicu Anda tetap fokus dan minimal. Jalankan hanya operasi penting dalam badan pemicu. Untuk operasi yang kompleks atau memakan waktu, pertimbangkan untuk mencatat detail peristiwa dan memprosesnya secara asinkron melalui pekerjaan terpisah:

CREATE TRIGGER tr_QueueLargeOrders
ON Sales.Orders
AFTER INSERT
AS
BEGIN
    INSERT INTO Processing.OrderQueue (OrderID, TotalAmount, QueuedDate)
    SELECT OrderID, TotalAmount, GETDATE()
    FROM inserted
    WHERE TotalAmount > 10000;
END;

Hindari operasi rekursif di mana modifikasi pemicu menyebabkan pemicu yang sama diaktifkan lagi. Atur RECURSIVE_TRIGGERS opsi database dengan tepat dan desain pemicu Anda untuk mencegah perulangan tanpa akhir.

Menangani kesalahan dengan benar dalam pemicu. Perilaku transaksi tergantung pada penanganan kesalahan Anda. Jika pemicu mengalami kesalahan dan Anda tidak menanganinya, SQL Server mengembalikan pemicu dan pernyataan asli:

CREATE TRIGGER tr_ValidateOrderDate
ON Sales.Orders
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted WHERE OrderDate > GETDATE())
    BEGIN
        THROW 50001, 'Order date cannot be in the future', 1;
    END;
END;

Dokumentasikan pemicu Anda secara menyeluruh. Pengembang lain perlu memahami mengapa pemicu ada dan apa yang mereka lakukan, karena mereka menjalankan secara tak terlihat selama operasi database normal.

Sekarang setelah Anda memahami cara membuat dan menerapkan pemicu, Anda siap untuk menjelajahi bagaimana mereka berintegrasi dengan objek keterprograman lainnya untuk membangun solusi database yang komprehensif.