Re: Transactions and ID's generated by triggers

Thanks for the reply.

I will try the code tonight.

Best Regards,

On Sun, Feb 27, 2011 at 11:37 PM, David Johnston <polobo(at)yahoo(dot)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 issue a select - assuming you can identify the record in tdir_uris_files that you need.
>
> David J.
>
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)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,
>
>