CODEWITHSUNDEEP
sqlpostgresqltriggerssql-updatesql-insert
Nok
Nok

Reputation: 57

Postgresql using Trigger to update column values after inserting values

I want to insert the values into a tableA and then update the column[amount_hkd]. I have successfully use the trigger but the performance is really slow, it takes an hour to finish the insertion of 8400 rows after I apply the trigger. How can I improve the performance? Thanks for your help

The statement to exectue:

INSERT INTO tableA (suppliers, invoice_dates, shipment_dates, amounts, currency, currency_conversion_rate)
SELECT l.supplier, l.invoice_date, l.shipment_date, l.amount, l.currency, o.currency_conversion_rate
FROM tableB l
LEFT JOIN tableC o
ON l.currency = o.currency_code
WHERE l.supplier = 'ABC'

The function I created:

CREATE OR REPLACE FUNCTION cal() 
RETURNS TRIGGER AS $$ 
BEGIN 
UPDATE tableA 
SET amount_hkd = NEW.amounts * NEW.currency_conversion_rate;
RETURN NEW; 
END; 
$$ LANGUAGE plpgsql;

The 1st Trigger I tried:

CREATE CONSTRAINT TRIGGER update_amount
AFTER INSERT ON tableA
DEFERRABLE
INITIALLY DEFERRED
FOR EACH ROW 
EXECUTE PROCEDURE cal();

The 2nd Trigger I tried:

CREATE TRIGGER update_amount 
AFTER INSERT ON tableA
FOR EACH ROW 
EXECUTE PROCEDURE cal();

Upvotes: 0

Views: 7936

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 248225

It is terribly inefficient to update the row after you inserted it. Better is to use a BEFORE trigger that can modify the new row before it is inserted:

CREATE OR REPLACE FUNCTION cal() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN 
   NEW.amount_hkd := NEW.amounts * NEW.currency_conversion_rate;
   RETURN NEW; 
END;$$;

CREATE TRIGGER update_amount 
   BEFORE INSERT ON tableA FOR EACH ROW 
   EXECUTE PROCEDURE cal();

Upvotes: 7

Belayer
Belayer

Reputation: 14932

You did not specify your Postgres version, but if at least V12 you can avoid triggers altogether. Define the column amount_hkd as a generated column. Not only do you avoid triggers, but you also eliminate maintenance issues of updating the currency_conversion_rate and/or amount and forgetting to also update amount_hkd, or a direct update to amount_hkd. Something like: ( see example here)

create table table_a( a_id integer generated always as identity 
                    , amounts  decimal
                    , currency_conversion_rate decimal
                    , amount_hkd decimal generated always as ( amounts * currency_conversion_rate ) stored
                    );

Upvotes: 3

Related Questions