BUAT PEMICU (Transact-SQL)

Artikel
08/11/2023

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance

Membuat pemicu DML, DDL, atau masuk. Pemicu adalah jenis prosedur tersimpan khusus yang berjalan secara otomatis saat peristiwa terjadi di server database. Pemicu DML berjalan saat pengguna mencoba memodifikasi data melalui peristiwa bahasa manipulasi data (DML). Peristiwa DML adalah pernyataan INSERT, UPDATE, atau DELETE pada tabel atau tampilan. Pemicu ini diaktifkan ketika peristiwa yang valid diaktifkan, apakah baris tabel terpengaruh atau tidak. Untuk informasi selengkapnya, lihat Pemicu DML.

Pemicu DDL berjalan sebagai respons terhadap berbagai peristiwa bahasa definisi data (DDL). Peristiwa ini terutama sesuai dengan pernyataan TRANSACT-SQL CREATE, ALTER, dan DROP, dan prosedur tersimpan sistem tertentu yang melakukan operasi seperti DDL.

Masuk memicu kebakaran sebagai respons terhadap peristiwa LOGON yang dimunculkan saat sesi pengguna sedang dibuat. Anda dapat membuat pemicu langsung dari pernyataan Transact-SQL atau dari metode rakitan yang dibuat di runtime bahasa umum (CLR) Microsoft .NET Framework dan diunggah ke instans SQL Server. SQL Server memungkinkan Anda membuat beberapa pemicu untuk pernyataan tertentu.

Penting

Kode berbahaya di dalam pemicu dapat berjalan di bawah hak istimewa yang disempurnakan. Untuk informasi selengkapnya tentang cara mengurangi ancaman ini, lihat Mengelola Keamanan Pemicu.

Catatan

Integrasi .NET Framework CLR ke SQL Server dibahas dalam artikel ini. Integrasi CLR tidak berlaku untuk Azure SQL Database.

Konvensi sintaks transact-SQL

Sintaks SQL Server

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  
  
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table | view }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
[ WITH APPEND ]  
[ NOT FOR REPLICATION ]   
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  
  
<dml_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  
  
<method_specifier> ::=  
    assembly_name.class_name.method_name

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a 
-- table (DML Trigger on memory-optimized tables)  
  
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
AS { sql_statement  [ ; ] [ ,...n ] }  
  
<dml_trigger_option> ::=  
    [ NATIVE_COMPILATION ]  
    [ SCHEMABINDING ]  
    [ EXECUTE AS Clause ]

-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, 
-- REVOKE or UPDATE statement (DDL Trigger)  
  
CREATE [ OR ALTER ] TRIGGER trigger_name   
ON { ALL SERVER | DATABASE }   
[ WITH <ddl_trigger_option> [ ,...n ] ]  
{ FOR | AFTER } { event_type | event_group } [ ,...n ]  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
  
<ddl_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

-- Trigger on a LOGON event (Logon Trigger)  
  
CREATE [ OR ALTER ] TRIGGER trigger_name   
ON ALL SERVER   
[ WITH <logon_trigger_option> [ ,...n ] ]  
{ FOR| AFTER } LOGON    
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
  
<logon_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

SintaksIs Azure SQL Database

-- Azure SQL Database Syntax   
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  
  
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table | view }   
 [ WITH <dml_trigger_option> [ ,...n ] ]   
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
  AS { sql_statement  [ ; ] [ ,...n ] [ ; ] > }  
  
<dml_trigger_option> ::=   
        [ EXECUTE AS Clause ]

-- Azure SQL Database Syntax  
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, 
-- REVOKE, or UPDATE STATISTICS statement (DDL Trigger)   
  
CREATE [ OR ALTER ] TRIGGER trigger_name   
ON { DATABASE }   
 [ WITH <ddl_trigger_option> [ ,...n ] ]   
{ FOR | AFTER } { event_type | event_group } [ ,...n ]   
AS { sql_statement  [ ; ] [ ,...n ]  [ ; ] }  
  
<ddl_trigger_option> ::=   
    [ EXECUTE AS Clause ]

Argumen

ATAU UBAH
Berlaku untuk: Azure SQL Database, SQL Server (dimulai dengan SQL Server 2016 (13.x) SP1).

Secara kondisional mengubah pemicu hanya jika sudah ada.

schema_name
Nama skema tempat pemicu DML berada. Pemicu DML dicakup ke skema tabel atau tampilan tempat pemicu dibuat. schema_name tidak dapat ditentukan untuk pemicu DDL atau masuk.

trigger_name
Nama pemicu. trigger_name harus mengikuti aturan untuk pengidentifikasi, kecuali bahwa trigger_name tidak dapat dimulai dengan # atau ##.

tampilan tabel |
Tabel atau tampilan tempat pemicu DML berjalan. Tabel atau tampilan ini terkadang disebut sebagai tabel pemicu atau tampilan pemicu. Menentukan nama tabel atau tampilan yang sepenuhnya memenuhi syarat bersifat opsional. Anda hanya dapat mereferensikan tampilan dengan BUKAN pemicu. Anda tidak dapat menentukan pemicu DML pada tabel sementara lokal atau global.

