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