Bagikan melalui

BUAT PEMICU (Transact-SQL)

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceDatabase SQL di Microsoft Fabric

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 INSERT, UPDATE, atau DELETE pernyataan 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 Transact-SQL CREATE, , ALTERdan DROP pernyataan, dan prosedur tersimpan sistem tertentu yang melakukan operasi seperti DDL.

Masuk memicu kebakaran sebagai respons terhadap LOGON peristiwa 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 atau database SQL di Microsoft Fabric.

Konvensi sintaks transact-SQL

Sintaksis

Sintaks SQL Server

Pemicu pada INSERTpernyataan , UPDATE, atau DELETE ke tabel atau tampilan (pemicu DML):

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

Pemicu pada INSERTpernyataan , UPDATE, atau DELETE ke tabel (pemicu DML pada tabel yang dioptimalkan memori):

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 ]

Pemicu pada CREATE, , ALTERDROPGRANT, , DENY, REVOKE, atau UPDATE pernyataan (pemicu DDL):

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 ]

Pemicu pada LOGON peristiwa (Pemicu masuk):

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 ]

Azure SQL Database atau database SQL dalam sintaks Fabric

Pemicu pada INSERTpernyataan , UPDATE, atau DELETE ke tabel atau tampilan (pemicu DML):

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 ]

Pemicu pada CREATE, , ALTERDROPGRANT, , DENY, REVOKE, atau UPDATE STATISTICS pernyataan (pemicu DDL):

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: SQL Server 2016 (13.x) SP1 dan versi yang lebih baru, Azure SQL Database, dan database SQL di Microsoft Fabric.

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 memulai 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 oleh pemicu INSTEAD OF . Anda tidak dapat menentukan pemicu DML pada tabel sementara lokal atau global.

BASIS DATA

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

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

Mengaburkan teks CREATE TRIGGER pernyataan. Menggunakan WITH ENCRYPTION mencegah pemicu diterbitkan sebagai bagian dari replikasi SQL Server. WITH ENCRYPTION 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 KLAUSUL EXECUTE AS.

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 AFTER pemicu pada tampilan.

MELAINKAN

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

Paling banyak, Anda dapat menentukan satu INSTEAD OF pemicu per INSERT, UPDATE, atau DELETE pernyataan pada tabel atau tampilan. Anda juga dapat menentukan tampilan pada tampilan di mana setiap tampilan memiliki pemicunya sendiri INSTEAD OF .

Anda tidak dapat menentukan INSTEAD OF pemicu pada tampilan yang dapat diperbarui yang menggunakan WITH CHECK OPTION. Melakukannya menghasilkan kesalahan saat pemicu INSTEAD OF ditambahkan ke tampilan WITH CHECK OPTION yang dapat diperbarui yang ditentukan. Anda menghapus opsi tersebut dengan menggunakan ALTER VIEW sebelum menentukan pemicu INSTEAD OF .