DATABASE
Menerapkan cakupan pemicu DDL ke database saat ini. Jika ditentukan, pemicu diaktifkan setiap kali event_type atau event_group terjadi dalam database saat ini.

SEMUA SERVER
Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.

Menerapkan cakupan pemicu DDL atau masuk ke server saat ini. Jika ditentukan, pemicu diaktifkan setiap kali event_type atau event_group terjadi di mana saja di server saat ini.

DENGAN ENKRIPSI
Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.

Mengaburkan teks pernyataan CREATE TRIGGER. Menggunakan WITH ENCRYPTION mencegah pemicu diterbitkan sebagai bagian dari replikasi SQL Server. DENGAN ENKRIPSI tidak dapat ditentukan untuk pemicu CLR.

JALANKAN SEBAGAI
Menentukan konteks keamanan tempat pemicu dijalankan. Memungkinkan Anda mengontrol akun pengguna mana yang digunakan instans SQL Server untuk memvalidasi izin pada objek database apa pun yang dirujuk oleh pemicu.

Opsi ini diperlukan untuk pemicu pada tabel yang dioptimalkan memori.

Untuk informasi selengkapnya, lihat EXECUTE AS Clause (Transact-SQL).

NATIVE_COMPILATION
Menunjukkan bahwa pemicu dikompilasi secara asli.

Opsi ini diperlukan untuk pemicu pada tabel yang dioptimalkan memori.

SCHEMABINDING
Memastikan bahwa tabel yang direferensikan oleh pemicu tidak dapat dihilangkan atau diubah.

Opsi ini diperlukan untuk pemicu pada tabel yang dioptimalkan memori dan tidak didukung untuk pemicu pada tabel tradisional.

UNTUK | SESUDAH
FOR atau AFTER menentukan bahwa pemicu DML hanya diaktifkan ketika semua operasi yang ditentukan dalam pernyataan SQL pemicu telah berhasil diluncurkan. Semua tindakan kaskade referensial dan pemeriksaan batasan juga harus berhasil sebelum pemicu ini diaktifkan.

Anda tidak dapat menentukan pemicu AFTER pada tampilan.

MELAINKAN
Menentukan bahwa pemicu DML diluncurkan alih-alih pernyataan SQL pemicu, dengan demikian, mengesampingkan tindakan pernyataan pemicu. Anda tidak dapat menentukan ALIH-ALIH untuk pemicu DDL atau masuk.

Paling banyak, Anda dapat menentukan satu ALIH-ALIH pemicu per pernyataan INSERT, UPDATE, atau DELETE pada tabel atau tampilan. Anda juga dapat menentukan tampilan pada tampilan di mana setiap tampilan memiliki pemicu SENDIRI ALIH-ALIH.

Anda tidak dapat menentukan ALIH-ALIH pemicu pada tampilan yang dapat diperbarui yang menggunakan WITH CHECK OPTION. Melakukannya menghasilkan kesalahan saat pemicu ALIH-ALIH ditambahkan ke tampilan yang dapat diperbarui DENGAN OPSI PEMERIKSAAN yang ditentukan. Anda menghapus opsi tersebut dengan menggunakan ALTER VIEW sebelum mendefinisikan pemicu ALIH-ALIH.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
Menentukan pernyataan modifikasi data yang mengaktifkan pemicu DML saat dicoba terhadap tabel atau tampilan ini. Tentukan setidaknya satu opsi. Gunakan kombinasi opsi ini dalam urutan apa pun dalam definisi pemicu.

Untuk ALIH-ALIH pemicu, Anda tidak dapat menggunakan opsi DELETE pada tabel yang memiliki hubungan referensial, menentukan tindakan kaskade ON DELETE. Demikian pula, opsi UPDATE tidak diizinkan pada tabel yang memiliki hubungan referensial, menentukan tindakan kaskade ON UPDATE.

DENGAN TAMBAHKAN
Berlaku untuk: SQL Server 2008 (10.0.x) melalui SQL Server 2008 R2 (10.50.x).

Menentukan bahwa pemicu tambahan dari jenis yang ada harus ditambahkan. WITH APPEND tidak dapat digunakan dengan ALIH-ALIH pemicu atau jika pemicu AFTER dinyatakan secara eksplisit. Untuk kompatibilitas mundur, hanya gunakan WITH APPEND saat FOR ditentukan, tanpa ALIH-ALIH atau SESUDAH. Anda tidak dapat menentukan WITH APPEND jika menggunakan NAMA EKSTERNAL (yaitu, jika pemicunya adalah pemicu CLR).

event_type
Nama peristiwa bahasa Transact-SQL yang, setelah diluncurkan, menyebabkan pemicu DDL diaktifkan. Peristiwa yang valid untuk pemicu DDL tercantum dalam Peristiwa DDL.

event_group
Nama pengelompokan peristiwa bahasa Transact-SQL yang telah ditentukan sebelumnya. Pemicu DDL diaktifkan setelah peluncuran peristiwa bahasa Transact-SQL apa pun yang termasuk dalam event_group. Grup peristiwa yang valid untuk pemicu DDL tercantum dalam Grup Peristiwa DDL.

