CODEWITHSUNDEEP
postgresqltriggers
user1658873
user1658873

Reputation: 461

Insert trigger to Update another table using PostgreSQL

I have a table named awards. How can I mount a Trigger in PostgreSQL where each insert in the table awards updates a different table?

Upvotes: 44

Views: 69388

Answers (2)

Sachin Gupta
Sachin Gupta

Reputation: 1805

Here we have two tables named table1 and table2. Using a trigger I'll update table2 on insertion into table1.

Create the tables

CREATE TABLE table1
(
  id integer NOT NULL,
  name character varying,
  CONSTRAINT table1_pkey PRIMARY KEY (id)
)

CREATE TABLE table2
(
  id integer NOT NULL,
  name character varying
)

The Trigger Function

CREATE OR REPLACE FUNCTION function_copy() RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO
        table2(id,name)
        VALUES(new.id,new.name);

           RETURN new;
END;
$BODY$
language plpgsql;

The Trigger

CREATE TRIGGER trig_copy
     AFTER INSERT ON table1
     FOR EACH ROW
     EXECUTE PROCEDURE function_copy();

Upvotes: 90

Craig Ringer
Craig Ringer

Reputation: 325161

You want the documenation for PL/PgSQL triggers, which discusses just this case among others. The general documentation on triggers may also be useful.

You can use either a BEFORE or AFTER trigger for this. I'd probably use an AFTER trigger so that my trigger saw the final version of the row being inserted, though. You want FOR EACH ROW, of course.

Upvotes: 6

Related Questions