Tracking Data Audits In ColdFusion 

<!--- Param form data. --->
<cfparam name="FORM.id" type="numeric" default="0" />
<cfparam name="FORM.first_name" type="string" default="" />
<cfparam name="FORM.last_name" type="string" default="" />
<cfparam name="FORM.title" type="string" default="" />
<cfparam name="FORM.company" type="string" default="" />
<cfparam name="FORM.submitted" type="boolean" default="false" />
 
 
<!--- Check to see if the form as been submitted. --->
<cfif FORM.submitted>
 
	<!---
		Normally, this is where we would validate data, but for
		this demo, we are not going to worry about valid data.
	--->
 
 
	<!--- Check to see if are inserting or updating data. --->
	<cfif FORM.id>
 
		<!--- Update the record. --->
		<cfquery name="qUpdate" datasource="#REQUEST.DSN#">
			UPDATE
				contact
			SET
				first_name =
					<cfqueryparam
						value="#FORM.first_name#"
						cfsqltype="cf_sql_varchar"
						/>,
 
				last_name =
					<cfqueryparam
						value="#FORM.last_name#"
						cfsqltype="cf_sql_varchar"
						/>,
 
				title =
					<cfqueryparam
						value="#FORM.title#"
						cfsqltype="cf_sql_varchar"
						/>,
 
				company =
					<cfqueryparam
						value="#FORM.company#"
						cfsqltype="cf_sql_varchar"
						/>
			WHERE
				id =
					<cfqueryparam
						value="#FORM.id#"
						cfsqltype="cf_sql_integer"
						/>
		</cfquery>
 
	<cfelse>
 
		<!--- Insert the new record. --->
		<cfquery name="qInsert" datasource="#REQUEST.DSN#">
			INSERT INTO contact
			(
				first_name,
				last_name,
				title,
				company
			) VALUES (
				<cfqueryparam
					value="#FORM.first_name#"
					cfsqltype="cf_sql_varchar"
					/>,
 
				<cfqueryparam
					value="#FORM.last_name#"
					cfsqltype="cf_sql_varchar"
					/>,
 
				<cfqueryparam
					value="#FORM.title#"
					cfsqltype="cf_sql_varchar"
					/>,
 
				<cfqueryparam
					value="#FORM.company#"
					cfsqltype="cf_sql_varchar"
					/>
			);
 
			<!--- Return the new ID. --->
			SELECT
				( LAST_INSERT_ID() ) AS id
			;
		</cfquery>
 
		<!--- Store the new id into our form variable. --->
		<cfset FORM.id = qInsert.id />
 
	</cfif>
 
 
	<!---
		ASSERT: At this point, we have either created or
		updated the given record and the ID of that record
		is stored in FORM.id.
	--->
 
 
	<!---
		Insert the new audit record. Rather than inserting the
		row data manually, we are going to select the insert
		data directly out of our contact table.
	--->
	<cfquery name="qInsertAudit" datasource="#REQUEST.DSN#">
		INSERT INTO contact_audit
		(
			date_audited,
			id,
			first_name,
			last_name,
			title,
			company
		)(
			SELECT
				<cfqueryparam
					value="#Now()#"
					cfsqltype="cf_sql_timestamp"
					/>,
				c.id,
				c.first_name,
				c.last_name,
				c.title,
				c.company
			FROM
				contact c
			WHERE
				c.id =
					<cfqueryparam
						value="#FORM.id#"
						cfsqltype="cf_sql_integer"
						/>
		);
	</cfquery>
 
</cfif>
 
 
<!--- Query for all record audits for this contact. --->
<cfquery name="qAudit" datasource="#REQUEST.DSN#">
	SELECT
		a.pkey,
		a.date_audited,
		a.id,
		a.first_name,
		a.last_name,
		a.title,
		a.company
	FROM
		contact_audit a
	WHERE
		a.id =
			<cfqueryparam
				value="#FORM.id#"
				cfsqltype="cf_sql_integer"
				/>
	ORDER BY
		a.pkey DESC
</cfquery>
 
 
<cfoutput>
 
	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
	<html>
	<head>
		<title>ColdFusion Audit Table Demo</title>
	</head>
	<body>
 
		<h1>
			ColdFusion Audit Table Demo
		</h1>
 
 
		<form action="#CGI.script_name#" method="post">
 
			<!--- Send submission flag. --->
			<input type="hidden" name="submitted" value="true" />
 
			<!---
				Send back the ID of the record we are working
				with. This will be zero the first time.
			--->
			<input type="hidden" name="id" value="#FORM.id#" />
 
 
			<p>
				Name:<br />
				<input
					type="text"
					name="first_name"
					value="#FORM.first_name#"
					size="16"
					maxlength="20"
					/>
 
				<input
					type="text"
					name="last_name"
					value="#FORM.last_name#"
					size="19"
					maxlength="30"
					/>
			</p>
 
			<p>
				Title:<br />
				<input
					type="text"
					name="title"
					value="#FORM.title#"
					size="40"
					maxlength="50"
					/>
			</p>
 
			<p>
				Company:<br />
				<input
					type="text"
					name="company"
					value="#FORM.company#"
					size="40"
					maxlength="50"
					/>
			</p>
 
			<p>
				<input type="submit" value="Submit" />
			</p>
 
		</form>
 
 
		<br />
 
		<h2>
			Contacts Audits
		</h2>
 
		<table cellspacing="2" cellpadding="4" border="1">
		<thead>
			<tr>
				<th>
					First Name
				</th>
				<th>
					Last Name
				</th>
				<th>
					Title
				</th>
				<th>
					Company
				</th>
				<th>
					Date
				</th>
			</tr>
		</thead>
		<tbody>
 
			<!--- Loop over audit records. --->
			<cfloop query="qAudit">
 
				<tr>
					<td>
						#qAudit.first_name#<br />
					</td>
					<td>
						#qAudit.last_name#<br />
					</td>
					<td>
						#qAudit.title#<br />
					</td>
					<td>
						#qAudit.company#<br />
					</td>
					<td>
						#DateFormat( qAudit.date_audited, "mm/dd/yy" )#
						#TimeFormat( qAudit.date_audited, "hh:mm TT" )#
					</td>
				</tr>
 
			</cfloop>
 
		</tbody>
		</table>
 
	</body>
	</html>
 
</cfoutput>

For Cut-and-Paste