Re: trigger impacting insertion of records

On 5/6/21 12:45 AM, Atul Kumar wrote:
> Hi,
>
> I have simple table having structure like given below:
>
> \d bp_ach_trans
> Table "bonzipay.bp_ach_trans"
> Column | Type |
> Modifiers
> --------------------+------------------------+-------------------------------------------------------------------
> bptransid | integer | not null default
> nextval('bp_ach_trans_bptransid_seq1'::regclass)
>
> filename | character varying(50) |
> payment_status | character varying(30) |
> settledate | character varying(15) |
> payment_pastransid | bigint |
> tname | character varying(250) |
> code | character varying(5) |
> error_txt | character varying(200) |
> routingnumber | character varying(15) |
> tracenumber | character varying(10) |
> accountnumber | character varying(15) |
> bankaccountnumber | character varying(17) |
> type | character varying(1) |
> amount | numeric |
> site | character varying(30) |
> accountype | character varying(2) |
> tranid | character varying(15) |
>
> Triggers:
> ins_ussf_rec AFTER INSERT ON bp_ach_trans FOR EACH ROW EXECUTE
> PROCEDURE ussf_accountnumber_update()
>
>
>
> the function definition is like below:
>
> CREATE OR REPLACE FUNCTION bonzipay.ussf_accountnumber_update()
> RETURNS trigger
> LANGUAGE plpgsql
> AS $function$ BEGIN update bonzipay.bp_ach_trans set
> accountnumber=replace(accountnumber,'_',' ') where
> left(accountnumber,3) = 'US_'; RETURN NEW; END; $function$
>
>
> my query is:
>
> when I am inserting around 1000 records in the table having
> accountnumber not having value 'US_', I am getting only 300 records
> insertion. remaining around 700 values are not getting inserted.

How are you determining this?

How are you doing the INSERT?

Does the Postgres log show any errors when the INSERTs are done?

>
> why this strange behavior is happening, as I am not inserting any
> record having value 'US_' even after that all records are not
> inserting.
>
> Any suggestions are welcome.
>
>
>
> Regards,
> Atul
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com