Setelah CREATE TRIGGER selesai berjalan, event_group juga bertindak sebagai makro dengan menambahkan jenis peristiwa yang dicakupnya ke tampilan katalog sys.trigger_events.

BUKAN UNTUK REPLIKASI
Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.

Menunjukkan bahwa pemicu tidak boleh dijalankan saat agen replikasi memodifikasi tabel yang terlibat dalam pemicu.

sql_statement
Kondisi dan tindakan pemicu. Kondisi pemicu menentukan kriteria tambahan yang menentukan apakah peristiwa DML, DDL, atau log masuk yang dicoba menyebabkan tindakan pemicu dijalankan.

Tindakan pemicu yang ditentukan dalam pernyataan Transact-SQL berlaku saat operasi dicoba.

Pemicu dapat mencakup sejumlah dan jenis pernyataan Transact-SQL, dengan pengecualian. Untuk informasi selengkapnya, lihat Keterangan. Pemicu dirancang untuk memeriksa atau mengubah data berdasarkan modifikasi data atau pernyataan definisi; seharusnya tidak mengembalikan data kepada pengguna. Pernyataan Transact-SQL dalam pemicu sering menyertakan bahasa kontrol aliran.

Pemicu DML menggunakan tabel logis (konseptual) yang dihapus dan disisipkan. Mereka secara struktural mirip dengan tabel tempat pemicu ditentukan, yaitu tabel tempat tindakan pengguna dicoba. Tabel yang dihapus dan disisipkan menyimpan nilai lama atau nilai baru baris yang dapat diubah oleh tindakan pengguna. Misalnya, untuk mengambil semua nilai dalam deleted tabel, gunakan:

SELECT * FROM deleted;

Untuk informasi selengkapnya, lihat Menggunakan Tabel yang disisipkan dan dihapus.

DDL dan pemicu masuk menangkap informasi tentang peristiwa pemicu dengan menggunakan fungsi EVENTDATA (Transact-SQL). Untuk informasi selengkapnya, lihat Menggunakan Fungsi EVENTDATA.

SQL Server memungkinkan pembaruan kolom teks, ntext, atau gambar melalui pemicu ALIH-ALIH pada tabel atau tampilan.

Penting

Jenis data ntext, teks, dan gambar akan dihapus dalam versi Microsoft SQL Server di masa mendatang. Hindari menggunakan jenis data ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakannya. Gunakan nvarchar(max), varchar(max), dan varbinary(max) sebagai gantinya. Pemicu AFTER dan INSTEAD OF mendukung data varchar(MAX), nvarchar(MAX), dan varbinary(MAX) dalam tabel yang disisipkan dan dihapus.

Untuk pemicu pada tabel yang dioptimalkan memori, satu-satunya sql_statement yang diizinkan di tingkat atas adalah blok ATOMIC. T-SQL yang diizinkan di dalam blok ATOMIC dibatasi oleh T-SQL yang diizinkan di dalam proc asli.

<>method_specifier Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.

Untuk pemicu CLR, menentukan metode assembly untuk mengikat dengan pemicu. Metode tidak boleh mengambil argumen dan mengembalikan kekosongan. class_name harus merupakan pengidentifikasi SQL Server yang valid dan harus ada sebagai kelas di rakitan dengan visibilitas perakitan. Jika kelas memiliki nama yang memenuhi syarat namespace yang menggunakan '.' untuk memisahkan bagian namespace, nama kelas harus dibatasi dengan menggunakan pemisah [ ] atau " " . Kelas tidak dapat menjadi kelas berlapis.

Catatan

Secara default, kemampuan SQL Server untuk menjalankan kode CLR nonaktif. Anda dapat membuat, memodifikasi, dan menghilangkan objek database yang mereferensikan modul kode terkelola, tetapi referensi ini tidak berjalan dalam instans SQL Server kecuali Opsi yang diaktifkan clr diaktifkan dengan menggunakan sp_configure.

Keterangan untuk Pemicu DML

Pemicu DML sering digunakan untuk memberlakukan aturan bisnis dan integritas data. SQL Server menyediakan integritas referensial deklaratif (DRI) melalui pernyataan ALTER TABLE dan CREATE TABLE. Namun, DRI tidak menyediakan integritas referensial lintas database. Integritas referensial mengacu pada aturan tentang hubungan antara kunci utama dan asing tabel. Untuk menerapkan integritas referensial, gunakan batasan KUNCI PRIMER dan KUNCI ASING dalam ALTER TABLE dan CREATE TABLE. Jika batasan ada pada tabel pemicu, batasan akan diperiksa setelah pemicu ALIH-ALIH berjalan dan sebelum pemicu AFTER berjalan. Jika batasan dilanggar, tindakan ALIH-ALIH pemicu digulung balik dan pemicu AFTER tidak diaktifkan.

Anda dapat menentukan pemicu AFTER pertama dan terakhir yang akan dijalankan pada tabel dengan menggunakan sp_settriggerorder. Anda hanya dapat menentukan satu pemicu AFTER pertama dan satu terakhir untuk setiap operasi INSERT, UPDATE, dan DELETE pada tabel. Jika ada pemicu AFTER lainnya pada tabel yang sama, pemicu tersebut dijalankan secara acak.

