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.

Answer 1

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;