[Dbix-class] Use autoincremented value in other column

[Rob Kinyon]

On Thu, Mar 26, 2009 at 12:12, David Schmidt <davewood at gmx.at> wrote:
> On Thu, Mar 26, 2009 at 3:56 PM, Rob Kinyon <rob.kinyon at gmail.com> wrote:
>> On Thu, Mar 26, 2009 at 10:48, David Schmidt <davewood at gmx.at> wrote:
>>> Hello everyone,
>>>
>>> Is there any way (without transactions or 2 DB trips) to achieve the following:
>>>
>>> Insert a row and use the autoincremented primary key value in another column.
>>>
>>> table: pictures
>>> id integer primary key,
>>> filename varchar2(128)
>>>
>>> my $picture = $c->model('myDB::Pictures')->new_result({
>>>                                           filename => <ID> . '_' . $filename
>>>                                });
>>
>> If you weren't using DBIC, how would you solve this?
>>
>> --
>> Thanks,
>> Rob Kinyon
>>
>> _______________________________________________
>> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>> IRC: irc.perl.org#dbix-class
>> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>>
>
> Hey Rob,
>
> Well, I dont want to use a RDBMS specific solution using triggers.
>
> What I'd like to do would be to instruct DBIC to concatenate the value
> I supply for the column with the autoincremented integer upon insert.
> For Oracle I'd probably write somehing like "INSERT INTO pictures
> (filename) VALUES (SEQUENCE.NEXT . 'my_filename')" (untested and not
> sure about the correct syntax here but I hope I made myself clear)
>
> But I really want to do this on the DBIC side so i can switch DB later.

The only way to do it on the client-side (which is where DBIC sits) is
to do the insert, then update the row immediately once we know what
the value for the PK will be. So, you'd write a post-insert trigger.
Ideally, you would do this within txn_do(), but that's just my
paranoia.

-- 
Thanks,
Rob Kinyon