Search code examples
sql-serverdefaultcheck-constraintsgetdate

Only allow current date/time on SQL Server insertion


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?


Solution

  • 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