Is it possible to add a check constraint that calls a user defined function in a different database?

Question

I'm trying to add a user defined function that actually calls the SQL# CLR function RegEx_IsMatch to a column, but I get this error:

A user-defined function name cannot be prefixed with a database name in this context.

But if the function is in a different db, I'm not sure how to do this.

score 1 · Accepted Answer · answered Aug 15 '15 at 20:32

You shouldn't need to do this in a CHECK CONSTRAINT. An AFTER INSERT, UPDATE Trigger should be able to provide the same functionality as the CHECK CONSTRAINT. You just need to cancel the INSERT or UPDATE operation if the desired condition is (or is not) met. And this is easily done simply by issuing a ROLLBACK, which works due to Triggers existing within the transaction that is the DML statement itself. Hence, just do something along the lines of:

CREATE TRIGGER dbo.trCheckSomeField
ON dbo.SomeTable
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;

IF (EXISTS(
        SELECT *
        FROM   Inserted ins
        WHERE  Utility.SQL#.RegEx_IsMatch(ins.SomeField, ...) = 0
          )
    )
BEGIN;
  ROLLBACK TRAN;
  RAISERROR('Your data suck!', 16, 1);
  RETURN;
END;

score 0 · Answer 2 · answered Jan 01 '12 at 22:57

Never tried it, but maybe you can create a helper function in the same DB which in turn calls into the other DB?

This may however fail because check constraints are supposed to be determinstic AFAIR, and calls into other databases aren't deterministic. In general it doesn't seem like a good idea to call into another DB, even if it is only for a regex check. Why not add the CLR assembly to this DB as well?

Is it possible to add a check constraint that calls a user defined function in a different database?

2 Answers2