Thread: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
This is a trigger related question.
Table, test_part_details_all_mcm_init has a trigger, tr_test_part_details_all_mcm_init.
CREATE TRIGGER tr_test_part_details_all_mcm_init
AFTER INSERT
ON bx.test_part_details_all_mcm_init
FOR EACH ROW
EXECUTE FUNCTION bx.tr_fn_test_part_details_all_mcm_init();
Function, bx.tr_fn_test_part_details_all_mcm_init() INSERTs a row into another table, bx.test_part_details_all_mcm_mid
Table, bx.test_part_details_all_mcm_mid has a constraint,
CONSTRAINT cons_unique_for_concatenated_view UNIQUE (start_time_numeric, stop_time_numeric, test_action, part_type, fixture_id, run_id, auxid1_build_id, auxid2_asic_id, serial_number, part_pf)
When I INSERT a data into the table Table, test_part_details_all_mcm_init , trigger is not checking CONSTRAINT cons_unique_for_concatenated_view while INSERTing the data in bx.test_part_details_all_mcm_mid. I am expecting that anything violating the constraint cons_unique_for_concatenated_view will throw an error. Instead of that it is allowing to INSERT the data.
What is that I am missing here?
Thanks,
Sarwar
Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
Sent: Wednesday, May 22, 2024 6:38 PM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
Am 22.05.24 um 23:15 schrieb M Sarwar
Datum: 22. Mai 2024
An: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Cc:
Betreff: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
This is a trigger related question.
Table, test_part_details_all_mcm_init has a trigger, tr_test_part_details_all_mcm_init.
CREATE TRIGGER tr_test_part_details_all_mcm_init
AFTER INSERT
ON bx.test_part_details_all_mcm_init
FOR EACH ROW
EXECUTE FUNCTION bx.tr_fn_test_part_details_all_mcm_init();
Function, bx.tr_fn_test_part_details_all_mcm_init() INSERTs a row into another table, bx.test_part_details_all_mcm_mid
Table, bx.test_part_details_all_mcm_mid has a constraint,
CONSTRAINT cons_unique_for_concatenated_view UNIQUE (start_time_numeric, stop_time_numeric, test_action, part_type, fixture_id, run_id, auxid1_build_id, auxid2_asic_id, serial_number, part_pf)
When I INSERT a data into the table Table, test_part_details_all_mcm_init , trigger is not checking CONSTRAINT cons_unique_for_concatenated_view while INSERTing the data in bx.test_part_details_all_mcm_mid. I am expecting that anything violating the constraint cons_unique_for_concatenated_view will throw an error. Instead of that it is allowing to INSERT the data.
What is that I am missing here?
Thanks,
Sarwar
Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
My Postgress version is 13.5. I hope that it is not due to the version.
Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
Sent: Wednesday, May 22, 2024 8:35 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: wolters.k@web.de <wolters.k@web.de>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
My Postgress version is 13.5. I hope that it is not due to the version.
Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
On May 22, 2024, at 9:22 PM, M Sarwar <sarwarmd02@outlook.com> wrote:Hi David,I did verify at Ver 13 and this ( UNIQUE NULLS NOT DISTINCT ) syntax is not existing there. That means, I need to work on it without this option.My team will not upgrade from 13 due to number of reasons.
Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
Sent: Wednesday, May 22, 2024 10:42 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>; wolters.k@web.de <wolters.k@web.de>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
On May 22, 2024, at 9:22 PM, M Sarwar <sarwarmd02@outlook.com> wrote:Hi David,I did verify at Ver 13 and this ( UNIQUE NULLS NOT DISTINCT ) syntax is not existing there. That means, I need to work on it without this option.My team will not upgrade from 13 due to number of reasons.
Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
On May 23, 2024, at 4:30 PM, M Sarwar <sarwarmd02@outlook.com> wrote:Constraints are working as expected when I am working the table-2 directly.In my case, Table-2 needs to get INSERTed only when table-1 has INSERTs. Table-1 is working fine. Table-2 is also working fine when I am directly working in table-2.But when I Table-2 needs to have INSERTs based on the trigger invocation of Table-1, Table-2 is applying constraints which is causing issues.