update_recordset: SQL Ran, and update() Never Did

The overdue job used to loop sales lines, set a flag, and call update(). A Chain of Command method on SalesLine.update() stamped TAGOverdueCheckedBy and inserted a row in TAGOverdueLog. Someone rewrote the job with update_recordset because Trace Parser showed tens of thousands of round trips. The run was green. The flag flipped. TAGOverdueCheckedBy stayed blank. The log table was empty. update_recordset is not update().

That is the whole property. insert_recordset, update_recordset, and delete_from send one SQL statement. They do not construct a table buffer per row. They do not run insert, update, or delete. They do not run validateWrite. They do not fire DataEventType::Updating. If your correctness lives in those methods, a set-based rewrite silently drops it.

The performance post is why you want the SQL. This post is what that SQL refuses to do. ExecuteUpdate skipping interceptors is the same hole in EF Core.

One statement, no buffer

SalesLine salesLine;

ttsbegin;
update_recordset salesLine
    setting TAGOverdueFlag = NoYes::Yes,
            TAGOverdueDate = today()
    where salesLine.SalesStatus == SalesStatus::Backorder
       && salesLine.ReceiptDateRequested < today();
ttscommit;

SQL Server gets one UPDATE SALESLINE SET ... WHERE .... The AOS does not new up a SalesLine for each match. Your [ExtensionOf(tableStr(SalesLine))] update() method is not on the stack. Neither is onUpdating. modifiedField never ran: there was no buffer, only a column in a SET clause.

The shape that does run them is the loop the performance post tells you to stop writing for a large set:

while select forupdate salesLine
    where salesLine.SalesStatus == SalesStatus::Backorder
       && salesLine.ReceiptDateRequested < today()
{
    ttsbegin;
    salesLine.TAGOverdueFlag = NoYes::Yes;
    salesLine.TAGOverdueDate = today();
    salesLine.update();
    ttscommit;
}

Both statements are true at once. Set-based is the right write when the write is the assignment. It is the wrong write when update() is a business operation — a stamp, a log, a number, a refusal.

What the kernel skips on purpose

Mechanism	update() / insert() / delete()	Set-based statement
Table insert / update / delete, including CoC	Runs	Skipped
validateWrite / validateDelete	Runs	Skipped
aosValidateInsert / Update / Delete	Runs	Skipped by default
DataEventType inserting / updating / deleting	Runs	Skipped by default
Database log	Writes	Skipped by default
modifiedField	Already ran on the buffer	Never ran
Number sequence called from insert()	Allocates	Does not allocate
OccEnabled / RecVersion compare	Per row, can fail the write	No per-row compare

System columns still land. insert_recordset gets RecId, DataAreaId, CreatedBy, CreatedDateTime, RecVersion from the AOS and SQL. Your insert() body is what is missing: the number sequence newGetNum, the satellite row, the extra stamp. CreatedBy is who ran the statement, not a substitute for a column your method used to set.

The skip flags on the buffer exist because the kernel already skipped:

salesLine.skipDataMethods(true);
salesLine.skipEvents(true);
salesLine.skipDatabaseLog(true);
salesLine.skipAosValidation(true);

Setting them to false does not bolt methods onto a SQL statement. It asks the kernel to honor those side effects. When it cannot do that as SQL, it falls back to row-by-row. No compile error — a slow job. Trace Parser is the check: one UPDATE whose duration matches the set means you got SQL; thousands of short UPDATEs mean you got the loop. Fallback does call update(), which is how teams "fix" a missing log by turning skips off and then wonder why the overnight job is a loop again.

Maps, containers, and a new RecordInsertList inside insert() are reasons the kernel cannot honor "also run the method" as SQL. If insert() must run, do not write insert_recordset. Write the loop, or split the work so the SQL statement has nothing to call back into. A table with an overridden update() is not forbidden from set-based writes. The default is to skip that method. The job in the first paragraph was green because skip is the feature, not a bug the compiler will catch.

skipAosValidation(true) on a row write is a different knob. Integrations use it with insert() or doInsert() to load history that would fail current rules. It does not make update_recordset call update(). Do not copy it onto a set-based job because the name sounds like performance. History loads only, and never on a path that later posts.

doInsert is still a row

salesLine.doInsert();
salesLine.doUpdate();
salesLine.doDelete();

doUpdate skips the X++ update() method. It still sends one row through the AOS. CoC on update() does not run. Table events do not run unless you left events on. It is the escape hatch when update() does work you must not repeat — a data repair, a copy — not a bulk API.

RecordInsertList is a batched doInsert. The second constructor argument is skipInsertMethod:

RecordInsertList list = new RecordInsertList(
    tableNum(TAGOverdueLog),
    true);  // skip insert()

