CREATE statement

Creates a record in a table, sets all the fields in the record to their default initial values, and moves a copy of the record to the record buffer.

Data movement

Syntax

CREATE record [ FOR TENANT tenant-expression ]
[ USING { ROWID ( nrow ) | RECID ( nrec ) } ] [ NO-ERROR ]

record

The name of the record or record buffer you are creating.

To create a record in a table defined for multiple databases, you might have to qualify the record's table name with the database name. See the record definition in the Record phrase reference entry for more information.

FOR TENANT tenant-expression

This option is useful only for a multi-tenant database, and primarily one with a connection identity that has super tenant access. If the user has a super-tenant connection identity and you do not specify this option, the record you create is owned by the effective tenant. If you do specify this option, you create a record owned by the regular tenant identified by tenant-expression.

If the user has a regular-tenant connection identity, and you specify this option, tenant-expression must match the tenancy of the connection identity. Otherwise, the statement raises ERROR.

If tenant-expression evaluates to an integer, the value must be a valid tenant ID for a regular tenant or zero (0) for the default tenant. If tenant-expression evaluates to a character string, the value must be a valid tenant name for a regular or "Default" for the default tenant. Otherwise, the statement raises ERROR.

If record belongs to a table that is not multi-tenant enabled, ABL raises a compiler error.

USING { ROWID ( nrow ) | RECID ( nrec ) }

Supported only for backward compatibility.

NO-ERROR

The NO-ERROR option is used to prevent the statement from raising ERROR and displaying error messages.

Example

The following example creates a record in the order file for each pass through the loop and then updates the record. It also creates an order-line record.

r-create.p

REPEAT:
  CREATE Order.
  UPDATE Order.OrderNum Order.CustNum 
    VALIDATE(CAN-FIND(Customer OF Order), "Customer does not exist")
    Order.CustNum Order.OrderDate.
  REPEAT:
    CREATE OrderLine.
    OrderLine.OrderNum = Order.OrderNnum.
    UPDATE OrderLine.LineNum OrderLine.ItemNum 
      VALIDATE(CAN-FIND(Item OF OrderLine), "Item does not exist")
      OrderLine.Qty OrderLine.Price.
  END.
END.

This procedure adds Orders and OrderLines to the database. Because the user supplies an order number when updating the order record, that order number is assigned (=) to the OrderNum field of the OrderLine record when the OrderLine record is created.

Notes

• When you run procedures that create large numbers of records (for example, during initial data loading), the process runs much faster if you use the No Crash Protection (-i) parameter. See Startup Command and Parameter Reference for more information on startup parameters. Back up your database before you use this parameter.
• After you create a new record with CREATE, the AVM waits to write the record to the database. The creation often happens after one of the following:
  • The AVM is about to release the buffer, which can happen as a result of an explicit RELEASE or VALIDATE statement, the reading of a new record into the buffer, the record going out of scope, or the end of the transaction.
  • You set a LOB field.
  • You set a field that an index is based on.
• All partition fields must be set by the time a record is created in a partitioned database.

  Suppose you have an Order table partitioned on OrderDate, but with a global index on OrderNum. If OrderDate has an initial value of the Unknown value (?), the following code will produce an error:

  CREATE Order. OrderNum = NEXT-VALUE(Next-Ord-Num). OrderDate = TODAY.

  After the assignment of the OrderNum field, the AVM will try to index the new OrderNum in the OrderNum global index. To do that, it needs the partition of the new Order record. To get the partition, the AVM needs to use the partition column OrderDate, which is still the Unknown value (?). This will produce an error message stating that OrderDate is not in any defined partition. You can fix this by reversing the order of the assignments, grouping the statements into one ASSIGN statement, or setting an initial value for the OrderDate column. If you use one ASSIGN statement, the actual create of the record won't occur until after all the specified field assignments are gathered. Therefore the OrderDate column will already have the correct value by the time the partition for the new record needs to be determined.

  Note: Even if all of the partition fields have initial values, an application might be inefficient if the underlying record creation happens before the partition fields are set to something other than the initial values. In this case, the AVM may have to move the record to a new partition once the initial value of the partition field is replaced.
• The CREATE statement causes any related database CREATE triggers to execute. All CREATE triggers execute after the record is actually created. If a CREATE trigger fails (or executes a RETURN statement with the ERROR option), the record creation is undone.
• When specifying the FOR TENANT option, the AVM looks up tenant-expression in the database with a share lock. The AVM waits 60 seconds to get the share lock and raises ERROR if it fails to obtain the share lock in that amount of time. The AVM releases the share lock immediately after successfully fetching the row. This share lock is released even if the statement is called while in the scope of a transaction.

See also

BUFFER-CREATE( ) method, INSERT statement, NEW function (record buffers), NO-ERROR option