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....
5 comments:
hi.. i am from indonesia
i want to ask you about cross database trigger
i create a cross database trigger and already running, but i have problem..
if remote server have problem about connection my trigger cannot running...shom message "could not established connection"
how to check connection in remote server in good connection or bad connection so i can create
IF connect
statement
ELSE
statement
so my trigger can running if remote server have problem.
Hi Fahmi,
before doing actual things just query the remote database by
SELECT *
FROM dblink('dbname=test1 host=192.10.1.5','select id from students')
AS stu(id int);
here we ip address of remote host using 'host' parameter.
if above query returns any row then server is up and running else its not up.
Hello i tried to try this, and i got this:
ERROR: record "new" is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.....
yes, I've the same error message as Jedd. I'm using postgresql 9.1.2. Any clue to solve this?
hi,
jedd and matoa, sorry for the delayed response,
Inadvertently I missed out to add FOR EACH ROW in create trigger statement.
now create trigger(I've changed the create trigger statement) by using
CREATE TRIGGER tr_remote_insert
BEFORE INSERT ON table1
FOR EACH ROW EXECUTE PROCEDURE fn_remote_insert()
and try to insert you will get the result...
Post a Comment