Trap Both Primary Key and CHECK Constraint Violations : Constriant violation « Constraints « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Constraints
3. Constriant violation

8> CREATE TABLE T1(
9>   pk_col    int NOT NULL PRIMARY KEY CHECK (pk_col > 0),
10>   ident_col int NOT NULL IDENTITY (1,1)
11> )
12>
13> DECLARE
14>   @myerror    AS int
15> INSERT INTO T1 VALUES(0) -- violate the check constraint
16> SET @myerror = @@ERROR
17> IF @myerror = 2627
18>   PRINT 'PRIMARY KEY constraint violation'
19> ELSE IF @myerror = 547
20>   PRINT 'CHECK constraint violation'
21> GO
Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 15
The INSERT statement conflicted with the CHECK constraint "CK__T1__pk_col__39788055". The conflict occurred in database "master", table "dbo.T1", column 'pk_col'.
The statement has been terminated.
CHECK constraint violation
1>
2> --Attempt to Capture @@IDENTITY, @@ROWCOUNT, and @@ERROR
3> DECLARE
4>   @myerror    AS int,
5>   @myrowcount AS int,
6>   @myidentity AS int
7> INSERT INTO T1 VALUES(10) -- PK violation
8> SELECT @myidentity = @@IDENTITY,
9>        @myrowcount = @@ROWCOUNT,
10>        @myerror    = @@ERROR
11> PRINT '@myidentity: ' + CAST(@myidentity AS varchar)
12> PRINT '@myrowcount: ' + CAST(@myrowcount AS varchar)
13> PRINT '@myerror   : ' + CAST(@myerror AS varchar)
14> GO

(1 rows affected)

(1 rows affected)
@myidentity: 2

(1 rows affected)
@myrowcount: 1

(1 rows affected)
@myerror   : 0
1> --Output of Successful Attempt to Capture @@IDENTITY, @@ROWCOUNT, and @@ERROR
2>
3> drop table T1;
4> GO