Jika pernyataan ALTER TRIGGER mengubah pemicu pertama atau terakhir, atribut pertama atau terakhir yang diatur pada pemicu yang dimodifikasi dihilangkan, dan Anda harus mengatur ulang nilai pesanan dengan menggunakan sp_settriggerorder.

Pemicu AFTER dijalankan hanya setelah pernyataan SQL pemicu berhasil dijalankan. Eksekusi yang berhasil ini mencakup semua tindakan kaskade referensial dan pemeriksaan batasan yang terkait dengan objek yang diperbarui atau dihapus. AFTER tidak secara rekursif menembakkan pemicu ALIH-ALIH pada tabel yang sama.

Jika pemicu ALIH-ALIH ditentukan pada tabel menjalankan pernyataan terhadap tabel yang biasanya akan menembakkan pemicu ALIH-ALIH lagi, pemicu tidak disebut secara rekursif. Sebaliknya, pernyataan memproses seolah-olah tabel tidak memiliki pemicu ALIH-ALIH dan memulai rantai operasi batasan dan setelah eksekusi pemicu. Misalnya, jika pemicu didefinisikan sebagai pemicu ALIH-ALIH INSERT untuk tabel. Dan, pemicu menjalankan pernyataan INSERT pada tabel yang sama, pernyataan INSERT yang diluncurkan oleh pemicu ALIH-ALIH TIDAK memanggil pemicu lagi. INSERT yang diluncurkan oleh pemicu memulai proses menjalankan tindakan batasan dan menembakkan pemicu AFTER INSERT yang ditentukan untuk tabel.

Ketika pemicu ALIH-ALIH didefinisikan pada tampilan menjalankan pernyataan terhadap tampilan yang biasanya akan menembakkan pemicu ALIH-ALIH LAGI, itu tidak disebut secara rekursif. Sebaliknya, pernyataan diselesaikan sebagai modifikasi terhadap tabel dasar yang mendasar tampilan. Dalam hal ini, definisi tampilan harus memenuhi semua batasan untuk tampilan yang dapat diperbarui. Untuk definisi tampilan yang dapat diperbarui, lihat Mengubah Data Melalui Tampilan.

Misalnya, jika pemicu didefinisikan sebagai pemicu ALIH-ALIH PEMBARUAN untuk tampilan. Dan, pemicu menjalankan pernyataan UPDATE yang merujuk pada tampilan yang sama, pernyataan UPDATE yang diluncurkan oleh pemicu ALIH-ALIH TIDAK memanggil pemicu lagi. PEMBARUAN yang diluncurkan oleh pemicu diproses terhadap tampilan seolah-olah tampilan tidak memiliki pemicu ALIH-ALIH. Kolom yang diubah oleh UPDATE harus diselesaikan ke satu tabel dasar. Setiap modifikasi pada tabel dasar yang mendasar memulai rantai penerapan batasan dan pemicu AFTER yang ditentukan untuk tabel.

Pengujian untuk TINDAKAN UPDATE atau INSERT ke Kolom Tertentu

Anda dapat merancang pemicu Transact-SQL untuk melakukan tindakan tertentu berdasarkan modifikasi UPDATE atau INSERT ke kolom tertentu. Gunakan UPDATE() atau COLUMNS_UPDATED dalam isi pemicu untuk tujuan ini. UJI UPDATE() untuk upaya UPDATE atau INSERT pada satu kolom. COLUMNS_UPDATED pengujian untuk tindakan PERBARUI atau SISIPKAN yang berjalan pada beberapa kolom. Fungsi ini mengembalikan pola bit yang menunjukkan kolom mana yang disisipkan atau diperbarui.

Batasan Pemicu

CREATE TRIGGER harus menjadi pernyataan pertama dalam batch dan hanya dapat berlaku untuk satu tabel.

Pemicu dibuat hanya di database saat ini; namun, pemicu dapat mereferensikan objek di luar database saat ini.

Jika nama skema pemicu ditentukan untuk memenuhi syarat pemicu, kualifikasikan nama tabel dengan cara yang sama.

Tindakan pemicu yang sama dapat ditentukan untuk lebih dari satu tindakan pengguna (misalnya, INSERT dan UPDATE) dalam pernyataan CREATE TRIGGER yang sama.

ALIH-ALIH pemicu DELETE/UPDATE tidak dapat ditentukan pada tabel yang memiliki kunci asing dengan kaskade pada tindakan DELETE/UPDATE yang ditentukan.

Pernyataan SET apa pun dapat ditentukan di dalam pemicu. Opsi SET yang dipilih tetap berlaku selama eksekusi pemicu lalu kembali ke pengaturan sebelumnya.

Ketika pemicu diaktifkan, hasil dikembalikan ke aplikasi panggilan, sama seperti prosedur tersimpan. Untuk mencegah hasil dikembalikan ke aplikasi karena pemicu diaktifkan, jangan sertakan pernyataan SELECT yang mengembalikan hasil atau pernyataan yang melakukan penetapan variabel dalam pemicu. Pemicu yang mencakup pernyataan SELECT yang mengembalikan hasil kepada pengguna atau pernyataan yang melakukan penetapan variabel, memerlukan penanganan khusus. Anda harus menulis hasil yang dikembalikan ke setiap aplikasi di mana modifikasi pada tabel pemicu diizinkan. Jika penetapan variabel harus terjadi dalam pemicu, gunakan pernyataan SET NOCOUNT di awal pemicu untuk mencegah pengembalian kumpulan hasil apa pun.

