dodaniel
Posts: 43
|
| Posted: 07/18/2016, 9:57 AM |
|
I am testing the trial version of CCS 5.1 and would like to know how to make it update the "Last Modified" date/time field in my SQL table if anything on the record changes.
|
 |
 |
eratech
Posts: 513
|
| Posted: 07/19/2016, 9:58 PM |
|
@dodaniel
I use a couple of methods but the simplest is to have a Hidden field for the 'last modified' date and in the Before Update for the field or record to 'Retrieve Value for Control' and put in the ASP function 'Now' in as the value.
You can show a Label also connected to the last modified field - CCS will automatically only update the text box type, and display only for Labels.
That will update the date if the Record is updated, even if nothing changes. I also use a simple MD5 hash of a few important fields calculated when loading the record, and before doing the Update - if the MD5 changes then set the Last modified field, otherwise don't. You aren't interested in what the MD5 is, just that it has changed.
I had another method originally to do an update using a SQL Query, but it's much simpler to use the built-in functions - and if you change the project from ASP to PHP or similar, then it's easier to change a few functions than a bunch of code.
Cheers
Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
dodaniel
Posts: 43
|
| Posted: 07/21/2016, 12:33 PM |
|
Thanks eratech!
I contacted support and have been trying to follow the example in the CCS manual for Working With Databases, After Execute Update but I am lost on what I need to change in the code to go with my database. I have no experience using the code they show. This is the code from the manual that I am inserting into the "After Excecute Update" event:
-------------------------------------------------------------------------------------------------
Function Tasks_DataSource_AfterExecuteUpdate(Sender)
Dim SQL
Dim Connection
Dim ErrorMessage
Set Connection = New clsDBConnection1
Connection.Open
SQL = "INSERT INTO report (report_task_id,report_creator) "&_
"VALUES ("& Connection.ToSQL(CCGetFromGet("task_id",0),ccsInteger) &","& Connection.ToSQL(CCGetUserID(),ccsInteger) &")"
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
Connection.Close
Set Connection = Nothing
On Error Goto 0
End Function
--------------------------------------------------------------------------------------------------------------
I assume I need to change things in the SQL= area of the code but I don't know what I need the variables to match. Any help is appreciated!
|
|
|
|
dodaniel
Posts: 43
|
| Posted: 07/26/2016, 8:34 AM |
|
I'm still trying to find a solution to this if anyone can help. I just need a timestamp field to automatically update any time a record is changed or modified.
|
|
|
|
dodaniel
Posts: 43
|
| Posted: 07/26/2016, 8:34 AM |
|
I'm still trying to find a solution to this if anyone can help. I just need a timestamp field to automatically update any time a record is changed or modified.
|
|
|
|
MaFi
Posts: 49
|
| Posted: 07/28/2016, 3:37 AM |
|
Use MySQL to update the timestampfield.
Insert a timestampfield in your MySQL table set the default to CURRENT_TIMESTAMP and mark it ON UPDATE CURRENT_TIMESTAMP.
--snipp--
ADD COLUMN `last_change` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
--snipp--
Now MySQL updates the timestamp field everytime you do a change on the record.
|
|
|
|
dodaniel
Posts: 43
|
| Posted: 08/02/2016, 1:54 PM |
|
I'm using SQL Server 2016 and ON UPDATE CURRENT_TIMESTAMP does not work.
I already have a field in my table named UpdatedTS (datetime, null) and that is the one that I'm trying to get to update anytime something changes with the record.
|
|
|
|
dodaniel
Posts: 43
|
| Posted: 08/02/2016, 3:07 PM |
|
*** RESOLVED ***
I was able to get it to work by running the following in Management Studio:
CREATE TRIGGER tgr_namefortrigger
ON TableName
AFTER UPDATE AS
UPDATE TableName
SET DateFieldName = GETDATE()
WHERE UniqueKeyFieldName IN (SELECT DISTINCT UniqueKeyFieldName FROM Inserted)
|
|
|
|
