drop function if exists finance.fn_update_account_id() cascade;
create function finance.fn_update_account_id() returns trigger as $$
begin
new.sfdc_account_id := (
select sfdc.account_id
from dim.account_master
where new.gl_customer_name = dim.account_master.account_name
);
return new;
end;
$$ language plpgsql;
drop trigger if exists tr_update_account_id_rev_reg on finance.rev_reg cascade;
create trigger tr_update_account_id_rev_reg
before insert on finance.rev_reg
for each row
execute procedure finance.fn_update_account_id();
A postgres trigger is a function that is automatically invoked when a specified database event occurs. A postgres trigger can be specified to fire before, after or instead of the operation. A trigger that is marked FOR EACH ROW is called once for each row that the operation modifies while a trigger that is marked FOR EACH STATEMENT is only called once per operation.
In this example we are inserting an arbitrary lookup field into a table before each row in the dataset is inserted into the table.