Hi all,
I used two databse with slony-i, they work well. db_master1 is a master
node, and db_slave1 is a slave.
In the both databse, I have a table call staff with following structure:
CREATE TABLE staff
(
id text NOT NULL,
"name" text,
age integer,
CONSTRAINT staff_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE staff OWNER TO postgres;
I test on windows xp, it is ok.
Then I created one more databse, its name is db_center.
My idea is: when slave have an operation - insert or update or delete then
the operated record must be updated to db_center, so i create a trigger
function as following:
CREATE OR REPLACE FUNCTION process_staff_audit() RETURNS TRIGGER AS
$staff_audit$
BEGIN
IF (TG_OP = 'DELETE') THEN
PERFORM dblink_exec('dbname=db_center port=5432
host=192.168.1.226 user=user1 password=user1',
'DELETE FROM staff WHERE id = ''' || OLD.id ||
''';',false);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
PERFORM dblink_exec('dbname=db_center port=5432
host=192.168.1.226 user=user1 password=user1',
'UPDATE staff SET "name" = ''' || NEW.name ||
''', age = ''' || NEW.age
|| ''' WHERE id = ''' || NEW.id || ''';',false);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
PERFORM dblink_exec('dbname=db_center port=5432
host=192.168.1.226 user=user1 password=user1',
'INSERT INTO staff VALUES( ''' || NEW.id ||''',
''' || NEW.name ||''',
''' || NEW.age ||''');',false);
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$staff_audit$ LANGUAGE plpgsql;
CREATE TRIGGER staff_audit
AFTER INSERT OR UPDATE OR DELETE ON staff
FOR EACH ROW EXECUTE PROCEDURE process_staff_audit();
I tested this trigger function on two database without slony-i, it worked
well.
But I used this trigger function on db_slave1 (using slony-i), it didn't
work.
Please help me to solve this problem,
Thank you very much.
Nick yahoo: lucf52
Nick skype: airbus--380
--
View this message in context:
http://www.nabble.com/Slony-I%2C-Slave-node-and-trigger-function-tp25639915p25639915.html
Sent from the Slony-I -- General mailing list archive at Nabble.com.
_______________________________________________
Slony1-general mailing list
[email protected]
http://lists.slony.info/mailman/listinfo/slony1-general
