对于Postgresql-最终还有Sybase ADS-我有一些表将使用自制的触发器进行复制。触发器有一个功能,它应该记录INSERT、UPDATE、DELETE更改的信息,包括I/U/D操作和更改的行的ID。我使用带有名字列和自动递增ID的测试表来测试双向复制。
如果我对NAME表进行插入操作,例如添加autoID为'1‘的'JOHN’,我是否可以将该ID值甚至'JOHN‘插入到日志表中?我正在考虑OLD.ID或NEW.ID选项,但我不知道它是否可以工作。我的触发器/函数代码是:
CREATE OR REPLACE FUNCTION Insert() RETURNS TRIGGER AS $ReplInsert$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO "log" ("Operation","ID","NAME")
VALUES ('DELETE', OLD.ID, OLD.NAME);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO "log" ("Operation","ID","NAME")
VALUES ('UPDATE', NEW.ID ,NEW.NAME);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO "log" ("Operation","ID","NAME")
VALUES ('UPDATE', NEW.ID , NEW.NAME);
RETURN NEW;
END IF;
RETURN NULL;
END;
$ReplInsert$ LANGUAGE plpgsql;
CREATE TRIGGER logTrg
AFTER INSERT OR UPDATE OR DELETE ON "FIRST_NAME"
FOR EACH ROW EXECUTE PROCEDURE Insert();这最终是使用触发器进行双向复制。感谢您的任何考虑或指导。我会根据需要进行编辑。
发布于 2019-02-09 16:21:06
我能够通过将触发器中的AFTER更改为which来解决这个问题,我一开始认为这不会起作用,或者:
CREATE OR REPLACE FUNCTION Insert() RETURNS TRIGGER AS $ReplInsert$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO "log" ("Operation","ID","NAME")
VALUES ('DELETE', OLD.ID, OLD.NAME);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO "log" ("Operation","ID","NAME")
VALUES ('UPDATE', NEW.ID ,NEW.NAME);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO "log" ("Operation","ID","NAME")
VALUES ('UPDATE', NEW.ID , NEW.NAME);
RETURN NEW;
END IF;
RETURN NULL;
END;
$ReplInsert$ LANGUAGE plpgsql;
CREATE TRIGGER logTrg
BEFORE INSERT OR UPDATE OR DELETE ON "FIRST_NAME"
FOR EACH ROW EXECUTE PROCEDURE Insert();https://stackoverflow.com/questions/54575124
复制相似问题