0

I need a way to enforce a single value only within an inserted field, more precisely a DateTime field, that should always be set to the current date/time at insertion. I am working on a university exercise and they want all the constraints to be done within the DB, ordinarily i would just put on a DEFAULT GetDate() and always use DEFAULT on inserts, but the exercise requirements prevent this. Now for an integer i can do this(i've omitted the other fields, since they are irrelevant to the issue at hand) :

CREATE TABLE tester(
d INTEGER not null DEFAULT 3,
CONSTRAINT chkd CHECK(d = 3)
);

However what i want is the following :

CREATE TABLE tester(
d DATETIME not null DEFAULT GETDATE(),
CONSTRAINT chkd CHECK(d = ????????)
);

Re-iterating GetDate() in the check() will trigger an error on inserts, because the microseconds will cause a mismatch. So i guess the first question is, is this possible? and if so(i hope so) how?

Louis M.
  • 143
  • 13

2 Answers2

1

Don't track the date/time in the tester table. Instead, have a separate table with a column that references the ID of the tester table as a foreign key constraint. The new table will have one other column, a DateTime column. On insertion into the tester table, a trigger can be fired that will insert a row into the new table containing the ID of the newly-created tester row as well as the current date/time.

Dan Forbes
  • 2,734
  • 3
  • 30
  • 60
  • 1
    Nice extension of Ryan's comment suggestion, although the separate table isn't necessary, can have the trigger act directly upon the tester table – Louis M. Apr 07 '16 at 15:39
  • Yeah, I wasn't sure how much you needed to isolate that value and make sure users didn't have mechanisms by which they could change it and such. – Dan Forbes Apr 07 '16 at 15:56
1

Based upon Ryan's comment got to this answer which is working

CREATE TRIGGER trigger_date ON [dbo].[tester] 
FOR INSERT
AS
BEGIN
    UPDATE tester SET d = GETDATE() WHERE id IN (SELECT id FROM INSERTED);
END
GO
Louis M.
  • 143
  • 13