CODEWITHSUNDEEP
sqlsql-serverconstraints
Shekhar Pankaj
Shekhar Pankaj

Reputation: 9145

How to add Constraint to Table's Column,so value cannot be other than default

Recently one issue came into my Db,the same db is been shared by multiple Application.

my First application uses one Table to Insert row which was Having A column

ProductionDate Of DataType DateTime
DateCreated Of DataType DateTime Default GetDate()

As we all know DateCreated, will have the time,when Row was inserted to the table when no values are passed in Insert Statement

But one of my collegues used the same column DateCreated from his application to insert some other value (date ) for the product inspite of using ProductionDate (he was misleaded with the name),

i found this issue when my reports was misleading (Select was based on DateCreated).

how can i force my Column to avoid accepting anything ,except it will contain only Getdate(), ie

 INSERT INTO MyTableName(.....,DateCreated,......)
 VALUES (.....,'2015-07-15 14:06:42.250',......)

Should throw Exception/Error!! I am using SQL SERVER 2012

Exception:I Already have an update/Insert Trigger to Fill My DateModified Column :(

Upvotes: 0

Views: 97

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

I would rename the existing table (say with an underscore prefix) and replace it with a view using the original name and performing a trivial computation on the DateCreated column so that it becomes computed and therefore readonly:

create table dbo._T (
    ID int not null,
    DateCreated datetime not null constraint DF_Created DEFAULT (CURRENT_TIMESTAMP))
go
create view dbo.T
with schemabinding
as
    select ID,COALESCE(DateCreated,DateCreated) as DateCreated
    from dbo._T
go
insert into dbo.T (ID) values (1)
go
insert into dbo.T(ID,DateCreated) values (1,'20150101')

Produces:

(1 row(s) affected)

Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'dbo.T' failed because it contains a derived or constant field.

And we can see the second insert failed:

select * from dbo.T
ID          DateCreated
----------- -----------------------
1           2015-07-07 14:22:48.840

And now only give the other user/application permission to talk to this view, not the base table.

Upvotes: 2

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

Two ways: 1) You could use an UPDATE TRIGGER to re-set the value of DateCreated to the previous value 2) You could use a rope to tie your collegue to the next tree and teach him the meaning of "DateCreated" :-)

Edit: found this: https://stackoverflow.com/a/12314601/5089204

Upvotes: 0

6ton
6ton

Reputation: 4214

You can add a trigger to set the date created. Code below taken from here

CREATE TRIGGER tr[TableName]CreateDate ON [TableName]
FOR INSERT 
AS
UPDATE [TableName] SET [TableName].Created=getdate()
FROM [TableName] INNER JOIN Inserted ON [TableName].[UniqueID]= Inserted.[UniqueID]

Upvotes: 0

Related Questions