SQL Programmability: Functions, Procedures & Triggers

Database không chỉ là nơi lưu trữ dữ liệu. Nó có thể chạy logic ngay bên trong. Bài này sẽ dạy bạn khi nào nên đưa logic xuống database.

1️⃣ Stored Procedures: Batch Jobs & Reduced Latency

Concept

Stored Procedure là một chương trình được lưu và thực thi bên trong database. Thay vì gửi 100 câu SQL từ application, bạn gọi 1 procedure và database tự xử lý.

Khi nào dùng Stored Procedures?

Use Case	Lý do
Batch Jobs phức tạp	Xử lý 10,000 records với logic nghiệp vụ
Giảm Network Latency	1 round-trip thay vì 100
Đảm bảo Atomic	Cả procedure chạy trong 1 transaction
Tái sử dụng logic	Nhiều app cùng gọi 1 procedure

Cú pháp PostgreSQL

sql

-- Tạo Stored Procedure
CREATE OR REPLACE PROCEDURE process_expired_orders()
LANGUAGE plpgsql
AS $$
DECLARE
    expired_count INT;
BEGIN
    -- Đánh dấu đơn hàng quá 30 ngày chưa thanh toán
    UPDATE orders 
    SET status = 'expired', updated_at = NOW()
    WHERE status = 'pending' 
      AND created_at < NOW() - INTERVAL '30 days';
    
    GET DIAGNOSTICS expired_count = ROW_COUNT;
    
    -- Log kết quả
    INSERT INTO job_logs (job_name, affected_rows, executed_at)
    VALUES ('process_expired_orders', expired_count, NOW());
    
    RAISE NOTICE 'Expired % orders', expired_count;
    
    COMMIT;  -- Procedure có thể COMMIT/ROLLBACK
END;
$$;

-- Gọi Procedure
CALL process_expired_orders();

Cú pháp MySQL

sql

DELIMITER //

CREATE PROCEDURE process_expired_orders()
BEGIN
    DECLARE expired_count INT DEFAULT 0;
    
    UPDATE orders 
    SET status = 'expired', updated_at = NOW()
    WHERE status = 'pending' 
      AND created_at < NOW() - INTERVAL 30 DAY;
    
    SET expired_count = ROW_COUNT();
    
    INSERT INTO job_logs (job_name, affected_rows, executed_at)
    VALUES ('process_expired_orders', expired_count, NOW());
END //

DELIMITER ;

-- Gọi
CALL process_expired_orders();

Procedure với Parameters

sql

-- PostgreSQL: Transfer money procedure
CREATE OR REPLACE PROCEDURE transfer_money(
    sender_id INT,
    receiver_id INT,
    amount DECIMAL(15,2)
)
LANGUAGE plpgsql
AS $$
DECLARE
    sender_balance DECIMAL(15,2);
BEGIN
    -- Lock sender row để tránh race condition
    SELECT balance INTO sender_balance 
    FROM accounts 
    WHERE id = sender_id 
    FOR UPDATE;
    
    IF sender_balance < amount THEN
        RAISE EXCEPTION 'Insufficient balance: % < %', sender_balance, amount;
    END IF;
    
    -- Thực hiện chuyển tiền
    UPDATE accounts SET balance = balance - amount WHERE id = sender_id;
    UPDATE accounts SET balance = balance + amount WHERE id = receiver_id;
    
    -- Log transaction
    INSERT INTO transactions (from_id, to_id, amount, created_at)
    VALUES (sender_id, receiver_id, amount, NOW());
    
    COMMIT;
END;
$$;

-- Usage
CALL transfer_money(1, 2, 1000000);

2️⃣ Triggers: Tự động hóa Actions

Concept

Trigger là code tự động chạy KHI có sự kiện xảy ra (INSERT, UPDATE, DELETE). Bạn không cần gọi nó manually.

Event -> Trigger Fires -> Execute Function

Timing Options

