Postgres Create Trigger Pass Parameter from Current Insert

Question

I'm very new to Postgres and right now I am tasked to create a trigger that would fire after an insert. Basically, I would let a script do an insert, fire the trigger that would grab a field from another table based on a parameter from the Insert Statement then update the newly inserted record with the value from the other table.

INSERT record to Table 1.

INSERT INTO "TableA" VALUES ('ABC-101', 'John Doe', '')

PersonID  |  Name  |  Address
------------------------------
ABC-101    John Doe

After this insert, I would then fire the trigger that would grab the value from a field in TableB.

CREATE OR REPLACE FUNCTION fn_getaddress()
  RETURNS trigger AS
$BODY$
DECLARE
  PersonID     TEXT;
BEGIN
  PersonID  := TG_ARGV[0];

    UPDATE  "TableA" 
       SET  "Address"  = (SELECT  "Address" 
                            FROM  "TableB" 
                           WHERE  "PersonID" = PersonID);
    RETURN null;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Here's my trigger:

CREATE TRIGGER tr_getaddress
AFTER INSERT
ON TableA
FOR EACH ROW
EXECUTE PROCEDURE fn_getaddress(personid);

So basically, I have a field in TableB (say Address) that I need to grab in order to complete the record in TableA. I am using an ID to reference which Address to take from TableB.

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2016-04-27T20:29:24

You don't need to pass a value to the trigger. A trigger has full access to all columns from the inserted row through the implicit record new.

You also don't need an UPDATE statement to change the inserted row. You just assign values to the fields in the new record:

CREATE OR REPLACE FUNCTION fn_getaddress()
  RETURNS trigger AS
$BODY$
DECLARE 
   l_address text;
BEGIN

   SELECT  "Address" 
     into l_address
   FROM  "TableB" 
   WHERE  "PersonID" = new."PersonID"

   new."Address" := l_address;
   RETURN new; --<<< important
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

You can't however change the inserted row in an after trigger, you need a before trigger:

CREATE TRIGGER tr_getaddress
  BEFORE INSERT --<<< important
  ON "TableA"
  FOR EACH ROW
  EXECUTE PROCEDURE fn_getaddress(); --<< no parameter necessary

But copying data like that is usually a very bad idea. Why don't you just join the two tables if you need information from both?

^{Unrelated to your question, but you should really avoid those dreaded quoted identifiers. They are much more trouble then they are worth it}

Postgres Create Trigger Pass Parameter from Current Insert

1 Answers