Re: trigger impacting insertion of records

just simplified, but it works fine for me.

create table example(id int primary key, value text);

create or replace function trg_fn() returns trigger language plpgsql as $$
begin
RAISE NOTICE 'trigger_func(%) called: action = %, when = %, level =
%', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
RAISE NOTICE 'id=%, value=%', NEW.id, NEW.value;
update example set value=replace(value,'_',' ') where left(value,3)
= 'US_';
return new;
end; $$;

create trigger after_insert_trigger after insert ON example for each row
execute function trg_fn();

insert into example select x, case when x % 2 = 0 then 'US_' || x::text
else x::text end from generate_series(1, 100) x;

NOTICE: trigger_func(<NULL>) called: action = INSERT, when = AFTER, level
= ROW
NOTICE: id=99, value=99
NOTICE: Returned 0 rows
NOTICE: trigger_func(<NULL>) called: action = INSERT, when = AFTER, level
= ROW
NOTICE: id=100, value=US_100
NOTICE: Returned 0 rows
INSERT 0 100

-- do not see any values with US_, although i inserted 50 of them.
postgres=# select count(*) from example where value like 'US\_%';
count
-------
0
(1 row)

-- do see 50 "US<space>" values as expected.
postgres=# select count(*) from example where value like 'US %';
count
-------
50
(1 row)

Can you verify accountnumber field does not have any spaces etc at the
beginning.

unless there is some conflicting stuff modifying rows, i think this should
be ok.
you can
lock TABLE example IN exclusive mode; -- DO NOT DO IT IF IT IMPACTS
ANYTHING IN PRODUCTION

On Thu, 6 May 2021 at 13:15, Atul Kumar <akumar14871(at)gmail(dot)com> 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.
>
> 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
>
>
>

--
Thanks,
Vijay
Mumbai, India