Meskipun pernyataan TRUNCATE TABLE berlaku pada pernyataan DELETE, pernyataan tersebut tidak mengaktifkan pemicu karena operasi tidak mencatat penghapusan baris individual. Namun, hanya pengguna dengan izin untuk menjalankan pernyataan TRUNCATE TABLE yang perlu dikhawatirkan secara tidak sengaja menghindari pemicu DELETE dengan cara ini.

Pernyataan WRITETEXT, baik yang dicatat atau tidak di-log, tidak mengaktifkan pemicu.

Pernyataan Transact-SQL berikut tidak diizinkan dalam pemicu DML:

ALTER DATABASE
BUAT DATABASE
JATUHKAN DATABASE
PULIHKAN DATABASE
PULIHKAN LOG
KONFIGURASI ULANG

Selain itu, pernyataan Transact-SQL berikut tidak diizinkan di dalam isi pemicu DML saat digunakan terhadap tabel atau tampilan yang menjadi target tindakan pemicu.

BUAT INDEKS (termasuk BUAT INDEKS SPASIAL dan BUAT INDEKS XML)
ALTER INDEX
DROP INDEX
DROP TABLE
DBCC DBREINDEX
ALTER PARTITION FUNCTION
UBAH TABEL saat digunakan untuk melakukan hal berikut:
- Tambahkan, ubah, atau letakkan kolom.
- Beralih partisi.
- Menambahkan atau menghilangkan batasan KUNCI PRIMER atau UNIK.

Catatan

Karena SQL Server tidak mendukung pemicu yang ditentukan pengguna pada tabel sistem, kami sarankan Anda tidak membuat pemicu yang ditentukan pengguna pada tabel sistem.

Mengoptimalkan Pemicu DML

Pemicu bekerja dalam transaksi (tersirat atau sebaliknya) dan saat terbuka, pemicu mengunci sumber daya. Kunci tetap di tempat sampai transaksi dikonfirmasi (dengan COMMIT) atau ditolak (dengan ROLLBACK). Semakin lama pemicu berjalan, semakin tinggi probabilitas bahwa proses lain kemudian diblokir. Jadi, tulis pemicu untuk mengurangi durasi mereka jika memungkinkan. Salah satu cara untuk mencapai durasi yang lebih pendek adalah dengan merilis pemicu saat pernyataan DML mengubah baris nol.

Untuk merilis pemicu untuk perintah yang tidak mengubah baris apa pun, gunakan variabel sistem ROWCOUNT_BIG.

Cuplikan kode T-SQL berikut menunjukkan cara merilis pemicu untuk perintah yang tidak mengubah baris apa pun. Kode ini harus ada di awal setiap pemicu DML:

IF (ROWCOUNT_BIG() = 0)
RETURN;

Keterangan untuk Pemicu DDL

Pemicu DDL, seperti pemicu standar, meluncurkan prosedur tersimpan sebagai respons terhadap suatu peristiwa. Tetapi, tidak seperti pemicu standar, mereka tidak berjalan sebagai respons terhadap pernyataan UPDATE, INSERT, atau DELETE pada tabel atau tampilan. Sebaliknya, mereka terutama berjalan sebagai respons terhadap pernyataan bahasa definisi data (DDL). Jenis pernyataan termasuk CREATE, ALTER, DROP, GRANT, DENY, REVOKE, dan UPDATE STATISTICS. Prosedur tersimpan sistem tertentu yang melakukan operasi seperti DDL juga dapat mengaktifkan pemicu DDL.

Penting

Uji pemicu DDL Anda untuk menentukan responsnya terhadap eksekusi prosedur tersimpan sistem. Misalnya, pernyataan CREATE TYPE dan prosedur tersimpan sp_addtype dan sp_rename mengaktifkan pemicu DDL yang dibuat pada peristiwa CREATE_TYPE.

Untuk informasi selengkapnya tentang pemicu DDL, lihat Pemicu DDL.

Pemicu DDL tidak diaktifkan sebagai respons terhadap peristiwa yang memengaruhi tabel sementara lokal atau global dan prosedur tersimpan.

Tidak seperti pemicu DML, pemicu DDL tidak terlingkup ke skema. Jadi, Anda tidak dapat menggunakan fungsi seperti OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY, dan OBJECTPROPERTYEX untuk mengkueri metadata tentang pemicu DDL. Gunakan tampilan katalog sebagai gantinya. Untuk informasi selengkapnya, lihat Mendapatkan Informasi Tentang Pemicu DDL.

Catatan

Pemicu DDL cakupan server muncul di SQL Server Management Studio Object Explorer di folder Pemicu . Folder ini terletak di bawah folder Objek Server. Pemicu DDL lingkup database muncul di folder Pemicu Database. Folder ini terletak di bawah folder Programmability dari database yang sesuai.

Pemicu Masuk

