@BEFORE - connect_cdc_sqdata - Latest

The @BEFORE function returns the before image of a source field or column following an Update operation. For deletes and inserts it return the current value. It also provides access to the before key column values of relational targets to support Key Changes.

@BEFORE does not provide access to the raw data of the before image. The before image has already been processed using default and user specified data rules. This includes data type and EXTTYPE validation, error processing and code page translations. @BEFORE is most often used to implement conditional business logic based on a change to the value of a particular source field/column. It is also used to support changes to the value of a column when replicating changes to a Relational target when using the APPLY command to write to the Target datastore.

Category

Changed Data Capture

Syntax

<source_description>.<source_field>@BEFORE | <target_description>.<target_field>@BEFORE

Example 1

IF CDCOP(CDCIN) = 'U'
{
   T_EMPLOYEE.SITE_CD@BEFORE       = EMPLOYEE.SITE_CD@BEFORE
   T_EMPLOYEE.STATE_CD@BEFORE      = EMPLOYEE.STATE_CD@BEFORE
   T_EMPLOYEE.OFF_ZIP_CD@BEFORE    = EMPLOYEE.OFF_ZIP_CD@BEFORE
}

-- EMPLOYEE.EMP_NO in this example cannot be changed and therefore is not specified above.

Example 2

T_PREV_SRC_FLD1 = SRC_FLD1@BEFORE

Example 3

IF SOURCE.STATUS@before = 'OPEN' AND SOURCE.STATUS = 'CLOSED'
{
   CALLPROC(CLOSING)
}

Example 4

CASE
WHEN SRC_FLD1@BEFORE = SRC_FLD1
{
   CHGFLAG = 'N'
}
OTHERWISE
{
  CHGFLAG  =  'Y'
}