Constraint in MS SQL

We have table Person(Id, Name, Type) and Role(Id, Name) and a binding M:N table Person_Role(Person_Id, Role_Id) which means开发者_如何学编程 "a person can have set of roles".

All we want to do is to have a UNIQUE constraint in MS SQL 2008 R2 which holds uniqueness of

(Person.Name, Person.Type, Person.SetOfRoles). That is (Jack, 1, {A,B}) and (Jack, 1, {B}) are NOT duplicities.

If we could use Oracle's pre-insert triggers, it would solve everything.

IMPORTANT UPDATE: We use MS SQL with Entity Framework and we DO NOT have the exact insert SQL command.

You can use INSTEAD OF TRIGGER in Sql Server.

CREATE TRIGGER InsteadTrigger on Role
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO Role
       -- Your logic goes here
       FROM inserted
END
GO

Use a "INSTEAD OF INSERT" trigger Example here: http://weblogs.asp.net/ryanw/archive/2006/02/02/437242.aspx