John said:
A Default on the table field, as I suggested, will in fact work
perfectly well.
When a new record is created, it will store the system clock date into
that table field, at the moment the record is first "dirtied".
That value will not change thereafter.
You're mistaken, I fear!
An I think *you* are mistaken!
The DEFAULT is only applied when the row is first created (INSERT INTO)
and then only when no value was supplied. It don't stop users from
changing it (UPDATE). Try this
CREATE TABLE Test1
(key_col INTEGER NOT NULL PRIMARY KEY,
data_col INTEGER NOT NULL,
created_date DATETIME DEFAULT DATE() NOT NULL);
INSERT INTO Test1 (key_col, data_col, created_date)
VALUES (1, 1, #2020-12-31#);
-- created row with noncurrent date
UPDATE Test1
SET created_date = #1987-01-01#
WHERE key_col = 1;
-- changes also to noncurrent date
You say "That value will not change thereafter" but I just changed it!
Post back if you saw the current date, you may be running it
incorrectly.
The proper solution.
Logon to database as Admin, then
CREATE USER JohnV;
REVOKE ALL PRIVILEGES
ON TABLE Test1
FROM JohnV;
GRANT SELECT
ON TABLE Test1
TO JohnV;
CREATE PROCEDURE AddTest1
key_col INTEGER, :data_col INTEGER)
AS
INSERT INTO Test1 (key_col, data_col)
VALUES
key_col, :data_col)
WITH OWNERACCESS OPTION;
GRANT UPDATE
ON OBJECT AddTest1
TO JohnV;
CREATE PROCEDURE UpdateTest1
key_col INTEGER, :data_col INTEGER)
AS
UPDATE Test1
SET data_col = :data_col
WHERE key_col = :key_col
WITH OWNERACCESS OPTION;
GRANT UPDATE
ON OBJECT UpdateTest1
TO JohnV;
Logon to database as JohnV, then
UPDATE Test1
SET created_date = #2002-02-02#
WHERE key_col = 1;
-- fails, cannot change created_date
INSERT INTO Test1 (key_col, data_col, created_date)
VALUES (2, 2, #2011-11-11#);
-- fails, cannot create row using created_date
EXECUTE AddTest1 2, 2;
-- success, created date is current
EXECUTE UpdateTest1 2, 999;
-- success, created date still is current
ate()