Forum OpenACS Q&A: Response to This is a little off topic, its a nstcl question

Marc, "on_error" is not going to do what you want. If 0 rows are updated, that's perfectly fine and not an error. In the ACS 4.x db_api, what you'd want it db_resultrows, which returns the number of rows affected by the last DML command. So, if { [db_resultrows] == 0 } then you'd do an insert - all within a single transaction of course, like you already said.

Unfortunately, at least as of v. 0.6, nstcl does not have db_resultrows - I think because nstcl's underlying database drivers (Oratcl, etc.) do not support that functionality. The AOLserver ns_db API doesn't have the "resultrows" feature either - in ACS, it's implemented with with a feature specific to each AOLserver database driver, and a given driver may or may not support it. For example, on my (modified) ACS 4.2 system:

ad_proc db_resultrows {{ -dbn "" }} {
   Returns the number of rows affected by the last DML command. 

   @param dbn The database name to use.  If empty_string, uses the default database.
} {
   upvar "#0" [db_state_array_name_is -dbn $dbn] db_state

   set driver_is [db_driver_is [db_driver_type_is -dbn $dbn]]
   switch $driver_is {
      oracle {
         return [ns_ora resultrows $db_state(last_used)]
      postgres {
         return [ns_pg ntuples $db_state(last_used)]
      nsodbc {
         error "db_resultrows is not supported for this database."
      default {
         error "Unknown database driver.  db_resultrows is not supported for this database."

There's probably some other way to do this, but off hand, I can't think of one. I would dig into the code and/or examples of whatever your underlying Tcl database driver is. I'm not familiar with pgtclsh, so maybe it supports resultrows functionality even though Michael didn't wrap it in his nstcl package.

Well, you could always start the transaction, try the insert first, and then do the update in the on_error block of db_transaction, when the insert dies due to referential integrity constraints. Make very sure you have all the correct referential integrity constraints first, of course...