For rs_update, rs_insert, and rs_delete, the yielding clause introduces before and after images of the row affected by the operation. Depending on the operation, the before image, the after image, or both, must be provided. For rs_truncate, the yielding clause is empty. Table B-4 shows which operations require before and after images:
Operation |
Before image |
After image |
|---|---|---|
rs_update |
Yes |
Yes |
rs_insert |
— |
Yes |
rs_delete |
Yes |
— |
The table.function_name form of the applied subcommand is used to distribute replicated stored procedures when you use the method associated with table replication definitions. This method is described in Appendix A, “Asynchronous Procedures,” in the Replication Server Administration Guide Volume 2.
The preferred method for replicating stored procedures,
which uses applied and request functions, is described in Chapter
10, “Managing Replicated Functions,” in the Replication
Server Administration Guide Volume 1. This method uses
the execute subcommand to distribute replicated
stored procedures (known as replicated functions).
If the stored procedure execution results in an insert or delete operation, RepAgent converts it to an rs_insert or rs_delete LTL command. If the execution results in an update operation, RepAgent uses the function_name form and supplies the before and after images of the updated row to the Replication Server.
A Replication Server function with the same name and parameters as the stored procedure is defined with the create function command, and the function_name in the applied command references this function. The param_list following the function name is the list of parameters of the stored procedure.
The yielding clause contains before and after images of the table row modified by the function. Subscriptions on that table determine where the function is distributed.
Before and after images are specified by a param_list, which is a list of column or parameter values. The syntax for param_list is:
[@param_name=]literal[, [@param_name=]literal]...
param_name is a column name or, for replicated stored procedures, a parameter name.
literal is the value of the column or parameter.
All column names in the replication definition must appear in the list. Replication Server ignores any additional columns. Column or parameter names can be omitted if the values are supplied in the same sequence as they are defined in the replication definition. If the column names are included, you can list them in any order, although there is a performance advantage if the columns are supplied in replication definition order.
Replication Server version 10.1 and later supports an optimized yielding clause. An after image value can be omitted if it is the same as the before image value. For example, if a table has three columns a, b, and c, for an update where only column b changes, the yielding clause could be:
yielding before @a=5, @b=10, @c=15 after @b=12
If
the minimal columns feature is used, a RepAgent using LTL version 101 or later must omit
identical after images. See the create replication definition command
in the Replication Server Reference Manual for
more information about replicating minimal columns.
