Atomic SP

[Ruslan Zasukhin]

On 9/27/08 8:40 PM, "Bart Pietercil" <bart.pietercil at cognosis.be> wrote:

Hi Bart,

> working on my stored procedures.
> I wonder if when I do an insert inside a stored procedure and on the
> next line I call last_rec_id is it possible that another id than the
> one I am looking for (the record  inserted in the line before) is
> returned ?
> 
> This one I can answer with Yes, when in an after insert trigger
> another record is created (for instance a datalogger record)
> 
> But now I wonder what will happen when I kill the triggers ?

Other users do not interrupt current procedure and all its call tree.

> Is there another way another recid could sneak in between
> line1(create) and 2(select lastid) ?
> For instance from another client another record is created in the
> split second?

Never from another client

> Or is the SP atomic and are all other calls serialised ?

yes

> I suppose yes but would like a reassurance.
> 
> 
> SO this function (incomplete but you get the idea) is thrustworthy ?
> 
> create or replace function
> cm_php_evaluations_PeriodNew_forJobTicket(in jtHolder long,periodStart
> Date,periodEnd Date) returns long
> begin
> Declare OpenStatusValue as integer;
> declare NewPeriodID as long
> SET OpenStatusValue = 1; --'Open' in EvaluationTypes list
> insert into  
> tbl_evaluations 
> (objptr_jobticket_holder
> ,objptr_evaluation_status
> ,objptr_organisation
> ,period_end,period_start,creation_dt,objptr_created_by) values
> (jtHolder 
> ,OpenStatusValue, at curr_org,periodStart,periodEnd,now(), at user_id);
> SET NewPeriodID = SELECT Last_RecID();
> exception
> when others then
> 
> return NewPeriodID
> end;

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]