PostgreSQL 触发器与存储过程

PL/pgSQL 函数基础

PostgreSQL 的存储过程实际上是一种函数（返回 void 的函数）。PL/pgSQL 是过程式语言，允许我们使用变量、控制流、异常处理等。

CREATE OR REPLACE PROCEDURE update_user_status(
    p_user_id INTEGER,
    p_status VARCHAR(20)
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE users
    SET status = p_status, updated_at = NOW()
    WHERE id = p_user_id;
IF NOT FOUND THEN
        RAISE EXCEPTION '用户 % 不存在', p_user_id;
    END IF;
COMMIT;
END;
$$;
CALL update_user_status(123, 'inactive');

触发器函数

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(50) NOT NULL,
    action VARCHAR(10) NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_by VARCHAR(100),
    changed_at TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, action, new_data, changed_by)
        VALUES (TG_TABLE_NAME, 'INSERT', to_jsonb(NEW), current_user);
        RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, action, old_data, new_data, changed_by)
        VALUES (TG_TABLE_NAME, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW), current_user);
        RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, action, old_data, changed_by)
        VALUES (TG_TABLE_NAME, 'DELETE', to_jsonb(OLD), current_user);
        RETURN OLD;
    END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_users_audit
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION audit_trigger();

自动时间戳

CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_updated_at
    BEFORE UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

条件触发器

CREATE TRIGGER trg_email_change
    AFTER UPDATE ON users
    FOR EACH ROW
    WHEN (OLD.email IS DISTINCT FROM NEW.email)
    EXECUTE FUNCTION log_email_change();

CREATE OR REPLACE FUNCTION log_email_change()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO email_change_log(user_id, old_email, new_email)
    VALUES (OLD.id, OLD.email, NEW.email);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

管理触发器

SELECT trigger_name, event_manipulation, action_statement
FROM information_schema.triggers
WHERE event_object_table = 'users';

ALTER TABLE users DISABLE TRIGGER trg_users_audit;
ALTER TABLE users ENABLE TRIGGER trg_users_audit;
DROP TRIGGER trg_users_audit ON users;