I need a way to query a database and over and over until a new record is added. The record will be added by another application, so my app just needs to wait until the new record is created, then immediately redirect my user to a page where they can edit that new record.
I think I'm on the right track with the code I have and the help I've received from the Macromedia forums. But I don't know how to run the query over and over...or if there is another way to do this. Setting up a scheduled task is not the answer as this could happen at any time.
Here's the code I have so far:
<!--- User clicks a button triggering an update to the user table, then an outside app see this new update to the user
table, and ads a new record to the activity table --->
UPDATE sysdba.USERINFO SET TICKETNOTIFY = 'T', ACCOUNTINGUSERID = '#FORM.CONTACTID#' WHERE USERID = '#FORM.userid#'
<!--- There is a field in the ACTIVITY table called CREATEDATE that is the date on which the record gets created. I
figured, if I set a variable for current time, I can then some how check the CREATEDATE against the currentTime variable, thus knowing a new record exists. --->
<cfset currentTime = Now()>
<!--- Not sure what to do with this. I need to query the ACTIVITY table and see if a new record has been created since "currentTime" --->
<cfquery name="activCnt" datasource="slx">
SELECT ACTIVITYID, CREATEDATE
<!--- Once I do determine that a new record has been completed, I redirect my user to the edit form where they can edit this newly created record. Don't know if this is correct either.--->
<cfif #activCnt.CREATEDATE# GT #currentTime#>