Karen, I have only used the NOCALC switch to unload and reload data without any of the computed columns recalculating. By design, the computed columns kicks in any time one of the components of the computations changes, for example, if I have a columns “FullName” which is defined as “FirstName& LastName” then, whenever one of the two components changes, the computed column also changes, as expected. When you unload and load data using the NOCALC switch, the update of the computed column is temporarily disabled ONLY during the unload and load operation. However, If you edit one of the components before or after, the computed column would update as expected. For this reason, I would never use the variable #NOW as part of a computed columns because I am not sure how often the computed column with the #NOW component would update. Using UPDATE in the INSERT trigger would update the columns only once, when the trigger kicks in, and the value would be retained afterwards unless specifically and manually or programmatically changed. It was suggested to use #NOW as the default value for the column at insert time when no value is entered for the column in question. This look like a nice solution; however, I have not used this approach myself before so I cannot attest how and if it works. In theory, sounds like it should work and it is something I will definitely try in the future. Hopefully this explains my comment based on my understanding on how the NOCALC switch work and my own experience. Javier, Javier Valencia, PE 913-915-3137 From: 'Karen Tellef' via RBASE-L <[email protected]> Sent: Wednesday, September 29, 2021 8:12 AM To: [email protected] Subject: Re: [RBASE-L] - Automatic DateTime for each row inserted Javier: Just confirm for me cuz I haven't played around with NOCALC yet. That prevents a recalculation of computed columns if you do an unload, but the computed column will still change if you make any change to the row, correct? If so, then a computed date or datetime column is good only as a "date last edited" but not to indicate when a record was inserted or another type of change. In that case, like you demonstrate, I either update a regular column in code or by using triggers. My triggers look at the before and after value, and then update a date column if a change was made, or populate a change logging table. Also to be considered is work at the R> prompt and whether you would want a computed column to be changed if, for example, a consultant is fiddling around with data on the backend and wouldn't want to have a user-generated computed column changing. Karen -----Original Message----- From: [email protected] <mailto:[email protected]> To: [email protected] <mailto:[email protected]> Sent: Tue, Sep 28, 2021 5:56 pm Subject: RE: [RBASE-L] - Automatic DateTime for each row inserted Doug,
I have used the NOCALC switch before and works as advertised. Having said that, I would be hesitant to use a computed field that uses the #NOW variable since it is dynamic and I would rather have that columns locked on one date and not take a chance of it updating when not wanted. Have you consider using the After Insert trigger with something like this: UPDATE ShipmentTracking + SET TrackingDateTimeAdded = .#NOW + WHERE TrackingDateTimeAdded IS NULL It would automatically update anything that does not have data which most of the time would be only the record just inserted. Jests a though and I have not looked into any undesirable side effects. Javier, Javier Valencia, PE 913-915-3137 -----Original Message----- From: [email protected] <mailto:[email protected]> <[email protected] <mailto:[email protected]> > On Behalf Of Doug Hamilton Sent: Tuesday, September 28, 2021 10:43 AM To: R:Base List <[email protected] <mailto:[email protected]> > Subject: [RBASE-L] - Automatic DateTime for each row inserted I have a 7 column table, one column is named TrackingDateTimeAdded, type DateTime. What's the best way to update TrackingDateTimeAdded when a row is inserted into the table? The DateTime needs to persist through an UNLOAD and INPUT. Some of these rows will be inserted by a third party via SATTACH, so it needs to be automated via a trigger or computed column. I tried an After Insert trigger with the Stored Procedure: UPDATE ShipmentTracking + SET TrackingDateTimeAdded = .#NOW + WHERE COUNT=INSERT This updated the DateTime of the previously inserted row. In a multi-user environment, COUNT = LAST may not update the row inserted by the user. The results using a trigger were too unpredictable to be used in production. A computed column using (IFNULL(TrackingDateTimeAdded,.#NOW,TrackingDateTimeAdded)) works until the database is UNOADED and INPUTted - the column then gets set to the DateTime of the input. TIA Doug RB X.5E 20812 -- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. <mailto:[email protected].> To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/0add1034-f377-c9e3-2183-d0027a6e6859%40wi.rr.com. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. <mailto:[email protected].> To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/!%26!AAAAAAAAAAAYAAAAAAAAAG1gU%2BA71stIig71BAFnx9iihQAAEAAAAEH4%2BR0fIvFMtLO8fsISh/8BAAAAAA%3D%3D%40vtgonline.com. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/1612567935.142843.1632921106073%40mail.yahoo.com <https://groups.google.com/d/msgid/rbase-l/1612567935.142843.1632921106073%40mail.yahoo.com?utm_medium=email&utm_source=footer> . -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/!%26!AAAAAAAAAAAYAAAAAAAAAG1gU%2BA71stIig71BAFnx9iihQAAEAAAAIiY8xumEHFHtyWHdwg1kCYBAAAAAA%3D%3D%40vtgonline.com.
