Count sum with trigger for delete/update/insert-CodePudding

I want to create trigger that updates price in a row. However this works fine whenever I am adding an amount, but it doesn't work whenever I am deleting the item or updating with smaller value. How can I make this trigger to work for all the 3 needs? I don't want to write multiple of triggers to handle this.

CREATE FUNCTION sum_total() RETURNS TRIGGER
AS $$
BEGIN
    UPDATE payment
    SET price = price   (SELECT SUM(price) from basket WHERE service_id = new.service_id)
    WHERE service_id = new.service_id;
    RETURN NULL;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER sum_total AFTER INSERT ON basket
    FOR EACH ROW EXECUTE PROCEDURE sum_total();

CodePudding user response：

A Postgres trigger function has a set of special local variables made available to it. One of these, TG_OP indicates the action being that caused the trigger to fire. With this the basic template for multiple becomes:

create function trigger_function_name() 
  returns trigger
  language plpgsql
as $$
begin
    case TG_OP
         when 'INSERT'   then  
              <place code for insert processing here>
         when 'UPDATE'   then 
              <place code for update processing here>         
         when 'DELETE'   then
              <place code for delete processing here>         
         when 'TRUNCATE' then 
              <place code for truncate processing here>   -- note must be Statement level trigger        
    end ; 
    return new; 
end ; 
$$ ;

However, I advise against even attempting this. Keeping a running total quickly becomes complex. This is especially when the value you are trying to maintain is easily deliverable as:

select sum(price) from basket where service_id = service_id_parameter;

That brings up another point. Your algorithm for computing payment is fundamentally flawed and will not work as is. Consider a user who adds an item to their basket at a price of 10. The payment price would not be 10 (what happens if this is the 1st item? Does the payment entry even exist for that service_id?) Now at a later time that user adds a second item for say 20. Total now in basket is now 30 (10 20), but value in payment is 40 (10 sum(10,20)). And what happens to another user with the same items but selects them in the reverse order. (Hint, you will get a different payment value.)
Best process: Unless you are expecting 100s of millions just eliminate the triggers altogether and derive the payment when needed, perhaps with a view that does so.

CodePudding user response：

you can specify different event types using OR from the docs: https://www.postgresql.org/docs/9.2/sql-createtrigger.html

event
One of INSERT, UPDATE, DELETE, or TRUNCATE; this specifies the 
event that will fire the trigger.
Multiple events can be specified using OR.