CODEWITHSUNDEEP
postgresqltriggers
Colonder
Colonder

Reputation: 1576

How to find out which row was updated in postgresql

I have three tables in PostgreSQL: customers, services and bought_services. It's one to many relation with linking table, so there are foreign keys in the third table referencing primary keys in first two. There's a primary key in each table. In customers table I have charge column, in services there's tax_percents column and in bought_services there are columns quantity, without_tax, tax and total. I want to write a trigger, that after quantity column update would update all the without_tax etc. columns and also customer's charge column. I wrote this so far:

CREATE FUNCTION updateValues() RETURN TRIGGER AS
$BODY$
BEGIN
UPDATE bought_services SET
        without_tax = quantity * (SELECT services.net_price FROM bought_services LEFT JOIN 
            services ON bought_serices.service_id = services.id), 
        tax = without_tax * (SELECT services.tax_percents FROM bought_services LEFT JOIN 
            services ON bought_serices.service_id = services.id) * 0.01, 
        total= without_tax + tax ;

        UPDATE customers SET
        charge = SELECT SUM(total) FROM bought_services LEFT JOIN customers ON 
            wykupione_uslugi.customer_id = customers.id WHERE bought_services.customer_id = ?
    end;
$BODY$

CREATE TRIGGER UpdateBoughtServicesValues AFTER UPDATE ON bought_services
FOR EACH ROW EXECUTE PROCEDURE updateValues();

I don't really know what should I type instead of ? that is, how should I tell postgres that I want to sum up the total amounts of all customer's services for which I updated the quantity field of one of them.

Upvotes: 0

Views: 383

Answers (1)

user330315
user330315

Reputation:

Unlike MySQL Postgres has two different types of triggers: one called for each row (as MySQL) and statement level triggers.

In a row level trigger the row that is updated (or inserted) can be directly accessed using the record new.

So as far as I can tell, your trigger should be something like this:

CREATE FUNCTION updatevalues() RETURN TRIGGER AS
$BODY$
declare 
   l_net_price decimal;
   l_tax_percent decimal;
BEGIN

   SELECT services.net_price, service.tax_percents
     into l_net_price, l_tax_percent
   FROM services 
   where service_id = new.service_id;

    new.without_tax := quantity * l_net_price;
    new.tax := without_tax * l_tax_percent;
    new.total := new.without_tax + new.tax; 

    UPDATE customers 
       SET charge = (select SUM(total)
                     FROM bought_services 
                     where bought_services.customer_id = new.customer_id)
     where id = new.customer_id;
end;
$BODY$;

I optimized it a bit, to only run the select for the tax calculation once.

Unrelated, but: this kind of calculation in a trigger is typically not such a good idea. The golden rule of thumb in the design of a relational database is to not store data that can be derived from existing data. This kind of aggregation should only be done in very rare cases. Otherwise I would simply aggregate those number whenever I need them. I would only do stuff like that if I absolutely have to. Note that Postgres is typically much better (faster) with complicated queries then MySQL. So don't be afraid of joining many tables. Only if you see that this really gives an unacceptable performance, then use tricks like that.

Upvotes: 1

Related Questions