触发器

-- 每次插入用户时，自动记录日志
CREATE TRIGGER log_new_user
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_creation();

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

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

CREATE OR REPLACE FUNCTION check_age()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.age < 0 OR NEW.age > 150 THEN
        RAISE EXCEPTION '年龄不合法: %', NEW.age;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_age
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION check_age();

CREATE OR REPLACE FUNCTION log_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_logs (user_id, action, changed_at)
    VALUES (NEW.id, TG_OP, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_log_changes
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_changes();

-- 文章表
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INT,
    title VARCHAR(200)
);

-- 用户统计表
CREATE TABLE user_stats (
    user_id INT PRIMARY KEY,
    post_count INT DEFAULT 0
);

-- 触发器函数
CREATE OR REPLACE FUNCTION update_post_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO user_stats (user_id, post_count)
        VALUES (NEW.user_id, 1)
        ON CONFLICT (user_id) 
        DO UPDATE SET post_count = user_stats.post_count + 1;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE user_stats 
        SET post_count = post_count - 1
        WHERE user_id = OLD.user_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trigger_update_post_count
AFTER INSERT OR DELETE ON posts
FOR EACH ROW
EXECUTE FUNCTION update_post_count();

CREATE OR REPLACE FUNCTION soft_delete()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE users 
    SET is_deleted = TRUE, deleted_at = NOW()
    WHERE id = OLD.id;
    RETURN NULL;  -- 阻止真实的DELETE
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_soft_delete
BEFORE DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION soft_delete();

-- 查看触发器
\dS table_name

-- 禁用触发器
ALTER TABLE users DISABLE TRIGGER trigger_name;

-- 启用触发器
ALTER TABLE users ENABLE TRIGGER trigger_name;

-- 删除触发器
DROP TRIGGER trigger_name ON users;