If the ORM framework is not used, you need to know the usage of @ identity when adding a new record to return the primary key (the method of returning the primary key when adding Nhibernate ).
@ Identity is an identity value returned by MSSQLServer to insert the last record. Only if the primary key value type is "Identity", the return value is null.
The following script creates three tables. One is the primary table testident, which has an auto-incrementing primary key, and the other two are the subtables testchild1 and testchild2. The primary key of the testident table is used as the foreign key.
Code
1 /**/ /*
2@ Identity example
3*/
4
5 Create Table Testident -- Create a table named testident. The primary key is idcol.
6 (
7 Idcol Int Identity Primary Key
8 )
9 Create Table Testchild1 -- Create the table testchild1. The primary key is idcol, and the external key is the idcol of the table testident.
10 (
11 Idcol Int Primary Key Foreign Key References Testident (idcol)
12 )
13 Create Table Testchild2 -- Create the table testchild2. The primary key is idcol, and the external key is the idcol of the table testident.
14 (
15 Idcol Int Primary Key References Testident (idcol)
16 )
17
18 Declare @ Ident Int
19 Insert Into Testident Default Values
20
21 Set @ Ident = @ Identity -- @ Identity is the primary key value of the last record inserted into the testident table.
22 -- The primary key of the testident table is identity, so @ identity has a value.
23
24 Print ' The value we got originally from identyty is ' + Convert ( Varchar ( 2 ), @ Ident )
25 Print ' The value currently in @ identity is ' + Convert ( Varchar ( 2 ), @ Identity )
26
27 Insert Into Testchild1 Values ( @ Identity )
28
29 Print ' The value we got originally from @ identity was ' + Convert ( Varchar ( 2 ), @ Ident )
30
31 If ( Select @ Identity ) Is Null
32 Print ' The value currently in @ identity is null '
33 Else
34 Print ' The value currently in @ identity is ' + Convert ( Varchar ( 2 ), @ Identity )
35
36 Print ''
37
38 Insert Into Testchild2 Values ( @ Identity ) -- @ Identity is the primary key value of the last record in the inserted Table testchild1.
39 -- Because the primary key of testchild1 is not of the Identity type, @ identity is empty.
40
The running result is:
Code
1
2 ( 1 Rows affected)
3 The value we got originally From @ Identyty Is 5
4 The value currently In @ Identity Is 5
5
6 ( 1 Rows affected)
7 The value we got originally From @ Identity Was 5
8 The value currently In @ Identity Is Null
9
10 Message 515 , Level 16 , Status 2 , No 21 Line
11 The value cannot be Null Insert column ' Idcol ' , Table ' Test. DBO. testchild2 ' ; Columns do not allow null values. Insert Failed.
12 The statement has been terminated.
Row 10th of the running result is incorrect because the primary key of the table testchild1 is not of the Identity type.
In practical applications, after the insert into statement is added with select @ identity, the command object uses the executescalar () method (to execute the query, return the first column of the first row of the query result set, ignore other rows or columns) to return the primary key of the latest inserted record. the same applies to stored procedures. the best practice is to save the object of a new user without having to log on.
@ Identity is to obtain such a primary key, which has many functions.
