Database Triggers: Trigger Database 🔄

Di materi keempat ini, kita akan belajar cara membuat dan menggunakan database triggers di berbagai DBMS. Contoh yang diberikan mencakup PostgreSQL, MySQL, dan SQL Server.

Prerequisites 📋

Sebelum memulai materi ini, pastikan Anda sudah memahami:

Dasar Triggers (Basic Triggers)

1. Before Insert Trigger

-- PostgreSQL
CREATE OR REPLACE FUNCTION validate_product()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.price < 0 THEN
        RAISE EXCEPTION 'Harga produk tidak boleh negatif';
    END IF;
    
    IF NEW.stock < 0 THEN
        RAISE EXCEPTION 'Stok produk tidak boleh negatif';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_product_before_insert
    BEFORE INSERT ON products
    FOR EACH ROW
    EXECUTE FUNCTION validate_product();

-- MySQL
DELIMITER //
CREATE TRIGGER check_product_before_insert
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Harga produk tidak boleh negatif';
    END IF;
    
    IF NEW.stock < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Stok produk tidak boleh negatif';
    END IF;
END //
DELIMITER ;

-- SQL Server
CREATE TRIGGER check_product_before_insert
ON products
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    IF EXISTS (
        SELECT 1 FROM inserted 
        WHERE price < 0 OR stock < 0
    )
    BEGIN
        THROW 50000, 'Harga atau stok produk tidak boleh negatif', 1;
        RETURN;
    END;
    
    INSERT INTO products (name, price, stock)
    SELECT name, price, stock
    FROM inserted;
END;

-- Contoh penggunaan (valid):
INSERT INTO products (name, price, stock)
VALUES ('Laptop Pro', 1500000, 10);

-- Contoh penggunaan (invalid):
INSERT INTO products (name, price, stock)
VALUES ('Invalid Product', -1000, -5);
-- Error: Harga dan stok produk tidak boleh negatif

2. After Update Trigger

-- PostgreSQL
CREATE OR REPLACE FUNCTION log_price_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO price_history (
        product_id, old_price, new_price, 
        change_date, changed_by
    )
    VALUES (
        NEW.id, OLD.price, NEW.price,
        CURRENT_TIMESTAMP, CURRENT_USER
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER track_price_changes
    AFTER UPDATE OF price ON products
    FOR EACH ROW
    EXECUTE FUNCTION log_price_changes();

-- MySQL
DELIMITER //
CREATE TRIGGER track_price_changes
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.price <> NEW.price THEN
        INSERT INTO price_history (
            product_id, old_price, new_price,
            change_date, changed_by
        )
        VALUES (
            NEW.id, OLD.price, NEW.price,
            NOW(), CURRENT_USER()
        );
    END IF;
END //
DELIMITER ;

-- SQL Server
CREATE TRIGGER log_price_changes
ON products
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    IF UPDATE(price)
    BEGIN
        INSERT INTO price_history (
            product_id,
            old_price,
            new_price,
            change_date
        )
        SELECT 
            i.id,
            d.price,
            i.price,
            GETDATE()
        FROM inserted i
        JOIN deleted d ON i.id = d.id
        WHERE i.price <> d.price;
    END;
END;

-- Contoh penggunaan:
UPDATE products 
SET price = 1800000 
WHERE id = 1;

-- Data perubahan harga:
product_id | old_price | new_price | change_date           | changed_by
---------- | --------- | --------- | -------------------- | ----------
1          | 1500000   | 1800000   | 2024-01-15 10:30:00 | admin

3. Complex Trigger with Multiple Events

-- SQL Server
CREATE TRIGGER maintain_inventory
ON order_items
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Handle deletes (cancelled orders)
    IF EXISTS (SELECT 1 FROM deleted)
    BEGIN
        UPDATE p
        SET p.stock = p.stock + d.quantity
        FROM products p
        JOIN deleted d ON p.id = d.product_id;
    END;
    
    -- Handle inserts (new orders)
    IF EXISTS (SELECT 1 FROM inserted)
    BEGIN
        -- Check if we have enough stock
        IF EXISTS (
            SELECT 1
            FROM inserted i
            JOIN products p ON i.product_id = p.id
            WHERE p.stock < i.quantity
        )
        BEGIN
            THROW 50000, 'Insufficient stock for order', 1;
            RETURN;
        END;
        
        -- Update stock
        UPDATE p
        SET p.stock = p.stock - i.quantity
        FROM products p
        JOIN inserted i ON p.id = i.product_id;
    END;
END;

4. DDL Trigger (SQL Server Specific)

-- SQL Server
CREATE TRIGGER prevent_table_deletion
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    PRINT 'Table deletion is not allowed in production';
    ROLLBACK;
END;

-- Audit schema changes
CREATE TRIGGER audit_schema_changes
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @EventData XML = EVENTDATA();
    
    INSERT INTO schema_changes_log (
        event_type,
        object_name,
        object_type,
        sql_command,
        login_name,
        event_date
    )
    VALUES (
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)'),
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'),
        @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),
        GETDATE()
    );
END;

3. Instead Of Delete Trigger

-- PostgreSQL
CREATE OR REPLACE FUNCTION handle_soft_delete()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE products
    SET 
        status = 'deleted',
        deleted_at = CURRENT_TIMESTAMP,
        deleted_by = CURRENT_USER
    WHERE id = OLD.id;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER soft_delete_products
    INSTEAD OF DELETE ON products
    FOR EACH ROW
    EXECUTE FUNCTION handle_soft_delete();

