Data changes: Row creation (INSERT)

To insert a new row with a serial or sequence primary key, generate a local unique negative ID, include the auto-incremented column in the INSERT statement, and register the change in the local sync log. Handle errors with rollback mechanisms to ensure data integrity.

Before inserting a new row in the local database, if the primary key is a serial or sequence column, get a local unique negative id with dbsync_app.new_temp_autoincr():

 LET r_customer.customer_id = dbsync_app.new_temp_autoincr("customer")

Note:

The INSERT statement must include the auto-incremented column. If the .sch schema file defines the column as SERIAL or BIGSERIAL, an INSERT in the form INSERT INTO tab VALUES rec.* will exclude the auto-incremented column. In such case, you just need to put parentheses arround rec.* to get VALUES(rec.*)

After doing the local INSERT, register the change in the local sync log with the dbsync_app.register_local_insert() function:

BEGIN WORK
TRY
    INSERT INTO customer ... VALUES ( r_customer.customer_id, ... )
    LET s = dbsync_app.register_local_insert( "customer",
                                              r_customer.customer_id )
    IF s < 0 THEN
        ERROR "Failed to register customer row creation"
        ROLLBACK WORK
        NEXT FIELD CURRENT
    END IF
CATCH
    ERROR "Failed to insert new customer row"
    ROLLBACK WORK
    NEXT FIELD CURRENT
END TRY
COMMIT WORK

If a change record is already registered for the row, and error is returned.