Check unique key for multiple fields

MX Kollection provides a trigger as a server behavior that allows checking if the value in a transaction field already exists in the table, and stop the insert operation if true. This is useful when trying to prevent duplicate records - e.g. when registering user accounts. But in order to check multiple fields you have to build your own custom trigger. In the example below which uses the Discussion Board tutorial structure (both database and files), a custom trigger is built to prevent posting duplicate messages - which have the same subject and content.

The trigger retrieves the values of the subject and content fields from the transaction and uses them to build a select query. If the query yields any results, then the message is a duplicate and the transaction is stopped. The trigger uses only the methods explained for the Execute sql queries and Use transaction fields sections, and the basic structure is as follows:

//first retrieve the transaction fields' values and store them in local variables, for convenience
subject = tNG.getColumnValue("subject_field")
content = tNG.getColumnValue("content_field");

//next use the two values to build the query
query = "SELECT * FROM [message_table] WHERE subject_field = ".KT_escapeForSql(subject ,"STRING_TYPE")." AND content_field = ".KT_escapeForSql(content ,"STRING_TYPE")

//run the query and retrieve the result into a variable
result = tNG.connection.execute(query)

//check if the resulted recordset contains any records; if not, return NULL
if( result.recordCount() = 0)
return NULL
//otherwise, create a new error object and return it
check_failed = new tNG_error("Error message",array(), array())
return check_failed

The structure above, adapted to your particular scripting language and scenario allows building the custom trigger to check for unique entries. From the trigger properties page, you must set it to the BEFORE type, so that it is executed before the actual insert, and will block it if it fails.

Next you will see an example of a multiple field unique check created for the Discussion Board tutorial scenario, for all server models:

• For PHP
  
  $query = "SELECT * FROM message_msg WHERE subject_msg = ".KT_escapeForSql($tNG->getColumnValue("subject_msg"),$tNG->getColumnType("subject_msg"))." AND content_msg = ".KT_escapeForSql($tNG->getColumnValue("content_msg"),$tNG->getColumnType("content_msg"));
  $result = $tNG->connection->Execute($query);
  if(!$result) {
  $error = new tNG_error("Could not access database!",array(),array());
  return $error;
  } else {
  if($numberOfRecords = $result->recordCount()) {
  $uniqueFailed = new tNG_error("There is already a message with the same subject and content!",array(),array());
  return $uniqueFailed;
  } else {
  return NULL;
  }
  }
   

• For ASP_VBScript
  
  'Trigger that checks if two combined fields are unique
  Dim subjectValue
  Dim contentValue
  Dim query
  
  subjectValue = tNG.getColumnValue("subject_msg")
  contentValue = tNG.getColumnValue("content_msg")
  
  query = "SELECT * FROM message_msg where subject_msg = " & KT_escapeForSql(subjectValue,tNG.getColumnType("subject_msg")) & " AND content_msg = " & KT_escapeForSql(conentValue,tNG.getColumnType("content_msg"))
  
  on Error Resume Next
  set check_result =  tNG.connection.Execute(query)
  If Err.Number <>0 Then
  Set check_failure = new tNG_error
  check_failure.init "Could not access database!", Array(), Array()
  Set Trigger_Custom2 = check_failure
  Else
  if(check_result.recordCount() <>0 ) Then
  Set not_unique = new tNG_error
  not_unique.init "A message with the same subject and content already exist. You cannot enter duplicates", Array(), Array()
  Set Trigger_Custom2 = not_unique
  else
  Set Trigger_Custom2 = nothing
  end if
  end if
  On Error GoTo 0
   

• For ColdFusion you must create a function that constructs the query, executes it and checks the number of records. It's result is assigned to the custom trigger return:
  
  <cffunction name="AliasCustomTrigger2">
  <cfargument name="tNG" required="true">
  <cfset var query = "">
  <cfset var result = "">
  <cfset var myerror = "">
  <cfset query = "SELECT * FROM message_msg WHERE subject_msg = " & Request.KT_escapeForSql(tNG.getColumnValue("subject_msg"),tNG.getColumnType("subject_msg")) & " AND content_msg = " & Request.KT_escapeForSql(tNG.getColumnValue("content_msg"), tNG.getColumnType("content_msg"))>
  <cflock name="check_multiple_unique" type="exclusive" timeout="10">
  <cftry>
  <cfquery name="result" datasource="#tNG.connection#">
  #PreserveSingleQuotes(query)#
  </cfquery>
  <cfif result.RecordCount GT 0>
  <cfset myerror = Request.tNG_CreateObject("tNG_error")>
  <cfset myerror.init ("There is already a message with the same subject and content!", ArrayNew(1), ArrayNew(1))>
  </cfif>
  <cfcatch>
  <cfset myerror = Request.tNG_CreateObject("tNG_error")>
  <cfset myerror.init ("Could not access database!", ArrayNew(1), ArrayNew(1))>
  </cfcatch>
  </cftry>
  </cflock>
  <cfif IsObject(myerror)>
  <cfreturn myerror>
  <cfelse>
  <cfreturn Request.KT_Null>
  </cfif>
  </cffunction>
  <cfset Request.AliasCustomTrigger2 = AliasCustomTrigger2>
  <cfscript>
  //start Trigger_Custom2 trigger
  function Trigger_Custom2(tNG) {
  return Request.AliasCustomTrigger2(tNG);
  }
  Request.Trigger_Custom2 = Trigger_Custom2;
  //end Trigger_Custom2 trigger
  </cfscript>