For Postgresql---and eventually Sybase ADS---I have tables that will replicate using homemade triggers. The triggers have a function which should record info for INSERT UPDATE DELETE changes which include the I/U/D operation and the ID of the row that changed. I am using test tables with a first name column and auto incrementing ID to test bidirectional replication.
If I make an insert to the NAME table such as adding 'JOHN' with an autoID of '1', am I able to insert that ID value and even possibly 'JOHN' into a log table? I am looking at the OLD.ID or NEW.ID options but I don't know if that's how it can work. My trigger/function code is:
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();
This is ultimately to make bidirectional replication using triggers. Thank you for any consideration or guidance. I will edit as needed.
I was able to resolve this by changing AFTER to BEFORE in the trigger, which I believed at first wouldn't work, or:
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();