I am new to ColdFusion and have to make a edit page that will both update and track the changes made to a table; while passing the logged in AD user to the database.
Environment:
DB: SQL 2008 R2
OS: Windows 8
Server: ColdFusion 8
Tables Involved:
Branches (main data)
Audit_Branches (change tracking data)
Files involved:
editBranch.cfm (form)
updateBranch.cfc (updates Branches table)
auditBranchLog.cfc (updates Audit_Branches table)
So far I have built the page, and have added the functionality to update the Branches table. Now, I am trying to track the changes and log the AD user who made them.
I started by using a SQL Trigger to watch for Inserts, Updates, Deletes on the Branches table; but the SQL Server only tracks the current SQL user. Since that user is a application user, all the users have the same name entry. Consequently, I re-engineered the Audit_Branches table to have the data inserted directly via CF and thus I can pass the AD username.
Main Problem:I can get the first of three fields to insert into the Audit_Branches table. If more than one field has a change, only the first one in the Arguments array is run and the others are ignored.
I am using cf-if statements to add to a query that is then being passed via a cf-invoke to the auditBranchLog function.
I have tried nested if, if-else; and and/or statements all to no avail. Please help me figure out the best way to make the updateBranch go through all the ifs one by one or suggest a better way to pass the information along to the auditBranchLog
Much Thanks...
------------------ editBranch code:
<cfquery name="getBranches" datasource=[...]>
SELECT
BRANCHID,
BRANCHDESCRIPTION,
BRANCHFILTER,
ADDRESS1,
[...]
FROM Branches
where BRANCHID = '#URL.BRANCHID#'
</cfquery>
<script>
function updateBranch(){
var branchID = $('#branchID').val() ;
var branchDescription = $('#branchDescription').val() ;
var branchDescription_Old = $('#branchDescription_Old').val() ;
var branchFilter = $('#branchFilter').val();
var branchFilter_Old = $('#branchFilter_Old').val() ;
var address1 = $('#address1').val();
var address1_Old = $('#address1_Old').val();
[...]
$.ajax(
{
// the location of the CFC to run
url: "updateBranch.cfc"
// send a GET HTTP operation
, type: "get"
// send the data to the CFC
, data: {
// the method in the CFC to run
method: "UpdateBranches"
// send the BranchID
, branchID: branchID
, branchDescription: branchDescription
, branchDescription_Old: branchDescription_Old
, branchFilter: branchFilter
, branchFilter_Old: branchFilter_Old
, address1: address1
, address1_Old: address1_Old
[...]
}
// this gets the data returned on success
, success: function doLoad(data) { if (typeof console != "undefined") {
console.log(data);
}; location.href = "BranchList.cfm";
}
} )
}
</script>
</head>
<body>
<cfoutput>
<form class="form-horizontal" action="" method="post">
[...]
<fieldset>
<!-- Form Name -->
<legend>Edit Branch</legend>
<!-- BRANCHID input-->
<div class="form-group">
<label class="col-md-4 control-label" for="branchID">Branch ID</label>
<div class="col-md-4">
<input id="branchID" class="form-control input-md" name="branchID" type="text" placeholder=""
value="#getBranches.BRANCHID#" readonly="yes">
</div>
</div>
<!-- BRANCHDESCRIPTION input-->
<div class="form-group">
<label class="col-md-4 control-label" for="branchDescription">Branch Description</label>
<div class="col-md-4">
<input id="branchDescription" class="form-control input-md" name="branchDescription"
value="#getBranches.BRANCHDESCRIPTION#" type="text" placeholder="">
<input id="branchDescription_Old" name="branchDescription_Old" value="#getBranches.BRANCHDESCRIPTION#"
type="hidden">
</div>
</div>
<!-- BRANCHFILTER input-->
<!--- using CFSelect allows to get the db field back for the current selection --->
<div class="form-group">
<label class="col-md-4 control-label" for="branchFilter">Branch Filter</label>
<div class="col-md-4">
<select id="branchFilter" class="form-control" name="branchFilter" size="1">
<cfif LEN(getBranches.branchFilter) GT 0>
<option value="#getBranches.branchFilter#" selected>#getBranches.branchFilter# (current value)</option>
<option value="#getBranches.branchFilter#">---</option>
</cfif>
<option value="Branch">Branch</option>
<option value="Headquarters">Headquarters</option>
<option value="RDC">RDC</option>
<option value="Automation">Automation</option>
</select>
<input id="branchFilter_Old" name="branchFilter_Old" value="#getBranches.BRANCHFILTER#"
type="hidden">
</div>
</div>
<!-- ADDRESS1 input-->
<div class="form-group">
<label class="col-md-4 control-label" for="address1">Address 1</label>
<div class="col-md-4">
<input id="address1" class="form-control input-md" name="address1" type="text"
value="#getBranches.ADDRESS1#" placeholder="">
<input id="address1_Old" name="address1_Old" value="#getBranches.ADDRESS1#"
type="hidden">
</div>
</div>
[...]
<!-- SUBMIT Button -->
<div class="form-group">
<label class="col-md-4 control-label" for="SUBMIT"></label>
<div class="col-md-4">
<button type = "button" id="SUBMIT" class="btn btn-primary" name="SUBMIT" onClick="updateBranch()">Submit</button>
</div>
</div>
------------------ updateBranch code:
<cfcomponent displayname="Branches" access="remote" hint="Update Branches">
<cffunction name="getUserID" displayname="getUserID" hint="I pass back the user information in a clean format" access="package" output="false">
<cfset var UserID = "">
<cfset UserID = [...] >
<cfreturn UserID>
</cffunction>
<cffunction name="UpdateBranches" access="remote" returntype="string"
hint="Changes the Branch Info" >
<cfargument name="branchID" />
<cfargument name="branchDescription" />
<cfargument name="branchDescription_Old" />
<cfargument name="branchFilter" />
<cfargument name="branchFilter_Old" />
<cfargument name="address1" />
<cfargument name="address1_Old" />
<cfset auditLog = QueryNew("BranchID,FieldName,Old,New,UserID", "VarChar, VarChar, VarChar, VarChar, VarChar")>
<!--- compare old a new and call auditBranchLog.cfc --->
<!---BranchDescription--->
<cfif "#ARGUMENTS.branchDescription#" NEQ "#ARGUMENTS.branchDescription_Old#">
<cfset newrow = QueryAddRow(auditLog)>
<cfset temp = QuerySetCell(auditLog,"BranchID","#ARGUMENTS.branchID#")>
<cfset temp = QuerySetCell(auditLog,"FieldName","BranchDescription")>
<cfset temp = QuerySetCell(auditLog,"Old","#ARGUMENTS.branchDescription_Old#")>
<cfset temp = QuerySetCell(auditLog,"New","#ARGUMENTS.branchDescription#")>
<cfset temp = QuerySetCell(auditLog,"UserID","#getUserID()#")>
</cfif>
<!---BranchFilter--->
<cfif "#ARGUMENTS.branchFilter#" NEQ "#ARGUMENTS.branchFilter_Old#">
<cfset newrow = QueryAddRow(auditLog)>
<cfset temp = QuerySetCell(auditLog,"BranchID","#ARGUMENTS.branchID#")>
<cfset temp = QuerySetCell(auditLog,"FieldName","BranchFilter")>
<cfset temp = QuerySetCell(auditLog,"Old","#ARGUMENTS.branchFilter_Old#")>
<cfset temp = QuerySetCell(auditLog,"New","#ARGUMENTS.branchFilter#")>
<cfset temp = QuerySetCell(auditLog,"UserID","#getUserID()#")>
</cfif>
<!---Address1--->
<cfif "#ARGUMENTS.address1#" NEQ "#ARGUMENTS.address1_Old#">
<cfset newrow = QueryAddRow(auditLog)>
<cfset temp = QuerySetCell(auditLog,"BranchID","#ARGUMENTS.branchID#")>
<cfset temp = QuerySetCell(auditLog,"FieldName","Address1")>
<cfset temp = QuerySetCell(auditLog,"Old","#ARGUMENTS.address1_Old#")>
<cfset temp = QuerySetCell(auditLog,"New","#ARGUMENTS.address1#")>
<cfset temp = QuerySetCell(auditLog,"UserID","#getUserID()#")>
</cfif>
<cfif auditLog.RecordCount NEQ 0>
<cfinvoke component="auditBranchLog" method="auditBranch" auditLog="#auditLog#" >
</cfif>
<cfquery name=[...]
</cfquery>
<cfreturn arguments.branchID />
</cffunction>
</cfcomponent>
------------------ auditBranchLog code:
<cffunction name="auditBranch" displayname="auditBranch" >
<cfargument name="auditLog" type="query" required="true">
<cfquery name="auditBranchQry" datasource=[...]>
USE [...]
INSERT INTO [dbo].[Audit_BRANCHES]
([BranchID]
,[FieldName]
,[Old]
,[New]
,[ChangeDate]
,[UserID])
VALUES
(
<CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#auditLog.BranchID#" />
,<CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#auditLog.FieldName#" />
,<CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#auditLog.Old#" />
,<CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#auditLog.New#" />
,getdate()
,<CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#auditLog.UserID#" />
)
</cfquery>
<cfset testVar = "true">
<cfreturn testVar>
</cffunction>
</cfcomponent>
UPDATE:
Here is the information from the cfdump, I changed all three values:
<th class="query" colspan="6" onClick="cfdump_toggleTable(this);" style="cursor:pointer;" title="click to collapse">query</th>
</tr>
<tr bgcolor="eeaaaa" >
<td class="query" style="cursor:pointer;" title="click to collapse" onClick="cfdump_toggleRow_qry(this);"> </td>
<td class="query">BRANCHID</td>
<td class="query">FIELDNAME</td>
<td class="query">NEW</td>
<td class="query">OLD</td>
<td class="query">USERID</td>
</tr>
<tr >
<td style="cursor:pointer;" title="click to collapse" onClick="cfdump_toggleRow_qry(this);" class="query">1</td>
<td valign="top">YYZ </td>
<td valign="top">BranchDescription </td>
<td valign="top">BranchLabel-New </td>
<td valign="top">BranchLabel </td>
<td valign="top">user.name </td>
</tr>
<tr >
<td style="cursor:pointer;" title="click to collapse" onClick="cfdump_toggleRow_qry(this);" class="query">2</td>
<td valign="top">YYZ </td>
<td valign="top">BranchFilter </td>
<td valign="top">Branch </td>
<td valign="top">Headquarters </td>
<td valign="top">user.name </td>
</tr>
<tr >
<td style="cursor:pointer;" title="click to collapse" onClick="cfdump_toggleRow_qry(this);" class="query">3</td>
<td valign="top">YYZ </td>
<td valign="top">Address1 </td>
<td valign="top">Address1-new </td>
<td valign="top">Address1 </td>
<td valign="top">user.name </td>
</tr>
</table>
<wddxPacket version='1.0'><header/><data><string>YYZ</string></data></wddxPacket>
