dbxDataEvent_tableName_AfterInsertRowByKey

Function called after inserting a row in the table.

Syntax

PUBLIC FUNCTION dbxDataEvent_tableName_AfterInsertRowByKey(
   p_data record-type)
   RETURNS (INTEGER, STRING)

The function has the following parameter:

p_data. This is a RECORD type defined according to the structure of the database table.

It returns the following values:

An integer specifying the SQLCA.SQLCODE error number. Codes are defined as constants in the libdbappSql and libdbappCore files in the libdbapp library. The errors that are relevant in this function are shown in the table.
Table 1. SQLCA.SQLCODE

Description Constant Value

Success ERROR_SUCCESS 0
Failure ERROR_FAILURE -1
A string is returned with the SQLERRMESSAGE error message.

Table 1. SQLCA.SQLCODE
Description	Constant	Value
Success	`ERROR_SUCCESS`	0
Failure	`ERROR_FAILURE`	-1

Usage

When you select the After Insert Row By Key property for the creation of the event, a function shell is created. Enter your code in the function.

This function is called after inserting a row in the database table. Use this event for administration tasks, such as updating what might otherwise be performed in SQL triggers. For example, when a record is inserted in an orders table, the stock inventory table is also updated.

Note: It is recommended to avoid duplicating a SQL trigger in the database. If a database trigger exists, there is no need to use this function.

Example: After Insert Row By Key

This example uses the After Insert Row By Key code event for the lineitem table in the OfficeStore demo.

Transactions are used in the example to isolate the row. However, you can not use the standard Genero BDL BEGIN WORK / COMMIT WORK / ROLLBACK WORK instructions to implement transaction blocks in this function because nested transactions are used by the BAM. Nested transactions are transactions grouped in a single transaction. They are supported in Genero BDL. See the Genero Business Development Language User Guide. Therefore, you use the following:

libdbapp_begin_work() instead of BEGIN WORK
libdbapp_commit_work() instead of COMMIT WORK
libdbapp_rollback_work() instead of ROLLBACK WORK

You need to have an IMPORT FGL libdbappSql statement in the module where the function is created (officestore.4gl) to call these. This library has functions that are essentially wrappers for the functions in the fgldbutl database utility module.

In this example, the function tries to deduct the quantity in the lineitem table from the quantity in the qty field for the item in the inventory table. Depending on the result of the search for the item in the inventory table and the quantity there, an error number and message is returned. If an update is tried and is not successful, the roll back is called.

PUBLIC FUNCTION dbxDataEvent_lineitem_AfterInsertRowByKey(p_data RECORD LIKE lineitem.*)
    RETURNS (INTEGER, STRING)

    DEFINE errNo INTEGER
    DEFINE errMsg STRING
    
    DEFINE res INTEGER
    
    DISPLAY "dbxDataEvent_lineitem_AfterInsertRowByKey (Table scope) is raised"

    SELECT qty INTO res FROM inventory WHERE @itemid = p_data.itemid AND @qty > p_data.quantity
    IF SQLCA.SQLCODE == NOTFOUND THEN
        LET errNo = libdbappCore.ERROR_SUCCESS
        LET errMsg = "Nothing to do"
    ELSE
        LET errNo = libdbapp_begin_work()
        TRY
            UPDATE inventory SET qty = (res - p_data.quantity) WHERE @itemid = p_data.itemid
        CATCH
            LET errNo = libdbappCore.ERROR_FAILURE
            LET errMsg = "SQL ERROR: ", SQLCA.SQLCODE
        END TRY
        IF errNo == ERROR_SUCCESS THEN
            LET errNo = libdbapp_commit_work()
            LET errMsg = "Inventory updated"
        ELSE
            CALL libdbapp_rollback_work()
        END IF
    END IF
    DISPLAY "dbxDataEvent_lineitem_AfterInsertRowByKey (Table scope) is exited"
     
    RETURN errNo, errMsg
END FUNCTION