Keterbatasan pemicu yang diketahui

Membangun dan Mengoptimalkan Trigger di SQL Server

Florin Angelescu

Instructor

Keunggulan pemicu

Untuk integritas basis data
Terapkan aturan bisnis langsung di basis data
Kendalikan pernyataan yang diizinkan di basis data
Jalankan logika bisnis kompleks dipicu oleh satu peristiwa
Cara sederhana mengaudit basis data dan tindakan pengguna

Kekurangan pemicu

Sulit dilihat dan dideteksi
Tidak terlihat oleh aplikasi klien atau saat debug kode
Sulit diikuti logikanya saat pemecahan masalah
Dapat menambah beban server dan memperlambatnya

Menemukan pemicu tingkat server

SELECT * FROM sys.server_triggers;

| name                 | parent_class_desc | create_date | is_disabled |...|
|----------------------|-------------------|-------------|-------------|---|
| DenyNewDatabases     | SERVER            | 2019-01-22  | 0           |...|
| DenyNewLinkedServers | SERVER            | 2019-01-22  | 1           |...|
| DenyDatabaseDelete   | SERVER            | 2019-01-22  | 1           |...|

Menemukan pemicu basis data dan tabel

SELECT * FROM sys.triggers;

| name                   | parent_class_desc | create_date | is_disabled | is_instead_of_trigger |
|----------------------|-------------------|-------------|---------------|-----------------------|
| TrackRetiredProducts   | OBJECT_OR_COLUMN  | 2019-04-19  | 0           | 0                     |
| PreventOrdersUpdate    | OBJECT_OR_COLUMN  | 2019-04-22  | 0           | 1                     |
| TrackDatabaseTables    | DATABASE          | 2019-04-23  | 0           | 0                     |
| KeepCanceledOrders     | OBJECT_OR_COLUMN  | 2019-04-27  | 0           | 0                     |
| DiscountsPreventDelete | OBJECT_OR_COLUMN  | 2019-04-27  | 0           | 1                     |
| PreventNewDiscounts    | OBJECT_OR_COLUMN  | 2019-04-27  | 0           | 1                     |
| PreventTableDeletion   | DATABASE          | 2019-04-27  | 0           | 0                     |

Melihat definisi pemicu (opsi 1)

Lihat definisi pemicu dari SSMS

CREATE TRIGGER PreventOrdersUpdate
ON Orders
INSTEAD OF UPDATE
AS
RAISERROR ('Updates on "Orders" table
            are not permitted.
            Place a new order
            to add new products.', 16, 1);

Melihat definisi pemicu (opsi 2)

SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID ('PreventOrdersUpdate');

| definition                                                   |
|--------------------------------------------------------------|
| CREATE TRIGGER PreventOrdersUpdate                           |
| ON Orders                                                    |
| INSTEAD OF UPDATE                                            |
| AS                                                           |
| RAISERROR ('Updates on "Orders" table are not permitted.     |
|             Place a new order to add new products.', 16, 1); |

Melihat definisi pemicu (opsi 3)

SELECT OBJECT_DEFINITION (OBJECT_ID ('PreventOrdersUpdate'));

| (No column name)                                             |
|--------------------------------------------------------------|
| CREATE TRIGGER PreventOrdersUpdate                           |
| ON Orders                                                    |
| INSTEAD OF UPDATE                                            |
| AS                                                           |
| RAISERROR ('Updates on "Orders" table are not permitted.     |
|             Place a new order to add new products.', 16, 1); |

Melihat definisi pemicu (opsi 4)

EXECUTE sp_helptext @objname = 'PreventOrdersUpdate';

| Text                                                         |
|--------------------------------------------------------------|
| CREATE TRIGGER PreventOrdersUpdate                           |
| ON Orders                                                    |
| INSTEAD OF UPDATE                                            |
| AS                                                           |
| RAISERROR ('Updates on "Orders" table are not permitted.     |
|             Place a new order to add new products.', 16, 1); |

Praktik terbaik pemicu

Tips:

desain basis data terdokumentasi baik
logika pemicu sederhana
hindari penggunaan pemicu berlebihan

Ayo berlatih!

Membangun dan Mengoptimalkan Trigger di SQL Server