Supabase数据库触发器 #

触发器特点
├── 自动执行的函数
├── 响应数据库事件
├── BEFORE/AFTER触发
├── 每行或每语句触发
└── 用于数据验证、审计、同步

-- 1. 创建触发器函数
CREATE OR REPLACE FUNCTION trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- 触发器逻辑
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. 创建触发器
CREATE TRIGGER trigger_name
    BEFORE INSERT ON table_name
    FOR EACH ROW
    EXECUTE FUNCTION trigger_function();

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.profiles (id, email, full_name)
    VALUES (
        NEW.id,
        NEW.email,
        NEW.raw_user_meta_data->>'full_name'
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW
    EXECUTE FUNCTION handle_new_user();

CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        -- NEW: 新插入的行
        INSERT INTO audit_log (action, record_id, new_data)
        VALUES ('INSERT', NEW.id, to_jsonb(NEW));
    ELSIF TG_OP = 'UPDATE' THEN
        -- OLD: 更新前的行
        -- NEW: 更新后的行
        INSERT INTO audit_log (action, record_id, old_data, new_data)
        VALUES ('UPDATE', NEW.id, to_jsonb(OLD), to_jsonb(NEW));
    ELSIF TG_OP = 'DELETE' THEN
        -- OLD: 被删除的行
        INSERT INTO audit_log (action, record_id, old_data)
        VALUES ('DELETE', OLD.id, to_jsonb(OLD));
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION notify_price_change()
RETURNS TRIGGER AS $$
BEGIN
    -- 只在价格变化时触发
    IF OLD.price IS DISTINCT FROM NEW.price THEN
        INSERT INTO price_history (product_id, old_price, new_price)
        VALUES (NEW.id, OLD.price, NEW.price);
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER track_price_changes
    AFTER UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION notify_price_change();

CREATE OR REPLACE FUNCTION validate_post()
RETURNS TRIGGER AS $$
BEGIN
    -- 验证标题长度
    IF LENGTH(NEW.title) < 5 THEN
        RAISE EXCEPTION 'Title must be at least 5 characters';
    END IF;
    
    -- 自动生成slug
    IF NEW.slug IS NULL THEN
        NEW.slug := LOWER(REGEXP_REPLACE(NEW.title, '[^a-zA-Z0-9]', '-', 'g'));
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_post_before_insert
    BEFORE INSERT OR UPDATE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION validate_post();

CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    action TEXT NOT NULL,
    record_id TEXT,
    old_data JSONB,
    new_data JSONB,
    user_id UUID,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, record_id, old_data, new_data, user_id)
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        COALESCE(NEW.id::TEXT, OLD.id::TEXT),
        CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END,
        auth.uid()
    );
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 应用到表
CREATE TRIGGER posts_audit
    AFTER INSERT OR UPDATE OR DELETE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger();

CREATE TABLE user_stats (
    user_id UUID PRIMARY KEY,
    posts_count INTEGER DEFAULT 0,
    comments_count INTEGER DEFAULT 0,
    last_post_at TIMESTAMPTZ
);

CREATE OR REPLACE FUNCTION update_user_stats()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO user_stats (user_id, posts_count, last_post_at)
        VALUES (NEW.author_id, 1, NEW.created_at)
        ON CONFLICT (user_id) DO UPDATE
        SET 
            posts_count = user_stats.posts_count + 1,
            last_post_at = NEW.created_at;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE user_stats
        SET posts_count = posts_count - 1
        WHERE user_id = OLD.author_id;
    END IF;
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_posts_stats
    AFTER INSERT OR DELETE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION update_user_stats();

CREATE OR REPLACE FUNCTION soft_delete()
RETURNS TRIGGER AS $$
BEGIN
    -- 不实际删除，而是设置deleted_at
    UPDATE posts
    SET deleted_at = NOW()
    WHERE id = OLD.id;
    
    RETURN NULL; -- 阻止实际删除
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_soft_delete
    BEFORE DELETE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION soft_delete();

CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := 
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'B');
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_search_vector
    BEFORE INSERT OR UPDATE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION update_search_vector();

-- 查看表的所有触发器
SELECT 
    trigger_name,
    event_manipulation,
    action_timing,
    action_statement
FROM information_schema.triggers
WHERE event_object_table = 'posts';

-- 禁用特定触发器
ALTER TABLE posts DISABLE TRIGGER posts_audit;

-- 禁用所有触发器
ALTER TABLE posts DISABLE TRIGGER ALL;

-- 启用触发器
ALTER TABLE posts ENABLE TRIGGER posts_audit;
ALTER TABLE posts ENABLE TRIGGER ALL;

DROP TRIGGER IF EXISTS trigger_name ON table_name;

CREATE OR REPLACE FUNCTION trigger_info()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'Table: %', TG_TABLE_NAME;
    RAISE NOTICE 'Schema: %', TG_TABLE_SCHEMA;
    RAISE NOTICE 'Operation: %', TG_OP;
    RAISE NOTICE 'When: %', TG_WHEN;
    RAISE NOTICE 'Level: %', TG_LEVEL;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

触发器性能建议
├── 避免复杂操作
├── 避免递归触发
├── 使用条件减少执行
├── 考虑批量操作影响
└── 必要时禁用触发器

CREATE OR REPLACE FUNCTION debug_trigger()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'Trigger fired: % on %', TG_OP, TG_TABLE_NAME;
    RAISE NOTICE 'OLD: %', OLD;
    RAISE NOTICE 'NEW: %', NEW;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;