How to update another table based on foreign table

I have two databases "Sales" and "Service" then have created a foreign Data wrapper on service for two tables of "Sales" DB ( accounts and orders) and created two foreign tables on service named accounts_sales and orders_sales.

Now on the order_sales foreign table, I have created one trigger that if there is any new order for any account the record is inserted in service.accounts table but it is not working. Below is the code.

CREATE table sales.accounts
(
  account_id  int,
  account_name text
)

CREATE table sales.order
(
  order_id_id  int,
  order_code   text,
  amount       numeric,
  account-id   int
)

Then created two foreign tables for the above table on service Environment.

CREATE table accounts_sale
    (
      account_id  int,
      account_name text
    )  SERVER sales
        OPTIONS (schema_name 'public', table_name 'accounts');

    CREATE table order_sales
    (
      order_id_id  int,
      order_code   text,
      amount       numeric,
      account-id   int
    ) SERVER sales
        OPTIONS (schema_name 'public', table_name 'orders');

Now I want when any new sales order generate on Sales environment then that new account entry should be inserted or update in service.accounts table based on sales.order table.

This is the trigger I have created on order_sales table

    CREATE OR REPLACE  FUNCTION set_sales_account_creation()
    RETURNS trigger
    LANGUAGE 'plpgsql'
 
AS $BODY$
BEGIN
        IF TG_OP = 'INSERT' THEN
            v_effective_from        :=  NEW.created_date;
            v_created_by            :=  NEW.created_by;
        ELSE
            v_effective_from        :=  NEW.last_modified_date;
            v_created_by            :=  NEW.last_modified_by;



        END IF;      
        
        IF TG_OP IN ('INSERT') THEN
        
            SELECT COUNT(1) INTO V_COUNT FROM
            ACCOUNTS WHERE SALES_ACCOUNT_ID = NEW.account_id;
            
            IF V_COUNT IS NULL OR V_COUNT  = 0  THEN
            
                INSERT INTO service.accounts
                SELECT
                FROM accounts_sales
                WHERE account_id = new.account_id;
              END IF; 
            END IF;
            RETURN NUll;
            
END;
$BODY$;

But when i have inserted in new order in Sales.orders table then it is not working.

CREATE TRIGGER TRG_ACCOUNT_SYNCUP_SALES
BEFORE INSERT OR UPDATE 
ON service.order_sales
FOR EACH ROW
EXECUTE PROCEDURE set_sales_account_creation();

Solution

There is a fundamental misconception about how triggers on foreign tables work.

If you are in database A, and you have a foreign table there that references a table in database B, then a trigger on the foreign table in A will fire whenever you run DML statements against the foreign table in A. The trigger will not fire if you modify the referenced table in B.