Timing	Mô tả
`BEFORE`	Chạy trước khi row thay đổi (có thể modify data)
`AFTER`	Chạy sau khi row đã thay đổi (read new data)
`INSTEAD OF`	Thay thế action (dùng cho Views)

Use Case 1: Audit Log (After Insert/Update/Delete)

sql

-- Bảng audit log
CREATE TABLE audit_logs (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    action VARCHAR(10) NOT NULL,  -- INSERT, UPDATE, DELETE
    record_id INT NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_by VARCHAR(100),
    changed_at TIMESTAMPTZ DEFAULT NOW()
);

-- Function cho trigger
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_logs (table_name, action, record_id, new_data, changed_by)
        VALUES (TG_TABLE_NAME, 'INSERT', NEW.id, to_jsonb(NEW), current_user);
        RETURN NEW;
        
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_logs (table_name, action, record_id, old_data, new_data, changed_by)
        VALUES (TG_TABLE_NAME, 'UPDATE', NEW.id, to_jsonb(OLD), to_jsonb(NEW), current_user);
        RETURN NEW;
        
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_logs (table_name, action, record_id, old_data, changed_by)
        VALUES (TG_TABLE_NAME, 'DELETE', OLD.id, to_jsonb(OLD), current_user);
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Attach trigger vào bảng orders
CREATE TRIGGER orders_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION log_changes();

-- Bây giờ mọi thay đổi trên orders sẽ tự động được log!

Use Case 2: Auto-update `updated_at`

sql

-- Function cập nhật timestamp
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Áp dụng cho nhiều bảng
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

Use Case 3: Validate Business Rules

sql

-- Trigger đảm bảo stock không âm sau khi bán
CREATE OR REPLACE FUNCTION check_stock_before_order()
RETURNS TRIGGER AS $$
DECLARE
    current_stock INT;
BEGIN
    SELECT stock INTO current_stock
    FROM products
    WHERE id = NEW.product_id;
    
    IF current_stock < NEW.quantity THEN
        RAISE EXCEPTION 'Insufficient stock for product %: available %, requested %',
            NEW.product_id, current_stock, NEW.quantity;
    END IF;
    
    -- Trừ stock
    UPDATE products 
    SET stock = stock - NEW.quantity 
    WHERE id = NEW.product_id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_order_stock
BEFORE INSERT ON order_items
FOR EACH ROW EXECUTE FUNCTION check_stock_before_order();

⚠️ Trigger Gotchas

Hidden Logic: Trigger chạy ngầm, làm debug khó khăn.
Performance: Trigger phức tạp làm chậm INSERT/UPDATE.
Cascading Triggers: Trigger A gọi Trigger B -> Khó kiểm soát.
Testing: Phải test cả database, không chỉ application code.

Best Practice: Chỉ dùng trigger cho audit logs và auto-update timestamps. Business logic nên ở application layer.

3️⃣ User Defined Functions (UDF)

Concept

Function khác Procedure ở chỗ nó RETURN một giá trị và có thể dùng trong SELECT.

	Procedure	Function
Return value	Không (hoặc OUT params)	Có
Dùng trong SELECT	Không	✅ Có
Transaction control	Có (COMMIT/ROLLBACK)	Không
Side effects	Thường có	Nên pure (không side effects)

Scalar Function (Trả về 1 giá trị)

sql

-- Function tính tuổi từ ngày sinh
CREATE OR REPLACE FUNCTION calculate_age(birth_date DATE)
RETURNS INT AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM age(birth_date));
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Usage trong SELECT
SELECT 
    name, 
    birth_date,
    calculate_age(birth_date) AS age
FROM users;

Table Function (Trả về table)

sql

