數據庫設計：藥品入出庫與庫存增減表觸發器實現方案

業務場景

三表結構：

• stock_in：藥品入庫單（增加庫存）
• stock_out：藥品出庫單（減少庫存）
• stock_change_log：庫存增減記錄（流水賬）

核心邏輯：四個觸發器覆蓋增刪場景

觸發器實現代碼

-- 入庫 INSERT：增加庫存、記錄流水
CREATE TRIGGER tr_in_insert ON stock_in
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE drug_stock 
    SET quantity = quantity + i.qty
    FROM inserted i
    WHERE drug_stock.drug_id = i.drug_id;

    INSERT INTO stock_change_log (drug_id, change_type, change_qty, change_time, ref_id)
    SELECT drug_id, 'IN', qty, GETDATE(), in_id FROM inserted;
END;

-- 入庫 DELETE：撤銷入庫，減少庫存、記錄負流水
CREATE TRIGGER tr_in_delete ON stock_in
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE drug_stock 
    SET quantity = quantity - d.qty
    FROM deleted d
    WHERE drug_stock.drug_id = d.drug_id;

    INSERT INTO stock_change_log (drug_id, change_type, change_qty, change_time, ref_id)
    SELECT drug_id, 'IN', -qty, GETDATE(), in_id FROM deleted;
END;

-- 出庫 INSERT：減少庫存、記錄流水
CREATE TRIGGER tr_out_insert ON stock_out
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE drug_stock 
    SET quantity = quantity - i.qty
    FROM inserted i
    WHERE drug_stock.drug_id = i.drug_id;

    INSERT INTO stock_change_log (drug_id, change_type, change_qty, change_time, ref_id)
    SELECT drug_id, 'OUT', -qty, GETDATE(), out_id FROM inserted;
END;

-- 出庫 DELETE：撤銷出庫，增加庫存、記錄正流水
CREATE TRIGGER tr_out_delete ON stock_out
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE drug_stock 
    SET quantity = quantity + d.qty
    FROM deleted d
    WHERE drug_stock.drug_id = d.drug_id;

    INSERT INTO stock_change_log (drug_id, change_type, change_qty, change_time, ref_id)
    SELECT drug_id, 'OUT', qty, GETDATE(), out_id FROM deleted;
END;

關鍵設計要點

1. inserted/deleted 虛表：觸發器內自動可用，包含受影響行
2. 多行處理：使用 JOIN inserted 而非變量賦值，支持批量操作
3. 事務一致性：觸發器在原事務中執行，回滾自動撤銷庫存變更
4. 庫存表設計：drug_stock(drug_id PK, quantity, min_stock, max_stock)
5. 流水表設計：stock_change_log(id PK, drug_id, change_type, change_qty, change_time, ref_id)

進階：防止超賣/負庫存

-- 出庫 INSERT 觸發器增加檢查
CREATE TRIGGER tr_out_insert ON stock_out
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- 檢查庫存充足
    IF EXISTS (
        SELECT 1 FROM inserted i
        JOIN drug_stock s ON s.drug_id = i.drug_id
        WHERE s.quantity < i.qty
    ) BEGIN
        RAISERROR('庫存不足，無法出庫', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
    -- 正常邏輯...
END;

替代方案：應用層事務腳本（微服務架構推薦）

@Transactional
public void stockIn(StockInOrder order) {
    stockInDao.insert(order);
    drugStockDao.increaseQuantity(order.getDrugId(), order.getQty());
    stockChangeLogDao.insert(new Log(order.getDrugId(), "IN", order.getQty()));
}

@Transactional
public void stockOut(StockOutOrder order) {
    // 悲觀鎖或樂觀鎖檢查庫存
    DrugStock stock = drugStockDao.lockById(order.getDrugId());
    if (stock.getQuantity() < order.getQty()) throw new BizException("庫存不足");
    stockOutDao.insert(order);
    drugStockDao.decreaseQuantity(order.getDrugId(), order.getQty());
    stockChangeLogDao.insert(new Log(order.getDrugId(), "OUT", -order.getQty()));
}

總結

觸發器方案適合單體應用、邏輯固定、數據庫為核心的場景，優勢是不可繞過、自動觸發；劣勢是隱式邏輯、調試難、跨庫遷移困難。現代微服務架構推薦應用層顯式事務腳本，邏輯可見、可測、可遷移。無論哪種方案，核心原則一致：入庫增、出庫減、刪除反向、流水全記錄。