You can do it with a trigger or with a CHECK constraint WITH NOCHECK.

Be aware that:

1) CHECK constraints can't read data from another table directly, but have to use a scalar function. From a performance standpoint, I see this even worse than triggers

2) If a CHECK constraint is attached to a column, it is not invoked if you update another column. Moreover, I just discovered a weird thing: if the column you update is not explicitly used in the CHECK constraint, you can bypass the check.

Example:

USE tempdb;

GO

-- Create master table

CREATE TABLE masterTable (

id int identity(1,1) PRIMARY KEY,

dateField datetime

)

GO

-- Create referenced table

CREATE TABLE referencedTable (

id int identity(1,1) PRIMARY KEY,

master_id int FOREIGN KEY REFERENCES masterTable(id),

valueField varchar(50)

)

GO

-- Insert sample data

INSERT INTO masterTable(dateField) VALUES(GETDATE())

INSERT INTO masterTable(dateField) VALUES(DATEADD(day,-1,GETDATE()))

INSERT INTO masterTable(dateField) VALUES(DATEADD(day,-2,GETDATE()))

SELECT * FROM masterTable

-- Insert sample data in referenced table

INSERT INTO referencedTable(master_id, valueField) VALUES (1,'Value for id 1')

INSERT INTO referencedTable(master_id, valueField) VALUES (2,'Value for id 2')

INSERT INTO referencedTable(master_id, valueField) VALUES (3,'Value for id 3')

SELECT * FROM referencedTable

GO

-- Create a scalar UDF: you will need this in the CHECK constraint

CREATE FUNCTION getMasterDate(@id int)

RETURNS datetime

AS

BEGIN

RETURN ISNULL((SELECT dateField FROM masterTable WHERE id = @id),'30110101')

END

GO

-- Add the constraint WITH NOCHECK: existing rows are not affected

ALTER TABLE referencedTable WITH NOCHECK ADD CONSTRAINT chk_date

CHECK (DATEADD(day,-1,GETDATE()) > dbo.getMasterDate(master_id))

-- Update referenced table. Some rows conflict with the CHECK constraint, but it works

-- because the constraint is attached to a single column

UPDATE referencedTable

SET valueField = 'New ' + valueField

-- If you try to update master_id it fails

UPDATE referencedTable

SET master_id = master_id + 1 - 1

--Msg 547, Level 16, State 0, Line 2

--The UPDATE statement conflicted with the CHECK constraint "chk_date". The conflict occurred in database "tempdb", table "dbo.referencedTable", column 'master_id'.

--The statement has been terminated.

-- Drop the constraint

ALTER TABLE referencedTable DROP CONSTRAINT chk_date

GO

-- Create the constraint referencing multiple columns

ALTER TABLE referencedTable WITH NOCHECK ADD CONSTRAINT chk_date

CHECK (

DATEADD(day,-1,GETDATE()) > dbo.getMasterDate(master_id)

AND

ISNULL(valueField,'') = ISNULL(valueField,SPACE(0))

)

GO

-- DOESN'T WORK ANY MORE:

UPDATE referencedTable

SET valueField = 'New ' + valueField

--Msg 547, Level 16, State 0, Line 1

--The UPDATE statement conflicted with the CHECK constraint "chk_date". The conflict occurred in database "tempdb", table "dbo.referencedTable".

--The statement has been terminated.

You can check the column/table scope of the constraint querying sys.check_constraints:

SELECT parent_column_id, definition, is_not_trusted

FROM sys.check_constraints

WHERE parent_object_id = OBJECT_ID('referencedTable')

parent_column_id = 0 means table scoped constraint, parent_column_id > 0 means a single column.

Hope this helps

Gianluca