Postgresql – Creating trigger from one database table to another database table postgresql

postgresqltrigger

I have two database say db1 and db2. And two tables user_auth_table in db1 and user_table in db2. Now I want to trigger a function which sync up few values in both tables for each new row or on each new insertion in user_auth_table.

I have tried to write on my own and creating trigger in db1. But the error says user_table doesn't exist. Which I know why I am getting this error. Any suggestion to target the tables outside the db ?

CREATE TRIGGER sync_user_table AFTER INSERT ON user_auth_table for each row execute procedure syncAndMaintainUsersTable()

create or replace function syncAndMaintainUsersTable()
returns trigger as
$BODY$
begin
if NEW.user_uuid<>OLD.user_uuid then
    insert into user_table values (NEW.user_uuid, CURRENT_TIMESTAMP, NEW.provider);
end if;
return new;
end;
$BODY$
language plpgsql;

Best Answer

Usually the full qualified name of a db table is:

catalog.schema.table

If you want to insert some values into another table you should use this syntax:

insert into catalog.schema.table
values (..., ..., ...)

ALTERNATIVE

You would be way better off using MySQL Replication as follows:

S1 is the Master
S2 is the Slave
Set replicate-do-table=db1.T1 in my.cnf on S2

This would automatically do all DML against that table provided you replicate to the same named table.

Mysql – Create Trigger MySql update or insert in another table

First, you need to specified when do you want to run the trigger inside the table. I've edited your trigger and use AFTER UPDATE and AFTER INSERT:

AFTER INSERT:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAI$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAI` AFTER INSERT ON `TestTable` FOR EACH ROW
    BEGIN
    SET @COUNT=(SELECT COUNT(*) FROM TestTable2 WHERE (ItemId=NEW.ItemId AND Year=YEAR(NEW.Date) AND Month=MONTH(NEW.Date)));
    IF @COUNT=0 THEN
        INSERT INTO TestTable2 (ItemId,Year,Month,Open,Close,Increase ) VALUES 
        (NEW.ItemId , YEAR(NEW.Date), MONTH(NEW.Date),NEW.Value,NEW.Close,0.0); 
    ELSE
        UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT 
        Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
        WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));
    END IF;
    END;
$$
DELIMITER ;

AFTER UPDATE:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAU$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAU` AFTER UPDATE ON `TestTable` FOR EACH ROW
BEGIN

    UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT 
    Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
    WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));

END;
$$
DELIMITER ;

Note: Be careful about the SELECT Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)), I've added a LIMIT 1 to avoid duplicate rows.

Change the database TEST.

You can also use ON DUPLICATE KEY on the two triggers execution.

You can get more information in the MySQL's documentation manual.

Best Answer

Related Solutions

Mysql – Federated tables and triggers

ALTERNATIVE

Mysql – Create Trigger MySql update or insert in another table

AFTER INSERT:

AFTER UPDATE:

Related Question