Roy,
   There are no cascading locks. The SP code only executes if Placement_Date has been modified in an update. Since the SP only updates the Placement_Num, there are no recursive updates/locks. Sure the trigger gets called indirectly when the SP updates the Placement_Num, but the trigger ignores it if NewRow.Placement_Num = OldRow.PlacementNum.

Sorry I didn't get back to you sooner, but I was hammering the code into submission yesterday.

Barry

John,
   I agree totally if it was a busy multi-user site. But only a single user will be doing updates on it. The Placement_Num will only be updated if needed so the trigger won't do anything most of the time because the Placement_Num's had been previously ordered. It is only when a new record Placement_Date is entered out of sequence, will the trigger actually update the other records.

As far as calculating the Placement_Num at runtime for reports or grids, the Placement_Num should not change because of filters or sorting. It has to be permanently linked or stored with the record. But your suggestions does have merit. The Placement_Num's could be stored in a second table along with the Placement_Date. The trigger could maintain this secondary table instead of its own table. The grids and reports could then use the Placement_Date to look up the Placement_Num into this secondary table. This will eliminate the trigger from getting called "recursively" and ignored. Hmmm. I may give it a try. Thanks.

Barry

Barry

> As far as calculating the Placement_Num at runtime for reports or grids, the Placement_Num should not change because
of filters or sorting. It has to be permanently linked or stored with the record. But your suggestions does have merit.
The Placement_Num's could be stored in a second table along with the Placement_Date. The trigger could maintain this
secondary table instead of its own table. The grids and reports could then use the Placement_Date to look up the
Placement_Num into this secondary table. This will eliminate the trigger from getting called "recursively" and ignored.
Hmmm. I may give it a try. Thanks.

Obviously you know your requirement better than me, but it was because Placement_Nums can/will change that I was
suggesting "late binding".

One possible way is with a view - something like

SELECT Table.*,
(SELECT Placement_Num FROM
(SELECT Adate,RUNSUM(1) AS Placement_Num FROM
(SELECT DISTINCT Adate FROM Atable) T1 GROUP BY Adate ORDER BY Adate)
FROM Atable

As I understand it this view can then be treated like a Table - queried,filtered without adversely affecting
Placement_Num.

John

"John Hay" wrote:

>One possible way is with a view - something like
>
>SELECT Table.*,
>(SELECT Placement_Num FROM
>(SELECT Adate,RUNSUM(1) AS Placement_Num FROM
>(SELECT DISTINCT Adate FROM Atable) T1 GROUP BY Adate ORDER BY Adate)
>FROM Atable
>
>As I understand it this view can then be treated like a Table - queried,filtered without adversely affecting
>Placement_Num.

John,

I don't quite understand your SQL. But along the same lines I could use a view to give me the distinct Placement_Num from the Table1.Placement_Date.

For forms or grids, I could then define a Table1.Placement_Num column that is computed by a stored function to retrieve the Placement_Num from the view for that Placement_Date. This allows the table to be sensitive (updateable). I suspect using an equi-join between Table1 and the view would be faster for reports. But Table1 will have at most a few hundred rows so I may stick with a computed column for now.

At least this should eliminate maintaining Placement_Num in Table1 or in a separate table.
What do you think?

Barry