Bagikan melalui


Membuat Pemicu Berlapis

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance

Pemicu DML dan DDL ditumpuk ketika pemicu melakukan tindakan yang memulai pemicu lain. Tindakan ini dapat memulai pemicu lain, dan sebagainya. Pemicu DML dan DDL dapat disarangkan hingga 32 tingkat. Anda dapat mengontrol apakah pemicu AFTER dapat disarangkan melalui opsi konfigurasi server pemicu berlapis. ALIH-ALIH pemicu (hanya pemicu DML yang dapat menjadi ALIH-ALIH pemicu) yang dapat ditumpuk terlepas dari pengaturan ini.

Catatan

Referensi apa pun ke kode terkelola dari pemicu Transact-SQL dihitung sebagai satu tingkat terhadap batas berlapis 32 tingkat. Metode yang dipanggil dari dalam kode terkelola tidak dihitung terhadap batas ini.

Jika pemicu berlapis diizinkan dan pemicu dalam rantai memulai perulangan tak terbatas, tingkat berlapis terlampaui dan pemicu berakhir.

Anda dapat menggunakan pemicu berlapis untuk melakukan fungsi housekeeping yang berguna seperti menyimpan salinan cadangan baris yang terpengaruh oleh pemicu sebelumnya. Misalnya, Anda dapat membuat pemicu pada PurchaseOrderDetail yang menyimpan salinan PurchaseOrderDetail cadangan baris yang delcascadetrig dihapus pemicunya. delcascadetrig Dengan pemicu berlaku, menghapus PurchaseOrderID 1965 dari PurchaseOrderHeader menghapus baris atau baris yang sesuai dari PurchaseOrderDetail. Untuk menyimpan data, Anda dapat membuat pemicu DELETE pada PurchaseOrderDetail yang menyimpan data yang dihapus ke dalam tabel lain yang dibuat secara terpisah, del_save. Contohnya:

CREATE TRIGGER Purchasing.savedel  
   ON Purchasing.PurchaseOrderDetail  
FOR DELETE  
AS  
   INSERT del_save
   SELECT * FROM deleted;  

Kami tidak menyarankan penggunaan pemicu berlapis dalam urutan tergantung urutan. Gunakan pemicu terpisah untuk menyesuaikan modifikasi data.

Catatan

Karena pemicu dijalankan dalam transaksi, kegagalan pada tingkat apa pun dari sekumpulan pemicu berlapis membatalkan seluruh transaksi, dan semua modifikasi data digulung balik. Sertakan pernyataan PRINT dalam pemicu Anda sehingga Anda dapat menentukan di mana kegagalan telah terjadi.

Pemicu Berulang

Pemicu AFTER tidak memanggil dirinya secara rekursif kecuali opsi database RECURSIVE_TRIGGERS diatur.

Ada dua jenis rekursi:

  • Rekursi langsung

    Rekursi ini terjadi ketika pemicu menembak dan melakukan tindakan yang menyebabkan pemicu yang sama diaktifkan lagi. Misalnya, aplikasi memperbarui tabel T3; ini menyebabkan pemicu Trig3 diaktifkan. Trig3 memperbarui tabel T3 lagi; ini menyebabkan pemicu Trig3 aktif lagi.

    Rekursi langsung juga dapat terjadi ketika pemicu yang sama dipanggil lagi, tetapi setelah pemicu dari jenis yang berbeda (SETELAH atau ALIH-ALIH) dipanggil. Dengan kata lain, rekursi langsung pemicu ALIH-ALIH PEMicu dapat terjadi ketika pemicu ALIH-ALIH yang sama dipanggil untuk kedua kalinya, bahkan jika satu atau beberapa pemicu AFTER dipanggil di antaranya. Demikian juga, rekursi langsung pemicu AFTER dapat terjadi ketika pemicu AFTER yang sama dipanggil untuk kedua kalinya, bahkan jika satu atau beberapa PEMicu DIpanggil di antaranya. Misalnya, tabel pembaruan aplikasi T4. Pembaruan ini menyebabkan ALIH-ALIH memicu Trig4 diaktifkan. Tabel pembaruan Trig4 T5. Pembaruan ini menyebabkan setelah pemicu Trig5 diaktifkan. Trig5 memperbarui tabel T4, dan pembaruan ini menyebabkan ALIH-ALIH memicu Trig4 diaktifkan lagi. Rantai peristiwa ini dianggap rekursi langsung untuk Trig4.

  • Rekursi tidak langsung

    Rekursi ini terjadi ketika pemicu diaktifkan dan melakukan tindakan yang menyebabkan pemicu lain dari jenis yang sama (SETELAH atau ALIH-ALIH) diaktifkan. Pemicu kedua ini melakukan tindakan yang menyebabkan pemicu asli diaktifkan lagi. Dengan kata lain, rekursi tidak langsung dapat terjadi ketika pemicu ALIH-ALIH dipanggil untuk kedua kalinya, tetapi tidak sampai pemicu ALIH-ALIH PEMicu lain dipanggil di antaranya. Demikian juga, rekursi tidak langsung dapat terjadi ketika pemicu AFTER dipanggil untuk kedua kalinya, tetapi tidak sampai pemicu AFTER lain dipanggil di antaranya. Misalnya, aplikasi memperbarui tabel T1. Pembaruan ini menyebabkan pemicu SETELAH Trig1 diaktifkan. Trig1 memperbarui tabel T2, dan pembaruan ini menyebabkan setelah pemicu Trig2 diaktifkan. Trig2 pada gilirannya memperbarui tabel T1 yang menyebabkan setelah pemicu Trig1 diaktifkan lagi.