-- Function trả về top customers
CREATE OR REPLACE FUNCTION get_top_customers(limit_count INT DEFAULT 10)
RETURNS TABLE (
    customer_id INT,
    customer_name VARCHAR,
    total_orders INT,
    total_spent DECIMAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        u.id,
        u.full_name,
        COUNT(o.id)::INT,
        COALESCE(SUM(o.total_amount), 0)
    FROM users u
    LEFT JOIN orders o ON o.user_id = u.id
    GROUP BY u.id, u.full_name
    ORDER BY SUM(o.total_amount) DESC NULLS LAST
    LIMIT limit_count;
END;
$$ LANGUAGE plpgsql STABLE;

-- Usage
SELECT * FROM get_top_customers(5);

Function Volatility Categories

sql

-- IMMUTABLE: Luôn trả về cùng kết quả với cùng input
-- Có thể cache, dùng trong index
CREATE FUNCTION double(x INT) RETURNS INT AS $$
    SELECT x * 2;
$$ LANGUAGE sql IMMUTABLE;

-- STABLE: Kết quả không đổi trong 1 transaction
-- Ví dụ: function đọc config table
CREATE FUNCTION get_tax_rate() RETURNS DECIMAL AS $$
    SELECT rate FROM tax_config WHERE is_active = true LIMIT 1;
$$ LANGUAGE sql STABLE;

-- VOLATILE (default): Kết quả có thể khác mỗi lần gọi
-- Ví dụ: NOW(), RANDOM(), functions có side effects
CREATE FUNCTION log_access() RETURNS VOID AS $$
    INSERT INTO access_logs VALUES (NOW());
$$ LANGUAGE sql VOLATILE;

4️⃣ So sánh: Khi nào dùng gì?

Cần làm gì?	Giải pháp
Batch job phức tạp, cần transaction control	Stored Procedure
Tự động log/validate khi data thay đổi	Trigger
Mở rộng SQL với logic tái sử dụng	Function
Compute column value	Function (trong SELECT)

💡 HPN Pro Tip: Database vs Application Logic

                    Application Layer
                    ┌─────────────────────────────────┐
                    │  Business Logic                 │
                    │  (Dễ test, dễ deploy, dễ scale) │
                    └─────────────────────────────────┘
                                  │
                    Database Layer
                    ┌─────────────────────────────────┐
                    │  Constraints (PK, FK, CHECK)    │ ← Dùng nhiều
                    │  Triggers (Audit, Timestamps)   │ ← Dùng vừa
                    │  Stored Procedures              │ ← Dùng ít
                    │  Complex Business Logic         │ ← Tránh!
                    └─────────────────────────────────┘

Nguyên tắc: Đưa logic xuống DB chỉ khi có lý do rõ ràng (performance, atomicity). Mặc định, giữ logic ở application.

SQL Programmability: Functions, Procedures & Triggers ​

1️⃣ Stored Procedures: Batch Jobs & Reduced Latency ​

Concept ​

Khi nào dùng Stored Procedures? ​

Cú pháp PostgreSQL ​

Cú pháp MySQL ​

Procedure với Parameters ​

2️⃣ Triggers: Tự động hóa Actions ​

Concept ​

Timing Options ​

Use Case 1: Audit Log (After Insert/Update/Delete) ​

Use Case 2: Auto-update updated_at ​

Use Case 3: Validate Business Rules ​

3️⃣ User Defined Functions (UDF) ​

Concept ​

Scalar Function (Trả về 1 giá trị) ​

Table Function (Trả về table) ​

Function Volatility Categories ​

4️⃣ So sánh: Khi nào dùng gì? ​

SQL Programmability: Functions, Procedures & Triggers

1️⃣ Stored Procedures: Batch Jobs & Reduced Latency

Concept

Khi nào dùng Stored Procedures?

Cú pháp PostgreSQL

Cú pháp MySQL

Procedure với Parameters

2️⃣ Triggers: Tự động hóa Actions

Concept

Timing Options

Use Case 1: Audit Log (After Insert/Update/Delete)

Use Case 2: Auto-update `updated_at`

Use Case 3: Validate Business Rules

3️⃣ User Defined Functions (UDF)

Concept

Scalar Function (Trả về 1 giá trị)

Table Function (Trả về table)

Function Volatility Categories

4️⃣ So sánh: Khi nào dùng gì?