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 <tel:(913)%20915-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]
To: [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 <tel:(913)%20915-3137>
-----Original Message-----
From: [email protected] <[email protected]> On Behalf
Of Doug Hamilton
Sent: Tuesday, September 28, 2021 10:43 AM
To: R:Base List <[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 <https://www.avast.com/antivirus>
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
<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/0add1034-f377-c9e3-2183-d0027a6e6859%40wi.rr.com
<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
<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%2BA71stIig71BAFnx9iihQAAEAAAAEH4%2BR0fIvFMtLO8fsISh/8BAAAAAA%3D%3D%40vtgonline.com
<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
<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/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
<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
<https://groups.google.com/d/msgid/rbase-l/!%26!AAAAAAAAAAAYAAAAAAAAAG1gU%2BA71stIig71BAFnx9iihQAAEAAAAIiY8xumEHFHtyWHdwg1kCYBAAAAAA%3D%3D%40vtgonline.com?utm_medium=email&utm_source=footer>.
