Trigger AFTER (DML)

Membangun dan Mengoptimalkan Trigger di SQL Server

Florin Angelescu

Instructor

Definisi dan properti

Menjalankan serangkaian aksi saat dipicu
Aksi berjalan hanya setelah event DML selesai
Dipakai dengan pernyataan INSERT, UPDATE, dan DELETE untuk tabel atau view

Prasyarat trigger AFTER

Tabel atau view yang diperlukan untuk pernyataan DML
Trigger akan dipasang pada tabel yang sama

Keluaran dari tabel Products untuk contoh ini:

| Product | Price | Currency | Quantity | Measure |
|---------|-------|----------|----------|---------|
| Apple   | 2.00  | USD      | 25000    | kg      |
| Apricot | 1.55  | USD      | 2500     | kg      |
| Avocado | 3.00  | USD      | 1000     | kg      |
| Banana  | 1.80  | USD      | 35000    | kg      |

Prasyarat trigger AFTER

Tabel target
Deskripsi trigger
Event pemicu (DML)
Nama trigger

Products
Simpan info produk yang dihapus ke tabel riwayat
DELETE
TrackRetiredProducts

Ringkasan prasyarat trigger AFTER

Saat baris dihapus dari tabel Products...

Simpan informasi yang diperlukan dari baris tersebut ke tabel RetiredProducts.

| Product | Price | Currency | Quantity | Measure |
|---------|-------|----------|----------|---------|
| Apple   | 2.00  | USD      | 25000    | kg      |
| Apricot | 1.55  | USD      | 2500     | kg      | X
| Avocado | 3.00  | USD      | 1000     | kg      |
| Banana  | 1.80  | USD      | 35000    | kg      |

| Product | Measure | RemovalDate |
|---------|---------|-------------|
| Apricot | kg      | 19.04.2019  |

Definisi trigger AFTER

CREATE TRIGGER TrackRetiredProducts

ON Products

AFTER DELETE

AS
    INSERT INTO RetiredProducts (Product, Measure)
    SELECT Product, Measure
    FROM deleted;

Tabel "inserted" dan "deleted"

Tabel khusus yang dipakai trigger DML
Dibuat otomatis oleh SQL Server

Tabel "inserted" dan "deleted"

Tabel khusus yang dipakai trigger DML
Dibuat otomatis oleh SQL Server

Tabel khusus	INSERT	UPDATE	DELETE
inserted	baris baru	baris baru	N/A
deleted	N/A	baris yang diperbarui	baris yang dihapus

Trigger AFTER lengkap

CREATE TRIGGER TrackRetiredProducts
ON Products
AFTER DELETE
AS
    INSERT INTO RetiredProducts (Product, Measure)
    SELECT Product, Measure
    FROM deleted;

Ayo berlatih!

Membangun dan Mengoptimalkan Trigger di SQL Server