{ [ HAPUS ] [ , ] [ SISIPKAN ] [ , ] [ PERBARUI ] }

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 INSTEAD OF pemicu, Anda tidak dapat menggunakan DELETE opsi pada tabel yang memiliki hubungan referensial, menentukan tindakan ON DELETEbertingkat . Demikian pula, UPDATE opsi tidak diizinkan pada tabel yang memiliki hubungan referensial, menentukan tindakan ON UPDATEbertingkat .

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 INSTEAD OF pemicu atau jika pemicu AFTER dinyatakan secara eksplisit. Untuk kompatibilitas mundur, hanya gunakan WITH APPEND saat FOR ditentukan, tanpa INSTEAD OF atau AFTER. Anda tidak dapat menentukan WITH APPEND apakah menggunakan EXTERNAL NAME (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 selesai berjalan, CREATE TRIGGER juga bertindak sebagai makro dengan menambahkan jenis peristiwa yang dicakupnya ke sys.trigger_events tampilan katalog.

BUKAN UNTUK REPLIKASI

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. Pemicu tidak boleh 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 mungkin 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 . Untuk informasi selengkapnya, lihat Menggunakan Fungsi EVENTDATA.

SQL Server memungkinkan pembaruan kolom teks, ntext, atau gambar melalui INSTEAD OF pemicu 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. Baik AFTER dan INSTEAD OF pemicu 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 ATOMIC blok. T-SQL yang diizinkan di dalam ATOMIC blok dibatasi oleh T-SQL yang diizinkan di dalam proc asli.

<method_specifier>

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 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 ALTER TABLE pernyataan 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 PRIMARY KEY dan FOREIGN KEY di ALTER TABLE dan CREATE TABLE. Jika batasan ada pada tabel pemicu, batasan akan diperiksa setelah pemicu INSTEAD OF berjalan dan sebelum pemicu AFTER berjalan. Jika batasan dilanggar, INSTEAD OF tindakan pemicu digulung balik dan pemicu AFTER tidak diaktifkan.

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

Jika pernyataan mengubah pemicu pertama atau terakhir, atribut pertama atau terakhir yang ALTER TRIGGER ditetapkan 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 INSTEAD OF pemicu pada tabel yang sama.

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

Ketika pemicu yang INSTEAD OF ditentukan pada tampilan menjalankan pernyataan terhadap tampilan yang biasanya akan menembakkan INSTEAD OF pemicu lagi, pemicu tersebut 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 INSTEAD OF UPDATE pemicu untuk tampilan. Dan, pemicu menjalankan pernyataan yang UPDATE mereferensikan tampilan yang sama, pernyataan yang UPDATE diluncurkan oleh INSTEAD OF pemicu tidak memanggil pemicu lagi. Diluncurkan UPDATE oleh pemicu diproses terhadap tampilan seolah-olah tampilan tidak memiliki INSTEAD OF pemicu. Kolom yang diubah oleh UPDATE harus diselesaikan ke satu tabel dasar. Setiap modifikasi pada tabel dasar yang mendasar memulai rantai penerapan batasan dan pemicu penembakan AFTER yang ditentukan untuk tabel.

Menguji tindakan UPDATE atau INSERT ke kolom tertentu

Anda dapat merancang pemicu Transact-SQL untuk melakukan tindakan tertentu berdasarkan UPDATE atau INSERT modifikasi pada kolom tertentu. Gunakan UPDATE atau COLUMNS_UPDATED dalam isi pemicu untuk tujuan ini. UPDATE() pengujian untuk UPDATE atau INSERT upaya pada satu kolom. COLUMNS_UPDATED pengujian untuk UPDATE atau INSERT tindakan 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 didefinisikan untuk lebih dari satu tindakan pengguna (misalnya, INSERT dan UPDATE) dalam pernyataan yang sama CREATE TRIGGER .

INSTEAD OF DELETE / INSTEAD OF UPDATE pemicu tidak dapat didefinisikan pada tabel yang memiliki kunci asing dengan kaskade pada DELETE/UPDATE tindakan 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 salah satu SELECT pernyataan 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 SET NOCOUNT pernyataan di awal pemicu untuk mencegah pengembalian kumpulan hasil apa pun.

TRUNCATE TABLE Meskipun pernyataan berlaku sebagai DELETE pernyataan, pernyataan tersebut tidak mengaktifkan pemicu karena operasi tidak mencatat penghapusan baris individual. Namun, hanya pengguna dengan izin untuk menjalankan pernyataan yang TRUNCATE TABLE perlu dikhawatirkan secara tidak sengaja menghindari pemicu dengan DELETE 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
  • CREATE DATABASE
  • DROP DATABASE
  • RESTORE DATABASE
  • RESTORE LOG
  • RECONFIGURE

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

  • CREATE INDEX (termasuk CREATE SPATIAL INDEX dan CREATE XML INDEX)
  • ALTER INDEX
  • DROP INDEX
  • DROP TABLE
  • DBCC DBREINDEX
  • ALTER PARTITION FUNCTION
  • ALTER TABLE ketika digunakan untuk melakukan tindakan berikut:
    • Tambahkan, ubah, atau letakkan kolom.
    • Beralih partisi.
    • Menambahkan atau menghilangkan PRIMARY KEY atau UNIQUE membatasi.

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 UPDATE, , INSERTatau DELETE pernyataan pada tabel atau tampilan. Sebaliknya, mereka terutama berjalan sebagai respons terhadap pernyataan bahasa definisi data (DDL). Jenis pernyataan termasuk CREATE, , ALTERDROP, 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, CREATE TYPE pernyataan dan sp_addtype prosedur dan sp_rename tersimpan 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_NAMEOBJECTPROPERTY, 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 yang terbatas pada 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 suatu LOGON peristiwa. 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 PRINT pernyataan, 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 Database Engine Service.

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 DML, DDL, atau LOGON peristiwa. Misalnya, jika CREATE TRIGGER FOR UPDATE dijalankan untuk tabel yang sudah memiliki UPDATE pemicu, pemicu pembaruan tambahan dibuat. Dalam versi SQL Server yang lebih lama, hanya satu pemicu untuk setiap INSERT, , UPDATEatau DELETE peristiwa modifikasi data yang diizinkan untuk setiap tabel.

Pemicu rekursif

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

Pemicu rekursif memungkinkan jenis rekursi berikut terjadi:

  • Rekursi tidak langsung: Dengan rekursi tidak langsung, tabel T1pembaruan aplikasi . Ini mengaktifkan pemicu TR1, memperbarui tabel T2. Pemicu T2 kemudian mengaktifkan dan memperbarui tabel T1.

  • Rekursi langsung: Dalam rekursi langsung, tabel T1pembaruan aplikasi . Ini mengaktifkan pemicu 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, ditentukan pada tabel T1. Picu TR1 pembaruan tabel T1 secara rekursif. Pernyataan UPDATE berjalan 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 UPDATE sesuai dengan pernyataan yang memanggil pemicu.

Catatan

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

RECURSIVE_TRIGGERS Menonaktifkan pengaturan 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 ROLLBACK TRANSACTION, 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 ke sp_configure 0 (nonaktif). Konfigurasi default mendukung pemicu berlapis. Jika pemicu berlapis nonaktif, pemicu rekursif juga dinonaktifkan, meskipun RECURSIVE_TRIGGERS pengaturan yang diatur dengan menggunakan ALTER DATABASE.

Pemicu pertama AFTER yang INSTEAD OF berlapis di dalam pemicu diaktifkan bahkan jika opsi konfigurasi server pemicu berlapis adalah 0. Tapi, di bawah pengaturan ini, pemicu selanjutnya AFTER 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 Transact-SQL prosedur tersimpan, pemicu, fungsi, dan batch untuk merujuk ke tabel yang tidak ada pada waktu kompilasi. Kemampuan ini disebut resolusi nama yang ditangguhkan.

Izin

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

Untuk membuat pemicu DDL dengan cakupan server (ON ALL SERVER) atau pemicu masuk, memerlukan CONTROL SERVER izin di server. Untuk membuat pemicu DDL dengan cakupan database (ON DATABASE), memerlukan ALTER ANY DATABASE DDL TRIGGER izin dalam database saat ini.

Contoh

J. Menggunakan pemicu DML dengan pesan pengingat

Pemicu DML berikut mencetak pesan ke klien ketika ada yang mencoba menambahkan atau mengubah data dalam Customer tabel dalam database AdventureWorks2025.

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
    EXECUTE 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. Gunakan pemicu DML AFTER untuk menerapkan aturan bisnis antara tabel PurchaseOrderHeader dan Vendor

Karena CHECK batasan 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 AdventureWorks2025 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
        INNER 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;
    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 lingkup 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 CREATE DATABASE peristiwa yang terjadi pada instans EVENTDATA server saat ini, dan menggunakan fungsi untuk mengambil teks pernyataan Transact-SQL yang sesuai. Untuk contoh selengkapnya yang digunakan EVENTDATA dalam pemicu DDL, lihat Menggunakan Fungsi EVENTDATA.

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 jika sudah ada tiga sesi pengguna yang berjalan di bawah login tersebut login_test . Ubah <password> ke kata sandi yang kuat.

USE master;
GO

CREATE LOGIN login_test
    WITH PASSWORD = '<password>' 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. Gunakan pemicu DDL lingkup database.

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

Konten terkait

  • Last updated on