PostgreSQL Trigger Function with Join – How to Update Data

functionsjoin;postgresqltrigger

I have two relations :

CREATE TABLE relation_a (id_a int PRIMARY KEY, field_1 int);
CREATE TABLE relation_b (id_b int PRIMARY KEY,fk_a int REFERENCES relation_a(id_a), field_1 int);
INSERT INTO relation_a VALUES (1,100), (2,101), (3,102);
INSERT INTO relation_b VALUES (1,1), (2,2), (3,3)

I want to create a trigger which always set :

SET b.field_1 = a.field_1 FROM relation_a a, relation_b b WHERE b.fk_a = a.id_a

I tried something like this :

CREATE OR REPLACE FUNCTION function1() RETURNS trigger AS 
$$
BEGIN
    UPDATE relation_b as b
    SET field_1 = a.field_1
    FROM relation_a a
    WHERE a.id_a = new.fk_a;
END
$$
LANGUAGE PLPGSQL;

CREATE TRIGGER trigger1
   AFTER INSERT OR UPDATE 
   ON relation_b
   FOR EACH ROW EXECUTE PROCEDURE function1();

If i try to insert or update data in relation_b, i got a very long error message i can't understand :

Can someone explain me the right way ? Thanks a lot

Best Answer

Just SET the value for field_1 BEFORE INSERT OR UPDATE. Just, make sure you use the alias for the new row of data.

needs to be edited for postgresql syntax

CREATE TRIGGER trigger1
   BEFORE INSERT OR UPDATE 
   ON relation_b
   FOR EACH ROW
begin
  -- "new" represent "current row being inserted/updated"
  -- just modify the values BEFORE it is stored in the DB

  select a.field_1
    into new.field_1
  from relation_a a
  where a.id_a = new.fk_a;

  -- add exception handle where "no data is found"
end;

Related Solutions

Sql-server – Alternative query to this (avoid DISTINCT)

The two scripts in RThomas' answer are both useful. You could also use GROUP BY, which gives a similar advantage to RThomas' methods, but keeping a similar form to your original query.

select country 
from Users inner join
countries on users.CountryID=countries.CountryID
GROUP BY countries.CountryID, countries.country;

The reason why you group by CountryID is that it's the primary key of your countries table, giving the Query Optimizer some better options.

...except that it's not in your scripts.

Put PKs (with Clustered Indexes) on your tables, and a FK relationship between them. Index CountryID in the Users table, and put a Unique Index on the Country field.

Once you've done all that, using DISTINCT how you have will actually give you the ideal execution plan.

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

What you presented is essentially the correct way for interrupting triggers in MySQL 5.1

I wrote about this before

Dec 23, 2011 : check constraint does not work?
Apr 25, 2011 : Trigger in MySQL to prevent insertion

You cannot do Dynamic SQL in triggers.

You may have to resort to coding the my_signal like this:

CREATE PROCEDURE `my_signal`(error_type INT)
BEGIN
    IF error_type = 1 THEN
        UPDATE `Error: can not insert new OWNER !` set x=1;
    END IF;
    IF error_type = 2 THEN
        UPDATE `Error: Row can not reference itself!` set x=1;
    END IF;
    .
    .
END//

then change the trigger to look like this:

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal(1);
       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal(2);
      END CASE; 
  END$$   
DELIMITER ;

Sorry for the clumsy programming but Signal Processing is MySQL 5.1 is horrible.

Best Answer

Related Solutions

Sql-server – Alternative query to this (avoid DISTINCT)

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

Related Question