Pemicu masuk melakukan prosedur tersimpan sebagai respons terhadap peristiwa LOGON. Kejadian ini terjadi ketika sesi pengguna dibuat dengan instans SQL Server. Pemicu masuk diaktifkan setelah fase autentikasi pengelogan selesai, tetapi sebelum sesi pengguna dibuat. Jadi, semua pesan yang berasal dari dalam pemicu yang biasanya akan menjangkau pengguna, seperti pesan kesalahan dan pesan dari pernyataan PRINT, dialihkan ke log kesalahan SQL Server. Untuk informasi selengkapnya, lihat Pemicu Masuk.

Pemicu masuk tidak diaktifkan jika autentikasi gagal.

Transaksi terdistribusi tidak didukung dalam pemicu masuk. Kesalahan 3969 kembali saat pemicu masuk yang berisi kebakaran transaksi terdistribusi.

Menonaktifkan Pemicu Masuk

Pemicu masuk dapat secara efektif mencegah keberhasilan koneksi ke Mesin Database untuk semua pengguna, termasuk anggota peran server tetap sysadmin . Ketika pemicu masuk mencegah koneksi, anggota peran server tetap sysadmin dapat terhubung dengan menggunakan koneksi administrator khusus, atau dengan memulai Mesin Database dalam mode konfigurasi minimal (-f). Untuk informasi selengkapnya, lihat Opsi Startup Layanan Mesin Database.

Pertimbangan Pemicu Umum

Mengembalikan Hasil

Kemampuan untuk mengembalikan hasil dari pemicu akan dihapus dalam versi SQL Server di masa mendatang. Pemicu yang mengembalikan tataan hasil dapat menyebabkan perilaku tak terduga dalam aplikasi yang tidak dirancang untuk bekerja dengannya. Hindari mengembalikan tataan hasil dari pemicu dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini dilakukan. Untuk mencegah pemicu mengembalikan tataan hasil, atur opsi larang hasil dari pemicu ke 1.

Pemicu masuk selalu melarang pengembalian kumpulan hasil dan perilaku ini tidak dapat dikonfigurasi. Jika pemicu masuk menghasilkan kumpulan hasil, pemicu gagal diluncurkan dan upaya masuk yang mengaktifkan pemicu ditolak.

Beberapa Pemicu

SQL Server memungkinkan Anda membuat beberapa pemicu untuk setiap peristiwa DML, DDL, atau LOGON. Misalnya, jika CREATE TRIGGER FOR UPDATE dijalankan untuk tabel yang sudah memiliki pemicu UPDATE, pemicu pembaruan tambahan dibuat. Dalam versi SQL Server yang lebih lama, hanya satu pemicu untuk setiap peristiwa modifikasi data INSERT, UPDATE, atau DELETE yang diizinkan untuk setiap tabel.

Pemicu Berulang

SQL Server juga mendukung pemanggilan pemicu rekursif saat pengaturan RECURSIVE_TRIGGERS diaktifkan menggunakan ALTER DATABASE.

Pemicu rekursif memungkinkan jenis rekursi berikut terjadi:

Rekursi tidak langsung

Dengan rekursi tidak langsung, aplikasi memperbarui tabel T1. Ini akan memicu TR1, memperbarui tabel T2. Pemicu T2 kemudian mengaktifkan dan memperbarui tabel T1.
Rekursi langsung

Dalam rekursi langsung, aplikasi memperbarui tabel T1. Ini akan memicu TR1, memperbarui tabel T1. Karena tabel T1 diperbarui, pemicu TR1 diaktifkan lagi, dan sebagainya.

Contoh berikut menggunakan rekursi pemicu tidak langsung dan langsung Asumsikan bahwa dua pemicu pembaruan, TR1 dan TR2, didefinisikan pada tabel T1. Picu TR1 memperbarui tabel T1 secara rekursif. Pernyataan UPDATE menjalankan setiap TR1 dan TR2 satu kali. Selain itu, peluncuran TR1 memicu eksekusi TR1 (secara rekursif) dan TR2. Tabel yang disisipkan dan dihapus untuk pemicu tertentu berisi baris yang hanya sesuai dengan pernyataan UPDATE yang memanggil pemicu.

Catatan

Perilaku sebelumnya hanya terjadi jika pengaturan RECURSIVE_TRIGGERS diaktifkan dengan menggunakan ALTER DATABASE. Tidak ada urutan yang ditentukan di mana beberapa pemicu yang ditentukan untuk peristiwa tertentu dijalankan. Setiap pemicu harus mandiri.

Menonaktifkan pengaturan RECURSIVE_TRIGGERS hanya mencegah rekursi langsung. Untuk menonaktifkan rekursi tidak langsung juga, atur opsi server pemicu berlapis ke 0 dengan menggunakan sp_configure.

Jika salah satu pemicu melakukan TRANSAKSI ROLLBACK, terlepas dari tingkat berlapis, tidak ada lagi pemicu yang dijalankan.

Pemicu Berlapis

