check constraint calls a stored procedure or trigger

Question

hi all

can a check constraint call a stored procedure or trigger ?

thanks

Answer 1

No.

---

Gert-Jan

Answer 2

CHECK constraints reject values that evaluate to FALSE. Because null values evaluate to UNKNOWN, their presence in expressions may override a constraint. For example, suppose you place a constraint on an int column MyColumn specifying that MyColumn can contain only the value 10 (MyColumn=10). If you insert the value NULL into MyColumn, the Database Engine inserts NULL and does not return an error.

A CHECK constraint returns TRUE when the condition it is checking is not FALSE for any row in the table. A CHECK constraint works at the row level. If a table that has just been created does not have any rows, any CHECK constraint on this table is considered valid. This situation can produce unexpected results, as in the following example.

CREATE TABLE CheckTbl (col1 int, col2 int);
GO
CREATE FUNCTION CheckFnctn()
RETURNS int
AS
BEGIN
   DECLARE @retval int
   SELECT @retval = COUNT(*) FROM CheckTbl
   RETURN @retval
END;
GO
ALTER TABLE CheckTbl
ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );
GO

The CHECK constraint being added specifies that there must be at least one row in table CheckTbl. However, because there are no rows in the table against which to check the condition of this constraint, the ALTER TABLE statement succeeds.

CHECK constraints are not validated during DELETE statements. Therefore, executing DELETE statements on tables with certain types of check constraints may produce unexpected results. For example, consider the following statements executed on table CheckTbl.

INSERT INTO CheckTbl VALUES (10, 10)
GO
DELETE CheckTbl WHERE col1 = 10;

The DELETE statement succeeds, even though the CHECK constraint specifies that table CheckTbl must have at least 1 row.

But CHECK constraints would never call or involve any SP and triggers directly.

---

Many Thanks & Best Regards, Hua Min

Answer 3

No.

But you can write a trigger which will validate the value and execute code as needed.