Friday, August 13, 2010

cross database triggers in postgresql

now we can write Trigger in local database based on trigger event(insert/update/delete) trigger function may execute local/remote database. as everyone knows how to write trigger function execute on local/same database. let us see how can we write trigger function execute on remote database server.

steps
1) create trigger function
2) create trigger
3) execute sql statement for firing trigger event

1) create trigger function

1) Creating trigger function would execute on remote db server table

CREATE OR REPLACE FUNCTION fn_remote_insert() RETURNS TRIGGER
AS
$BODY$
DECLARE
-- declare anyvariable over here
BEGIN
PERFORM
DBLINK_EXEC('dbname=remoteDB host=190.10.1.5','INSERT INTO remoteTbl VALUES('||new.eid||','''||new.ename||''')');
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql'

2) Create trigger on local db server table

CREATE TRIGGER tr_remote_insert
BEFORE INSERT ON table1
FOR EACH ROW EXECUTE PROCEDURE fn_remote_insert()

3) Firing trigger event by insert

INSERT INTO table1 VALUES (986,'employeeName');

after above insert statement it will fire the trigger " tr_remote_insert " and trigger execute procedure, the procedure would insert new row in remote table.

by this way we can also  synchronize two PostgreSQL database server....
Post a Comment