Anda dapat menumpuk pemicu ke maksimum 32 tingkat. Jika pemicu mengubah tabel di mana ada pemicu lain, pemicu kedua diaktifkan dan kemudian dapat memanggil pemicu ketiga, dan sebagainya. Jika ada pemicu dalam rantai yang menetapkan perulangan tak terbatas, tingkat berlapis terlampaui dan pemicu dibatalkan. Saat pemicu Transact-SQL meluncurkan kode terkelola dengan merujuk rutinitas CLR, jenis, atau agregat, referensi ini dihitung sebagai satu tingkat terhadap batas berlapis 32 tingkat. Metode yang dipanggil dari dalam kode terkelola tidak dihitung terhadap batas ini.

Untuk menonaktifkan pemicu berlapis, atur opsi pemicu berlapis sp_configure ke 0 (nonaktif). Konfigurasi default mendukung pemicu berlapis. Jika pemicu berlapis nonaktif, pemicu rekursif juga dinonaktifkan, meskipun pengaturan RECURSIVE_TRIGGERS yang diatur dengan menggunakan ALTER DATABASE.

Pemicu AFTER pertama yang berlapis di dalam pemicu ALIH-ALIH diaktifkan bahkan jika opsi konfigurasi server pemicu berlapis adalah 0. Tapi, di bawah pengaturan ini, pemicu AFTER nanti tidak diaktifkan. Tinjau aplikasi Anda untuk pemicu berlapis untuk menentukan apakah aplikasi mengikuti aturan bisnis Anda saat opsi konfigurasi server pemicu berlapis diatur ke 0. Jika tidak, buat modifikasi yang sesuai.

Resolusi Nama yang Ditangguhkan

SQL Server memungkinkan prosedur, pemicu, dan batch tersimpan Transact-SQL untuk merujuk ke tabel yang tidak ada pada waktu kompilasi. Kemampuan ini disebut resolusi nama yang ditangguhkan.

Izin

Untuk membuat pemicu DML, pemicu memerlukan izin ALTER pada tabel atau tampilan tempat pemicu sedang dibuat.

Untuk membuat pemicu DDL dengan cakupan server (ON ALL SERVER) atau pemicu masuk, memerlukan izin CONTROL SERVER di server. Untuk membuat pemicu DDL dengan cakupan database (ON DATABASE), memerlukan izin UBAH PEMICU DDL DATABASE APA PUN dalam database saat ini.

Contoh

J. Menggunakan pemicu DML dengan pesan pengingat

Pemicu DML berikut mencetak pesan ke klien saat siapa pun mencoba menambahkan atau mengubah data dalam Customer tabel dalam database AdventureWorks2022.

CREATE TRIGGER reminder1  
ON Sales.Customer  
AFTER INSERT, UPDATE   
AS RAISERROR ('Notify Customer Relations', 16, 10);  
GO

B. Menggunakan pemicu DML dengan pesan email pengingat

Contoh berikut mengirimkan pesan email ke orang tertentu (MaryM) saat Customer tabel berubah.

CREATE TRIGGER reminder2  
ON Sales.Customer  
AFTER INSERT, UPDATE, DELETE   
AS  
   EXEC msdb.dbo.sp_send_dbmail  
        @profile_name = 'AdventureWorks2022 Administrator',  
        @recipients = 'danw@Adventure-Works.com',  
        @body = 'Don''t forget to print a report for the sales force.',  
        @subject = 'Reminder';  
GO

C. Menggunakan pemicu DML AFTER untuk menerapkan aturan bisnis antara tabel PurchaseOrderHeader dan Vendor

Karena batasan CHECK hanya mereferensikan kolom di mana batasan tingkat kolom atau tingkat tabel ditentukan, Anda harus menentukan batasan lintas tabel (dalam hal ini, aturan bisnis) sebagai pemicu.

Contoh berikut membuat pemicu DML dalam AdventureWorks2022 database. Pemicu ini memeriksa untuk memastikan peringkat kredit untuk vendor baik (bukan 5) ketika ada upaya untuk memasukkan pesanan pembelian baru ke PurchaseOrderHeader dalam tabel. Untuk mendapatkan peringkat kredit vendor, Vendor tabel harus dirujuk. Jika peringkat kredit terlalu rendah, pesan muncul dan penyisipan tidak terjadi.

USE AdventureWorks2022;
GO
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).  
  
CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader  
AFTER INSERT  
AS  
IF (ROWCOUNT_BIG() = 0)
RETURN;
IF EXISTS (SELECT 1  
           FROM inserted AS i   
           JOIN Purchasing.Vendor AS v   
           ON v.BusinessEntityID = i.VendorID  
           WHERE v.CreditRating = 5  
          )  
BEGIN  
RAISERROR ('A vendor''s credit rating is too low to accept new  
purchase orders.', 16, 1);  
ROLLBACK TRANSACTION;  
RETURN   
END;  
GO  
  
-- This statement attempts to insert a row into the PurchaseOrderHeader table  
-- for a vendor that has a below average credit rating.  
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.  
  
INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,  
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)  
VALUES (  
2  
,3  
,261  
,1652  
,4  
,GETDATE()  
,GETDATE()  
,44594.55  
,3567.564  
,1114.8638 );  
GO

D. Menggunakan pemicu DDL dengan cakupan database

Contoh berikut menggunakan pemicu DDL untuk mencegah sinonim apa pun dalam database dihilangkan.

