InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 17:21:37 GMT20http://www.sqlservercentral.com/Forums/Topic766706-146-1.aspxJust for documentation purposes and to clarify the missleading title that reads "[i]Is CHECK constraint same as TRIGGER?[/i]" ... no, check constraint and triggers are not the same.Sat, 08 Aug 2009 16:16:06 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic766706-146-1.aspxI completly agree to Jack.Sat, 08 Aug 2009 13:20:53 GMTSarab_SQLGeekhttp://www.sqlservercentral.com/Forums/Topic766706-146-1.aspxDid you read the BOL entry that contained the example you posted? It explicitly states (emphasis is mine):[quote]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. 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);GOCREATE FUNCTION CheckFnctn()RETURNS intAS BEGIN DECLARE @retval int SELECT @retval = COUNT(*) FROM CheckTbl RETURN @retvalEND;GOALTER TABLE CheckTblADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );GOThe CHECK constraint being added specifies that there must be at least one row in table CheckTbl. [b][i][u]However, because there are no rows in the table against which to check the condition of this constraint, the ALTER TABLE statement succeeds[/u][/i].[/b][/quote]So this is not a valid check. If you change the value being checked to anything greater than 1 then a single row insert fails.Fri, 07 Aug 2009 12:47:16 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic766706-146-1.aspxHi Chandu,I think CHECK constraint checks AFTER insertion of data. If we do row count in the ckeck constraint it will have the new row. See the following example:[code]CREATE TABLE CheckTbl (col1 int, col2 int);GOCREATE FUNCTION CheckFnctn()RETURNS intAS BEGIN DECLARE @retval int SELECT @retval = COUNT(*) FROM CheckTbl RETURN @retvalEND;GOALTER TABLE CheckTblADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );GOinsert into CheckTbl values (1, 1)(1 row(s) affected)[/code]Fri, 07 Aug 2009 02:47:20 GMTSuresh B.http://www.sqlservercentral.com/Forums/Topic766706-146-1.aspx[quote] I just noticed that CHECK constraint behaves like after TRIGGER. That is, the row is inserted then checked whehter it satisfies the condition. If not, rolled back. [/quote]Suresh, No. CHECK constraint checks before insertion of data. When you are trying to insert value 'X' into the table CHECK constraint fails the insert statement. As you have a identity column here even though your insert statement failed the identity value gets increased. Its the defaul behaviour of an identity column.Try this example:create table identTest(id int identity(1, 1),data char(1) )goinsert into identTest(data) values ('A')insert into identTest(data) values ('XX)insert into identTest(data) values ('BV')insert into identTest(data) values ('Y')insert into identTest(data) values ('C')insert into identTest(data) values ('ZX')insert into identTest(data) values ('D')insert into identTest(data) values ('DARK')goselect * from identTest order by idgoFri, 07 Aug 2009 01:18:51 GMTChand00http://www.sqlservercentral.com/Forums/Topic766706-146-1.aspxI just noticed that CHECK constraint behaves like after TRIGGER. That is, the row is inserted then checked whehter it satisfies the condition. If not, rolled back. See the example below. Notice the missing id (identity) values 2, 4, and 6.[code]create table CheckTest(id int identity(1, 1),data char(1) check (Data not in ('X', 'Y', 'Z')))goinsert into CheckTest(data) values ('A')insert into CheckTest(data) values ('X')insert into CheckTest(data) values ('B')insert into CheckTest(data) values ('Y')insert into CheckTest(data) values ('C')insert into CheckTest(data) values ('Z')insert into CheckTest(data) values ('D')select id, Data from CheckTest1 A3 B5 C7 D[/code]My question is, why checking is not done before inserting?Fri, 07 Aug 2009 00:17:01 GMTSuresh B.