-- MySQL (menggunakan BEFORE DELETE karena tidak mendukung INSTEAD OF)
DELIMITER //
CREATE TRIGGER soft_delete_products
BEFORE DELETE ON products
FOR EACH ROW
BEGIN
    INSERT INTO deleted_products
    SELECT 
        p.*,
        NOW() as deleted_at,
        CURRENT_USER() as deleted_by
    FROM products p
    WHERE p.id = OLD.id;
END //
DELIMITER ;

-- SQL Server
CREATE TRIGGER soft_delete_products
ON products
INSTEAD OF DELETE
AS
BEGIN
    UPDATE p
    SET 
        status = 'deleted',
        deleted_at = GETDATE(),
        deleted_by = SYSTEM_USER
    FROM products p
    JOIN deleted d ON p.id = d.id;
END;

-- Contoh penggunaan:
DELETE FROM products WHERE id = 1;

-- Data produk setelah soft delete:
id | name       | status  | deleted_at           | deleted_by
-- | ---------- | ------- | ------------------- | ----------
1  | Laptop Pro | deleted | 2024-01-15 11:00:00 | admin

4. Multiple Event Trigger

-- PostgreSQL
CREATE OR REPLACE FUNCTION audit_inventory()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO inventory_audit (
            product_id, action, quantity_change,
            audit_date, audit_user
        )
        VALUES (
            NEW.id, 'INSERT', NEW.stock,
            CURRENT_TIMESTAMP, CURRENT_USER
        );
    ELSIF TG_OP = 'UPDATE' THEN
        IF NEW.stock <> OLD.stock THEN
            INSERT INTO inventory_audit (
                product_id, action, quantity_change,
                audit_date, audit_user
            )
            VALUES (
                NEW.id, 'UPDATE', NEW.stock - OLD.stock,
                CURRENT_TIMESTAMP, CURRENT_USER
            );
        END IF;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO inventory_audit (
            product_id, action, quantity_change,
            audit_date, audit_user
        )
        VALUES (
            OLD.id, 'DELETE', -OLD.stock,
            CURRENT_TIMESTAMP, CURRENT_USER
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER track_inventory_changes
    AFTER INSERT OR UPDATE OR DELETE ON products
    FOR EACH ROW
    EXECUTE FUNCTION audit_inventory();

-- MySQL
DELIMITER //
CREATE TRIGGER track_inventory_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO inventory_audit (
        product_id, action, quantity_change,
        audit_date, audit_user
    )
    VALUES (
        NEW.id, 'INSERT', NEW.stock,
        NOW(), CURRENT_USER()
    );
END //

CREATE TRIGGER track_inventory_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.stock <> OLD.stock THEN
        INSERT INTO inventory_audit (
            product_id, action, quantity_change,
            audit_date, audit_user
        )
        VALUES (
            NEW.id, 'UPDATE', NEW.stock - OLD.stock,
            NOW(), CURRENT_USER()
        );
    END IF;
END //

CREATE TRIGGER track_inventory_delete
AFTER DELETE ON products
FOR EACH ROW
BEGIN
    INSERT INTO inventory_audit (
        product_id, action, quantity_change,
        audit_date, audit_user
    )
    VALUES (
        OLD.id, 'DELETE', -OLD.stock,
        NOW(), CURRENT_USER()
    );
END //
DELIMITER ;

-- SQL Server
CREATE TRIGGER track_inventory_changes
ON products
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    -- Handle INSERT
    INSERT INTO inventory_audit (
        product_id, action, quantity_change,
        audit_date, audit_user
    )
    SELECT 
        id, 'INSERT', stock,
        GETDATE(), SYSTEM_USER
    FROM inserted
    WHERE id NOT IN (SELECT id FROM deleted);
    
    -- Handle UPDATE
    INSERT INTO inventory_audit (
        product_id, action, quantity_change,
        audit_date, audit_user
    )
    SELECT 
        i.id, 'UPDATE', i.stock - d.stock,
        GETDATE(), SYSTEM_USER
    FROM inserted i
    JOIN deleted d ON i.id = d.id
    WHERE i.stock <> d.stock;
    
    -- Handle DELETE
    INSERT INTO inventory_audit (
        product_id, action, quantity_change,
        audit_date, audit_user
    )
    SELECT 
        id, 'DELETE', -stock,
        GETDATE(), SYSTEM_USER
    FROM deleted
    WHERE id NOT IN (SELECT id FROM inserted);
END;

-- Contoh penggunaan:
INSERT INTO products (name, stock) 
VALUES ('New Product', 100);

UPDATE products 
SET stock = 80 
WHERE id = 1;

DELETE FROM products 
WHERE id = 2;

-- Data audit inventory:
product_id | action | quantity_change | audit_date           | audit_user
---------- | ------ | -------------- | ------------------- | ----------
3          | INSERT | 100            | 2024-01-15 12:00:00 | admin
1          | UPDATE | -20            | 2024-01-15 12:01:00 | admin
2          | DELETE | -50            | 2024-01-15 12:02:00 | admin

Tips Penting

  1. Perhatikan perbedaan fitur trigger antar DBMS
  2. Gunakan trigger dengan bijak karena dapat mempengaruhi performa
  3. Implementasikan error handling yang tepat
  4. Pertimbangkan penggunaan audit table untuk tracking perubahan

Latihan

  1. Buat trigger untuk validasi data sebelum insert/update
  2. Implementasikan sistem audit log menggunakan trigger
  3. Buat trigger untuk maintain history table

Referensi

Materi Sebelumnya

Materi Selanjutnya

Fatkul Nur Koirudin