list.add(logBuffer);
list.insertDatabase();

Mix insert() and RecordInsertList on the same table and you have two code paths. The log table in the first paragraph had CoC on insert(). The list never called it. Default new RecordInsertList(tableNum(...)) does not skip insert() — the boolean defaults to false — so methods can still run for a batch you thought was silent.

Stamp in the SET, or do not use SET

The overdue flag belongs in SQL. The user id belongs in SQL too, if that is all update() was doing:

SalesLine salesLine;
UserId    userId = curUserId();
TransDate asOf   = today();

ttsbegin;
update_recordset salesLine
    setting TAGOverdueFlag      = NoYes::Yes,
            TAGOverdueDate      = asOf,
            TAGOverdueCheckedBy = userId
    where salesLine.SalesStatus == SalesStatus::Backorder
       && salesLine.ReceiptDateRequested < asOf;
ttscommit;

If update() also inserts a log row, that insert is a second statement. Either insert_recordset the log from a TempDB staging table, or keep the while select forupdate loop, because the business operation is "flag plus log," not "set two columns."

Do not update_recordset and then loop the same rows to write the log. You scanned twice and still have a loop. Stage, then insert:

TAGOverdueStaging staging;
TAGOverdueLog     log;
UserId            userId = curUserId();
TransDate         asOf   = today();
utcDateTime       asOfDt = DateTimeUtil::utcNow();

delete_from staging;

insert_recordset staging (SalesId, LineNum, CheckedBy)
    select SalesId, LineNum, userId from salesLine
        where salesLine.SalesStatus == SalesStatus::Backorder
           && salesLine.ReceiptDateRequested < asOf;

insert_recordset log (SalesId, LineNum, CheckedBy, CheckedDateTime)
    select SalesId, LineNum, CheckedBy, asOfDt from staging;

userId and asOfDt in that select list are constants for the statement — "who ran the job" and when. If each row must capture a different value from a joined table, put that column in the select list from the source.

Set-based updates increment RecVersion. They do not fail because another session already changed the row. There is no version in the WHERE unless you write one. A clerk who had the sales line open then gets "The record has been updated by another user" on save. CacheLookup Found can still serve a stale buffer on another session after your statement commits. Set-based does not flush every AOS.

validateWrite never had a chance

Row writes go validateWrite then update. Set-based writes go to SQL. A CoC validateWrite that rejects a negative TAGMargin does not run. The job can set a field the form would refuse.

If the set must be valid, put the rule in the WHERE. A pre-pass is for operators; the update is the guard:

while select salesLine
    where salesLine.SalesStatus == SalesStatus::Backorder
       && salesLine.ReceiptDateRequested < asOf
       && salesLine.TAGMargin < 0
{
    throw error(strFmt("Line %1 %2 has a negative margin.",
        salesLine.SalesId, salesLine.LineNum));
}

ttsbegin;
update_recordset salesLine
    setting TAGOverdueFlag = NoYes::Yes,
            TAGOverdueDate = asOf
    where salesLine.SalesStatus == SalesStatus::Backorder
       && salesLine.ReceiptDateRequested < asOf
       && salesLine.TAGMargin >= 0;
ttscommit;

A line can still turn negative between the check and the update. If that window matters, you are not in set-based territory. You need select forupdate so the validation and the write share a transaction.

delete_from is the same hole on the way out. validateDelete and delete() do not run. Cascades you implemented in delete() do not run. Kernel relations and SQL foreign keys still do. If deleting a header must delete TAGOverdueLog, delete_from the satellite in the same ttsbegin, or stay on header.delete().

What to write before it ships

• The assignment is the operation. Status flag, date stamp, user id, a counter you can express in the setting clause: update_recordset. Put every column update() used to stamp into the setting list.
• The method is the operation. Posting, number sequences, inventory, a satellite insert, a refusal in validateWrite: while select forupdate and update(), or a set-based write of a staging table plus a second statement you control.
• A one-off data repair that must not re-enter insert(): doInsert / doUpdate / RecordInsertList with skipInsertMethod stated in the call.
• Never skipAosValidation(true) on a path that later posts.
• Never update_recordset and then reread() expecting CoC to catch up. The row is already written.

The form still calls write() then validateWrite then update(). Set-based does not skip that path for a clerk. It is not research vs executeQuery: that is a cursor, this is a write. It is not faster posting. Posting classes call update() because the method is the posting. Set-based the bookkeeping you own.

The overdue job was green because SQL set two columns. The method you trusted to set the third and write the log was never a SQL column. Put those values in the setting clause, or put the job back on update(). Then the flag, the user, and the log agree, because they were written by the same kind of statement.