CREATE TRIGGER safety   
ON DATABASE   
FOR DROP_SYNONYM  
AS   
IF (@@ROWCOUNT = 0)
RETURN;
   RAISERROR ('You must disable Trigger "safety" to remove synonyms!', 10, 1)  
   ROLLBACK  
GO  
DROP TRIGGER safety  
ON DATABASE;  
GO

E. Menggunakan pemicu DDL cakupan server

Contoh berikut menggunakan pemicu DDL untuk mencetak pesan jika ada peristiwa CREATE DATABASE yang terjadi pada instans EVENTDATA server saat ini, dan menggunakan fungsi untuk mengambil teks pernyataan Transact-SQL yang sesuai. Untuk contoh selengkapnya yang menggunakan EVENTDATA di pemicu DDL, lihat Menggunakan Fungsi EVENTDATA.

Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.

CREATE TRIGGER ddl_trig_database   
ON ALL SERVER   
FOR CREATE_DATABASE   
AS   
    PRINT 'Database Created.'  
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  
GO  
DROP TRIGGER ddl_trig_database  
ON ALL SERVER;  
GO

F. Menggunakan pemicu masuk

Contoh pemicu masuk berikut menolak upaya untuk masuk ke SQL Server sebagai anggota login_test masuk jika sudah ada tiga sesi pengguna yang berjalan di bawah login tersebut.

Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.

USE master;  
GO  
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,  
    CHECK_EXPIRATION = ON;  
GO  
GRANT VIEW SERVER STATE TO login_test;  
GO  
CREATE TRIGGER connection_limit_trigger  
ON ALL SERVER WITH EXECUTE AS 'login_test'  
FOR LOGON  
AS  
BEGIN  
IF ORIGINAL_LOGIN()= 'login_test' AND  
    (SELECT COUNT(*) FROM sys.dm_exec_sessions  
            WHERE is_user_process = 1 AND  
                original_login_name = 'login_test') > 3  
    ROLLBACK;  
END;

G. Melihat peristiwa yang menyebabkan pemicu diaktifkan

Contoh berikut meminta sys.triggers tampilan katalog dan sys.trigger_events untuk menentukan peristiwa bahasa Transact-SQL mana yang menyebabkan pemicu safety diaktifkan. Pemicu, safety, dibuat dalam contoh 'D', ditemukan di atas.

SELECT TE.*  
FROM sys.trigger_events AS TE  
JOIN sys.triggers AS T ON T.object_id = TE.object_id  
WHERE T.parent_class = 0 AND T.name = 'safety';  
GO

Lihat Juga

ALTER TABLE (Transact-SQL)
UBAH PEMICU (Transact-SQL)
COLUMNS_UPDATED (T-SQL)
BUAT TABEL (Transact-SQL)
JATUHKAN PEMICU (Transact-SQL)
AKTIFKAN PEMICU (Transact-SQL)
NONAKTIFKAN PEMICU (Transact-SQL)
TRIGGER_NESTLEVEL (T-SQL)
EVENTDATA (Transact-SQL)
sys.dm_sql_referenced_entities (T-SQL)
sys.dm_sql_referencing_entities (T-SQL)
sys.sql_expression_dependencies (Transact-SQL)
sp_help (T-SQL)
sp_helptrigger (T-SQL)
sp_helptext (T-SQL)
sp_rename (T-SQL)
sp_settriggerorder (T-SQL)
UPDATE() (Transact-SQL)
Mendapatkan Informasi Tentang Pemicu DML
Mendapatkan Informasi Tentang Pemicu DDL
sys.triggers (Transact-SQL)
sys.trigger_events (T-SQL)
sys.sql_modules (Transact-SQL)
sys.assembly_modules (T-SQL)
sys.server_triggers (T-SQL)
sys.server_trigger_events (T-SQL)
sys.server_sql_modules (T-SQL)
sys.server_assembly_modules (T-SQL)

Bagikan melalui

BUAT PEMICU (Transact-SQL)

Sintaks SQL Server

SintaksIs Azure SQL Database

Argumen

Keterangan untuk Pemicu DML

Pengujian untuk TINDAKAN UPDATE atau INSERT ke Kolom Tertentu

Batasan Pemicu

Mengoptimalkan Pemicu DML

Keterangan untuk Pemicu DDL

Pemicu Masuk

Menonaktifkan Pemicu Masuk

Pertimbangan Pemicu Umum

Mengembalikan Hasil

Beberapa Pemicu

Pemicu Berulang

Pemicu Berlapis

Resolusi Nama yang Ditangguhkan

Izin

Contoh

J. Menggunakan pemicu DML dengan pesan pengingat

B. Menggunakan pemicu DML dengan pesan email pengingat

C. Menggunakan pemicu DML AFTER untuk menerapkan aturan bisnis antara tabel PurchaseOrderHeader dan Vendor

D. Menggunakan pemicu DDL dengan cakupan database

E. Menggunakan pemicu DDL cakupan server

F. Menggunakan pemicu masuk

G. Melihat peristiwa yang menyebabkan pemicu diaktifkan

Lihat Juga

Saran dan Komentar

Sumber Daya Tambahan: