I have a trigger that gets executed After All for a table. The trigger calls a stored procedure (SQL) that renumbers a column in the table (if necessary). Unfortunately the procedure gets a "record locked" error when it tries to update the row that was just modified.

I thought the row lock would have been released before the AFTER ALL trigger is executed. I'm testing this in EDB Mgr by updating the row in a Select statement which then executes the After All trigger.

TIA
Barry

Barry,

<<The trigger calls a stored procedure (SQL) that renumbers a column in the table (if necessary).>>

Altering the structure of a table requires an exclusive lock on that table; it's normal that such a lock fails while one of it's triggers is still being executed.

<< I thought the row lock would have been released before the AFTER ALL trigger is executed.>>

I'm not sure about this one, but as in SQL trigger execution is considered to be part of the operation that fires the triggers, I find natural that the row remains locked.

--
Fernando Dias
[Team Elevate]

Fernando Dias wrote:

Barry,

<<The trigger calls a stored procedure (SQL) that renumbers a column in the table (if necessary).>>

<Altering the structure of a table requires an exclusive lock on that table; it's normal that such a lock fails while one of it's triggers is still being executed.>

Sorry, there is only one question in my post. The table structure was not being changed. The trigger just renumbers the values of a column in the table, like a sequence number based on a date field. If the user enters a row with an earlier date, then the values in the sequence number column needs to be re-ordered.

<< I thought the row lock would have been released before the AFTER ALL trigger is executed.>>

<I'm not sure about this one, but as in SQL trigger execution is considered to be part of the operation that fires the triggers, I find natural that the row remains locked.>

If the row is still locked, how do I update the row that was just updated in the After All trigger?
If there is no way to update this row (which is what I suspect), why is the row still locked? What purpose does the lock serve at this point? If the row can't be updated by the current session, why is the lock still necessary?

TIA
Barry

Barry


Can you post your code - trigger and SP.

Roy Lambert [Team Elevate]

Barry,

<< The table structure was not being changed. The trigger just renumbers the
values of a column in the table, >>

Aaaah, I see
When you said you were renumbering the column, I though  you were altering
the column position in the table structure,
like in ALTER TABLE <Table> ALTER COLUMN <Column> MOVE TO <NewPosition> .

<<If the row is still locked, how do I update the row that was just updated
in the After All trigger?>>

Good question
I don't know if the row remains locked by design or not, we will have to ask
Tim, but in any case if it was possible to update the column from the
trigger, you would have to be very careful to avoid recursive trigger calls.

As Roy already asked, could you please post the trigger and SP so that we
have a clear idea of what you are trying to do?

--
Fernando Dias
[Team Elevate]

>>Can you post your code - trigger and SP.
Sure, here it is. The client will be entering Placement records in any date order because he has historical data to enter. I thought it would be neat if it recalcs Placement_Num automatically instead of forcing him to hit a button to do the recalc.

Synopsis: Each Placement record has a Placement_Date and a Placement_Num. The Placement_Num changes only when the date changes, and the Placement_Num starts at 1 for the earliest date and increments for each distinct date there-after. There can be 4 or 5 records with the same placement_date and they all have the same placement_num.

Barry


<code>

CREATE TRIGGER "trg_A_IUD_Placement" AFTER ALL ON "Placement"
WHEN (Operation()='Insert') or (Operation()='Delete') or (Operation()='Update' and NewRow.Placement_Date<>OldRow.Placement_Date)

BEGIN
 Call prc_renumberplacements;  --Renumber the Placement.Placement_Num
END


--------------------
CREATE PROCEDURE "prc_RenumberPlacements"
BEGIN
 Declare PL_Cursor   CURSOR  for Stmt;
 Declare _NumDates   Integer default 0;
 Declare _PLNum      Integer default 0;
 Declare _PLDate     Date    default 0;
 Declare _LastPLDate Date    default 0;
 Prepare Stmt from 'Select Placement_Num, Placement_Date from Placement order Placement_Date, Placement_Time';
 begin
    Open PL_Cursor;
    Fetch First from PL_Cursor ('Placement_Date', 'Placement_Num') into _PLDate, _PLNum;
    while not EOF(PL_Cursor) do
      if (_NumDates=0) or (_PLDate <> _LastPLDate) then
        set _NumDates   = _NumDates + 1;
        set _LastPLDate = _PLDate;
      end if;
      if (_PLNum is Null) or (_PLNum <> _NumDates) then
        execute immediate 'Update Placement set Placement_Num=? where Placement_Date=? and (Placement_Num<>? or Placement_Num is null)'
                using _NumDates,  _PLDate, _NumDates;
      end if;
      Fetch Next from PL_Cursor ('Placement_Date', 'Placement_Num') into _PLDate, _PLNum;
    End While;
 finally
    Close PL_Cursor;
    Unprepare Stmt;
 end;
END

DESCRIPTION 'Renumber placements according to Placement_Date. Each rcd with same Placement_Date get the same Placement_Num.'
VERSION 1.00
</code>

Barry


I think my brain is working a bit better today, and thinking about locks I'm guessing the locks won't be released until after all the triggers have finiished. My logic is based on the fact that you can change the column values for that row in the trigger, but to do so you have to use "special" syntax (NEWROW, OLDROW). If the lock had been released get ready for chaos.

With that preamble I think you can do one of two things

1. move all the code into the trigger and use special handling for the locked row
2. pass / retrieve variables into the SP (I think you can but I'm not sure) that will allow you to skip altering that row, returning the value back and then using that to update NEWROW.


Roy Lambert [Team Elevate]

ps What sort of app is it?

Roy, I think your solution is good, in fact the only approach I can think
of, but Barry would have to use BEFORE triggers because it's not allowed to
change the value of NEWROW in an AFTER trigger.
So, what I would do is to move the processing of inserts and updates to
BEFORE triggers and keep the renumbering after deletes in the AFTER trigger,
and exactly as Roy said, use NEWROW to set the new values.

--
Fernando Dias
[Team Elevate]

I forgot to say...
You also can't use NEWROW inside a stored procedure, you would have to move
the code to the BOFORE triggers.

--
Fernando Dias
[Team Elevate]

Fernando


Reading your post I just had a horrible thought - is this going to start a cascade?

I assume changes made in triggers can fire off a trigger (never tried it so don't know). If so - woops.

Roy Lambert [Team Elevate]