在postgresql 15中,编写sql触发器实现跨表数据同步的核心做法是:定义一个触发器函数(trigger function),在其中通过 `new` 和 `old` 变量捕获行级变更,并使用 `insert`、`update` 或 `delete` 语句将变更应用到目标表。具体而言,你需要创建一个返回类型为 `trigger` 的函数,并在源表上绑定该函数到 `after insert or update or delete` 事件。这样做可以确保当源表数据发生变化时,目标表自动保持同步,且由于使用了事务机制,操作具有原子性。

如何定义处理插入和更新操作的触发器函数?
要处理插入和更新操作,触发器函数需要检查 `TG_OP` 变量的值来判断当前执行的操作类型。对于 `INSERT` 和 `UPDATE`,通常需要将新值(`NEW`)写入目标表;如果是 `UPDATE`,可能还需要先删除旧记录或根据主键更新。以下是一个完整的示例程序,展示了如何创建一个简单的同步触发器,将 `source_table` 的变更同步到 `target_table`。

-- 创建目标表(如果不存在)
CREATE TABLE IF NOT EXISTS target_table (
id INTEGER PRIMARY KEY,
name TEXT,
value NUMERIC
);
-- 创建触发器函数
CREATE OR REPLACE FUNCTION sync_to_target()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
-- 插入新记录到目标表
INSERT INTO target_table (id, name, value)
VALUES (NEW.id, NEW.name, NEW.value)
ON CONFLICT (id) DO UPDATE SET
name = EXCLUDED.name,
value = EXCLUDED.value;
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
-- 更新目标表中的现有记录
UPDATE target_table
SET name = NEW.name,
value = NEW.value
WHERE id = NEW.id;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
-- 从目标表中删除记录
DELETE FROM target_table
WHERE id = OLD.id;
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- 在源表上创建触发器
CREATE TRIGGER trg_sync_source_to_target
AFTER INSERT OR UPDATE OR DELETE ON source_table
FOR EACH ROW
EXECUTE FUNCTION sync_to_target();上述代码的预期结果是:当对 source_table 进行插入、更新或删除操作时,target_table 会立即发生相应的变化。例如,插入一条 id=1, name='test', value=100 的记录到源表,目标表中也会存在相同的记录。如果更新源表中的 id=1 记录,目标表中的对应记录也会被更新。如果删除源表中的记录,目标表中的记录也会被删除。
如何处理外键约束和冲突情况?
在跨表同步中,外键约束和冲突处理是关键问题。如果目标表有外键约束指向其他表,或者源表和目标表之间存在复杂的关系,需要在触发器函数中妥善处理这些情况。此外,如果目标表中已经存在相同主键的记录,需要使用 ON CONFLICT 子句来处理冲突。以下代码展示了如何在触发器函数中处理外键检查和冲突情况。

-- 假设目标表有外键约束
ALTER TABLE target_table
ADD CONSTRAINT fk_source_id
FOREIGN KEY (id) REFERENCES source_table(id);
-- 修改触发器函数以处理外键检查和冲突
CREATE OR REPLACE FUNCTION sync_to_target_with_fk_check()
RETURNS TRIGGER AS $$
DECLARE
target_exists BOOLEAN;
BEGIN
IF TG_OP = 'INSERT' THEN
-- 检查外键约束
IF NOT EXISTS (SELECT 1 FROM source_table WHERE id = NEW.id) THEN
RAISE EXCEPTION 'Foreign key constraint violated: id % does not exist in source_table', NEW.id;
END IF;
-- 插入或更新目标表
INSERT INTO target_table (id, name, value)
VALUES (NEW.id, NEW.name, NEW.value)
ON CONFLICT (id) DO UPDATE SET
name = EXCLUDED.name,
value = EXCLUDED.value;
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
-- 检查外键约束
IF NOT EXISTS (SELECT 1 FROM source_table WHERE id = NEW.id) THEN
RAISE EXCEPTION 'Foreign key constraint violated: id % does not exist in source_table', NEW.id;
END IF;
-- 更新目标表
UPDATE target_table
SET name = NEW.name,
value = NEW.value
WHERE id = NEW.id;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
-- 检查外键约束
IF EXISTS (SELECT 1 FROM target_table WHERE id = OLD.id) THEN
-- 如果目标表中有记录,则删除
DELETE FROM target_table
WHERE id = OLD.id;
END IF;
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- 替换旧的触发器
DROP TRIGGER IF EXISTS trg_sync_source_to_target ON source_table;
CREATE TRIGGER trg_sync_source_to_target
AFTER INSERT OR UPDATE OR DELETE ON source_table
FOR EACH ROW
EXECUTE FUNCTION sync_to_target_with_fk_check();这段代码的预期结果是:在插入或更新操作时,如果源表中不存在对应的主键,会抛出异常,防止数据不一致。在删除操作时,只有当目标表中存在对应记录时才会删除,避免不必要的错误。这种处理方式确保了数据的完整性和一致性。
基于源表特定输入条件的同步行为是什么?
在某些场景下,我们可能希望只在满足特定条件时才触发同步操作。例如,只同步状态为“active”的记录,或者只同步某些字段的变化。以下代码展示了如何基于源表的特定输入条件来控制同步行为。
-- 修改触发器函数以基于特定条件同步
CREATE OR REPLACE FUNCTION sync_to_target_with_condition()
RETURNS TRIGGER AS $$
BEGIN
-- 只同步状态为 'active' 的记录
IF TG_OP = 'INSERT' AND NEW.status = 'active' THEN
INSERT INTO target_table (id, name, value)
VALUES (NEW.id, NEW.name, NEW.value)
ON CONFLICT (id) DO UPDATE SET
name = EXCLUDED.name,
value = EXCLUDED.value;
RETURN NEW;
ELSIF TG_OP = 'UPDATE' AND NEW.status = 'active' THEN
UPDATE target_table
SET name = NEW.name,
value = NEW.value
WHERE id = NEW.id;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
DELETE FROM target_table
WHERE id = OLD.id;
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- 替换旧的触发器
DROP TRIGGER IF EXISTS trg_sync_source_to_target ON source_table;
CREATE TRIGGER trg_sync_source_to_target
AFTER INSERT OR UPDATE OR DELETE ON source_table
FOR EACH ROW
EXECUTE FUNCTION sync_to_target_with_condition();这段代码的预期结果是:只有当源表中的记录状态为“active”时,才会触发同步操作。如果插入或更新的记录状态不是“active”,则不会同步到目标表。这提供了一种灵活的控制机制,可以根据业务需求定制同步行为。

















