Thread: Transactions and ID's generated by triggers
Hi, I have a situation that I dont know how to deal. I have 2 tables "tdir_uris_files" and "tdir_uri_files_details". Please see the Image in attach. The table "tdir_uris_files" have the field "id_ordinal" that is originated by a trigger(before insert) The table "tdir_uri_files_details" use the field "id_ordinal" generated by trigger on the insert on the table "tdir_uris_files" Now my doubt. It is possible to do a transaction to this two tables at the same time? How can I know the value of the field "id_ordinal" that was generated by the trigger? Can I be able to do this? [code] BEGIN; INSERT INTO tdir_uris_files (uri, id_language, id_category, id_file_context, id_encode_format, n_file, file) values (...) INSERT INTO tdir_uri_files_details (uri, id_language, id_category, id_file_context, id_ordinal, id_file_type, id_file_detail, value) values (...); COMMIT; [/code] PS: Sorry my bad english. Best Regards,
Attachment
Using pl/pgsql you can: .... DECLARE idordinal type; BEGIN INSERT INTO tdir_uris_files RETURNING id_ordinal INTO idordinal; INSERT INTO tdir_uris_files_details (id_ordinal) VALUES (idordinal); END; .... Similar results are possible in other environments. If you do not have access to "RETURNING" for some reason you can issuea select - assuming you can identify the record in tdir_uris_files that you need. David J. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andre Lopes Sent: Sunday, February 27, 2011 3:34 PM To: postgresql Forums Subject: [GENERAL] Transactions and ID's generated by triggers Hi, I have a situation that I dont know how to deal. I have 2 tables "tdir_uris_files" and "tdir_uri_files_details". Please see the Image in attach. The table "tdir_uris_files" have the field "id_ordinal" that is originated by a trigger(before insert) The table "tdir_uri_files_details"use the field "id_ordinal" generated by trigger on the insert on the table "tdir_uris_files" Now my doubt. It is possible to do a transaction to this two tables at the same time? How can I know the value of the field "id_ordinal" that was generated by the trigger? Can I be able to do this? [code] BEGIN; INSERT INTO tdir_uris_files (uri, id_language, id_category, id_file_context, id_encode_format, n_file, file) values (...)INSERT INTO tdir_uri_files_details (uri, id_language, id_category, id_file_context, id_ordinal, id_file_type, id_file_detail,value) values (...); COMMIT; [/code] PS: Sorry my bad english. Best Regards,
Thanks for the reply. I will try the code tonight. Best Regards, On Sun, Feb 27, 2011 at 11:37 PM, David Johnston <polobo@yahoo.com> wrote: > Using pl/pgsql you can: > .... > DECLARE idordinal type; > BEGIN > INSERT INTO tdir_uris_files RETURNING id_ordinal INTO idordinal; > INSERT INTO tdir_uris_files_details (id_ordinal) VALUES (idordinal); > END; > .... > > Similar results are possible in other environments. If you do not have access to "RETURNING" for some reason you can issuea select - assuming you can identify the record in tdir_uris_files that you need. > > David J. > > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andre Lopes > Sent: Sunday, February 27, 2011 3:34 PM > To: postgresql Forums > Subject: [GENERAL] Transactions and ID's generated by triggers > > Hi, > > I have a situation that I dont know how to deal. > > I have 2 tables "tdir_uris_files" and "tdir_uri_files_details". Please see the Image in attach. > > The table "tdir_uris_files" have the field "id_ordinal" that is originated by a trigger(before insert) The table "tdir_uri_files_details"use the field "id_ordinal" > generated by trigger on the insert on the table "tdir_uris_files" > > > Now my doubt. It is possible to do a transaction to this two tables at the same time? > > How can I know the value of the field "id_ordinal" that was generated by the trigger? > > Can I be able to do this? > > [code] > BEGIN; > INSERT INTO tdir_uris_files (uri, id_language, id_category, id_file_context, id_encode_format, n_file, file) values (...)INSERT INTO tdir_uri_files_details (uri, id_language, id_category, id_file_context, id_ordinal, id_file_type, id_file_detail,value) values (...); COMMIT; [/code] > > PS: Sorry my bad english. > > Best Regards, > >
On 28 Feb 2011, at 24:37, David Johnston wrote: > Using pl/pgsql you can: You don't need pl/pgsql for that. You can return the resultset of the first insert into a client-side variable (while stillmaking use of the RETURNING-clause of course). Of course, the benefit of using pl/pgsql for this is that you could make the desired behaviour independent of client-implementations(which are all too likely to differ if there are multiple types of clients). However, for that to workreliably you will need to do some extra work to make sure it's the only possible code-path for inserting those records. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4d6bf0da235882099214672!
Hi, Thanks for the reply. How can I user the RETURNING without pl/sql? My PostgreSQL version is 8.3 Can you give me some example? Best Regards, On Mon, Feb 28, 2011 at 7:00 PM, Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote: > On 28 Feb 2011, at 24:37, David Johnston wrote: > >> Using pl/pgsql you can: > > You don't need pl/pgsql for that. You can return the resultset of the first insert into a client-side variable (while stillmaking use of the RETURNING-clause of course). > > Of course, the benefit of using pl/pgsql for this is that you could make the desired behaviour independent of client-implementations(which are all too likely to differ if there are multiple types of clients). However, for that to workreliably you will need to do some extra work to make sure it's the only possible code-path for inserting those records. > > Alban Hertroys > > -- > If you can't see the forest for the trees, > cut the trees and you'll see there is no forest. > > > !DSPAM:921,4d6bf0d6235881485283256! > > >
On 28 Feb 2011, at 22:07, Andre Lopes wrote: > Hi, > > Thanks for the reply. > > How can I user the RETURNING without pl/sql? My PostgreSQL version is 8.3 > > Can you give me some example? http://www.postgresql.org/docs/8.3/interactive/sql-insert.html Most notably the last example on that page. > On Mon, Feb 28, 2011 at 7:00 PM, Alban Hertroys > <dalroi@solfertje.student.utwente.nl> wrote: >> On 28 Feb 2011, at 24:37, David Johnston wrote: >> >>> Using pl/pgsql you can: >> >> You don't need pl/pgsql for that. You can return the resultset of the first insert into a client-side variable (whilestill making use of the RETURNING-clause of course). >> >> Of course, the benefit of using pl/pgsql for this is that you could make the desired behaviour independent of client-implementations(which are all too likely to differ if there are multiple types of clients). However, for that to workreliably you will need to do some extra work to make sure it's the only possible code-path for inserting those records. >> >> Alban Hertroys >> >> -- >> If you can't see the forest for the trees, >> cut the trees and you'll see there is no forest. >> >> >> >> >> >> > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4d6c2543235881724113336!