Cara penggunaan trigger DML

Membangun dan Mengoptimalkan Trigger di SQL Server

Florin Angelescu

Instructor

Mengapa menggunakan trigger DML?

Memicu aksi saat memanipulasi data
Mencegah manipulasi data
Melacak perubahan data atau objek basis data
Audit pengguna dan keamanan basis data

Memilih antara AFTER dan INSTEAD OF

CREATE TRIGGER MyFirstAfterTrigger
ON Table1
-- Triggered after 
-- the firing event (UPDATE)
AFTER UPDATE
AS
{trigger_actions_section};

CREATE TRIGGER MyFirstInsteadOfTrigger
ON Table2
-- Triggered instead of 
-- the firing event (UPDATE)
INSTEAD OF UPDATE
AS
{trigger_actions_section};

Memilih antara AFTER dan INSTEAD OF

Trigger AFTER

Peristiwa awal memicu trigger

Peristiwa awal dieksekusi

Aksi trigger dijalankan

Cara kerja Trigger AFTER

Trigger INSTEAD OF

Peristiwa awal memicu trigger

Peristiwa awal tidak lagi dieksekusi

Aksi trigger dijalankan

Cara kerja Trigger INSTEAD OF

Contoh penggunaan trigger AFTER

Data dimasukkan ke tabel penjualan
Jalankan prosedur pembersihan data
Hasilkan laporan tabel dari hasil prosedur
Beri tahu administrator basis data

CREATE TRIGGER SalesNewInfoTrigger
ON Sales
AFTER INSERT
AS

EXEC sp_cleansing @Table = 'Sales';

EXEC sp_generateSalesReport;

EXEC sp_sendnotification;

Contoh penggunaan trigger INSTEAD OF

| Brand | Model    | Power | Stock |
|-------|----------|-------|-------|
| Ecco  | Standard | 30W   | 30    |
| Miry  | Buma     | 45W   | 0     |
| Lume  | Ultra    | 50W   | 0     |

Daya berubah untuk beberapa model

CREATE TRIGGER BulbsStockTrigger
ON Bulbs
INSTEAD OF INSERT
AS

Contoh penggunaan trigger INSTEAD OF

| Brand | Model    | Power | Stock |
|-------|----------|-------|-------|
| Ecco  | Standard | 30W   | 30    |
| Miry  | Buma     | 50W   | 100   |
| Lume  | Ultra    | 52W   | 100   |

Daya berubah untuk beberapa model
Perbarui hanya produk tanpa stok

CREATE TRIGGER BulbsStockTrigger
ON Bulbs
INSTEAD OF INSERT
AS

IF EXISTS (SELECT * FROM Bulbs AS b
INNER JOIN inserted AS i
           ON b.Brand = i.Brand
          AND b.Model = i.Model
WHERE b.Stock = 0)
BEGIN
    UPDATE b
    SET b.Power = i.Power,
        b.Stock = i.Stock
    FROM Bulbs AS b
    INNER JOIN inserted AS i
                ON b.Brand = i.Brand
                  AND b.Model = i.Model
    WHERE b.Stock = 0
END

Contoh penggunaan trigger INSTEAD OF

| Brand | Model    | Power | Stock |
|-------|----------|-------|-------|
| Ecco  | Standard | 30W   | 30    |
| Miry  | Buma     | 50W   | 100   |
| Lume  | Ultra    | 52W   | 100   |
| Ecco  | Standard | 35W   | 100   |

Daya berubah untuk beberapa model
Perbarui hanya produk tanpa stok
Tambahkan baris baru untuk produk yang punya stok

-- First part was truncated for spacing reasons
IF EXISTS (SELECT * FROM Bulbs AS b
INNER JOIN inserted AS i
           ON b.Brand = i.Brand
          AND b.Model = i.Model
WHERE b.Stock = 0)
BEGIN
    UPDATE b
    SET b.Power = i.Power,
        b.Stock = i.Stock
    FROM Bulbs AS b
    INNER JOIN inserted AS i
                ON b.Brand = i.Brand
                  AND b.Model = i.Model
    WHERE b.Stock = 0
END
ELSE
    INSERT INTO Bulbs
    SELECT * FROM inserted;

Pertanyaan latihan

Membangun dan Mengoptimalkan Trigger di SQL Server