CODEWITHSUNDEEP
sql-server
William Meitzen
William Meitzen

Reputation: 63

Preventing specifying values in a column

Is there a way to prevent specifying explicit values in a column? The identity attribute prevents specifying explicit values (as long as IDENTITY_INSERT is off), which is the type of behavior I want.

create table testguid (
    ID uniqueidentifier default newsequentialid() not null primary key
    ,somedate datetime
)

[constraint or trigger here?]

insert into testguid (somedate) values (getdate()) -- this is ok
insert into testguid (ID, somedate) values (newid(), getdate()) -- this is not ok

I want the database to insert values, but I want to prevent any way of specifying them.

Upvotes: 1

Views: 77

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

You could use an INSTEAD OF TRIGGER to basically re-write the INSERT statement, leaving out the ID column.

CREATE TRIGGER dbo.testguid_beforeinsert
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.testguid(somedate --, other columns
    ) SELECT GETDATE() --, other columns
    FROM inserted;
END
GO

Probably want something similar for UPDATE, too.

Upvotes: 1

Related Questions