Atomic SP

[Bart Pietercil]

Hi Ruslan,

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 ?

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?

Or is the SP atomic and are all other calls serialised ?
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;

tia

Bart Pietercil