Hanya rekursi langsung pemicu AFTER yang dicegah saat opsi database RECURSIVE_TRIGGERS diatur ke NONAKTIF. Untuk menonaktifkan rekursi tidak langsung pemicu AFTER, atur juga opsi server pemicu berlapis ke 0.

Contoh

Contoh berikut menunjukkan penggunaan pemicu rekursif untuk menyelesaikan hubungan referensi mandiri (juga dikenal sebagai penutupan transitif). Misalnya, tabel emp_mgr menentukan hal berikut:

  • Karyawan (emp) di perusahaan.

  • Manajer untuk setiap karyawan (mgr).

  • Jumlah total karyawan dalam pelaporan pohon organisasi kepada setiap karyawan (NoOfReports).

Pemicu PEMBARUAN rekursif dapat digunakan untuk menjaga kolom tetap NoOfReports terbaru saat rekaman karyawan baru disisipkan. Pemicu INSERT memperbarui NoOfReports kolom catatan manajer, yang secara rekursif memperbarui NoOfReports kolom rekaman lain ke hierarki manajemen.

USE AdventureWorks2022;  
GO  
-- Turn recursive triggers ON in the database.  
ALTER DATABASE AdventureWorks2022  
   SET RECURSIVE_TRIGGERS ON;  
GO  
CREATE TABLE dbo.emp_mgr (  
   emp char(30) PRIMARY KEY,  
    mgr char(30) NULL FOREIGN KEY REFERENCES emp_mgr(emp),  
    NoOfReports int DEFAULT 0  
);  
GO  
CREATE TRIGGER dbo.emp_mgrins ON dbo.emp_mgr  
FOR INSERT  
AS  
DECLARE @e char(30), @m char(30);  
DECLARE c1 CURSOR FOR  
   SELECT emp_mgr.emp  
   FROM   emp_mgr, inserted  
   WHERE emp_mgr.emp = inserted.mgr;  
  
OPEN c1;  
FETCH NEXT FROM c1 INTO @e;  
WHILE @@fetch_status = 0  
BEGIN  
   UPDATE dbo.emp_mgr  
   SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Add 1 for newly  
   WHERE emp_mgr.emp = @e ;                           -- added employee.  
  
   FETCH NEXT FROM c1 INTO @e;  
END  
CLOSE c1;  
DEALLOCATE c1;  
GO  
-- This recursive UPDATE trigger works assuming:  
--   1. Only singleton updates on emp_mgr.  
--   2. No inserts in the middle of the org tree.  
CREATE TRIGGER dbo.emp_mgrupd ON dbo.emp_mgr FOR UPDATE  
AS  
IF UPDATE (mgr)  
BEGIN  
   UPDATE dbo.emp_mgr  
   SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Increment mgr's  
   FROM inserted                            -- (no. of reports) by  
   WHERE emp_mgr.emp = inserted.mgr;         -- 1 for the new report.  
  
   UPDATE dbo.emp_mgr  
   SET emp_mgr.NoOfReports = emp_mgr.NoOfReports - 1 -- Decrement mgr's  
   FROM deleted                             -- (no. of reports) by 1  
   WHERE emp_mgr.emp = deleted.mgr;          -- for the new report.  
END  
GO  
-- Insert some test data rows.  
INSERT dbo.emp_mgr(emp, mgr) VALUES  
    ('Harry', NULL)  
    ,('Alice', 'Harry')  
    ,('Paul', 'Alice')  
    ,('Joe', 'Alice')  
    ,('Dave', 'Joe');  
GO  
SELECT emp,mgr,NoOfReports  
FROM dbo.emp_mgr;  
GO  
-- Change Dave's manager from Joe to Harry  
UPDATE dbo.emp_mgr SET mgr = 'Harry'  
WHERE emp = 'Dave';  
GO  
SELECT emp,mgr,NoOfReports FROM emp_mgr;  
  
GO  

Berikut adalah hasilnya sebelum pembaruan.

emp                            mgr                           NoOfReports  
------------------------------ ----------------------------- -----------  
Alice                          Harry                          2  
Dave                           Joe                            0  
Harry                          NULL                           1  
Joe                            Alice                          1  
Paul                           Alice                          0  

Berikut adalah hasilnya setelah pembaruan.

emp                            mgr                           NoOfReports  
------------------------------ ----------------------------- -----------  
Alice                          Harry                          2  
Dave                           Harry                          0  
Harry                          NULL                           2  
Joe                            Alice                          0  
Paul                           Alice                          0  

Untuk mengatur opsi pemicu berlapis

Untuk mengatur opsi database RECURSIVE_TRIGGERS

Lihat Juga

BUAT PEMICU (Transact-SQL)
Mengonfigurasi Opsi Konfigurasi Server pemicu berlapis