Use transaction fields

Custom trigger code can access the columns of the transaction to which it is registered through the following methods of the tNG class:

• getPrimaryKeyValue() - returns the value of the primary key of the record modified by the transaction

• getColumnValue("column_name") - returns the value of the column. You must specify the name as it is used in the transaction.

• getColumnType("column_name") - returns the type of the specified column.

You can use these methods to retrieve column value and use them for any processing - either before or after the transaction. Note that the primary key value can only be used in an after trigger, as it gets initialized only after the transaction has completed.

Example: the following example uses the Discussion Board tutorial database structure, and the post message page. When posting a new message, aside the regular information you have to enter in the form fields, an additional field has to be set after the insert operation: the id_init_msg field must be set to the same value as the new message ID. The custom trigger that is executed after the insert transaction performs an update on the last record and sets the correct values through an update transaction.

• For PHP:
  
  $query = "UPDATE message_msg SET id_init_msg = ".KT_escapeForSql($tNG->getPrimaryKeyValue(), $tNG->getColumnType($tNG->getPrimaryKey()))." WHERE id_msg = ".$tNG->getPrimaryKeyValue();
  $update_result = $tNG->connection->execute($query);
  if(!$update_result) {
  $updateError = new tNG_error("Error setting the initial message ID",array(),array());
  return $updateError;
  } else {
  return NULL;
  }
   

• For ASP
  
  query = "UPDATE message_msg SET id_init_msg = " & KT_escapeForSql(tNG.getPrimaryKeyValue(),tNG.getColumnType("id_msg")) & " WHERE id_msg = " & tNG.getPrimaryKeyValue()
  On Error Resume Next
  Set update_result = tNG.connection.Execute(query)
  if Err.Number <> 0 then
  Set update_error = new tNG_error
  update_error.init "Error setting the initial message ID", Array(), Array()
  Set Trigger_Custom = update_error
  else
  Set Trigger_Custom = nothing
  end if
  On Error GoTo 0
   

• For ColdFusion - because the code sample below needs to execute a SQL query, the functions are used within the function that resolves this. See the Execute sql queries topic for more details.
  
  <cffunction name="AliasCustomTrigger">
  <cfargument name="tNG" required="true">
  <cfset var query = "">
  <cfset var result = "">
  <cfset var myerror = "">
  <cfset query = "UPDATE message_msg SET id_init_msg = " & tNG.getPrimaryKeyValue() & " WHERE id_msg = " & tNG.getPrimaryKeyValue()>
  <cflock name="ins_message_msg" type="exclusive" timeout="10">
  <cftry>
  <cfquery name="result" datasource="#tNG.connection#">
  #PreserveSingleQuotes(query)#
  </cfquery>
  <cfcatch>
  <cfset updateError= Request.tNG_CreateObject("tNG_error")>
  <cfset updateError.init ("Error setting the initial message ID", ArrayNew(1), ArrayNew(1))>
  </cfcatch>
  </cftry>
  </cflock>
  <cfif IsObject(updateError)>
  <cfreturn updateError>
  <cfelse>
  <cfreturn Request.KT_Null>
  </cfif>
  </cffunction>
  <cfset Request.AliasCustomTrigger = AliasCustomTrigger>
  
  <cfscript>
  //start Trigger_Custom trigger
  function Trigger_Custom(tNG) {
  return Request.AliasCustomTrigger(tNG);
  }
  Request.Trigger_Custom = Trigger_Custom;
  //end Trigger_Custom trigger
  </cfscript>
   

In the next section you will learn how to modify the value of fields involved in a transaction.