这是我刚刚开始学习sql事务的一段代码,希望能给大家拿来看一看。
1
1
--
SQL事务处理:
2
2
3
3
SET
QUOTED_IDENTIFIER
ON
4
4
GO
5
5
SET
ANSI_NULLS
ON
6
6
GO
7
7
8
8
ALTER
procedure
myInsertTest
9
9
10
10
@job_desc
varchar
(
50
),
11
11
@min_lvl
tinyint
,
12
12
@max_lvl
tinyint
,
13
13
@id
int
,
14
14
@name
varchar
(
40
)
15
15
16
16
as
17
17
18
18
begin
tran
19
19
20
20
--
第一张表的插入语句
21
21
INSERT
INTO
dbo.jobs (job_desc, min_lvl,max_lvl)
VALUES
(
@job_desc
,
@min_lvl
,
@max_lvl
)
22
22
23
23
--
第二张表的插入语句
24
24
INSERT
INTO
test (id,name)
values
(
@id
,
@name
)
25
25
26
26
--
如果有一张表插入失败就回滚
27
27
IF
@@ERROR
<>
0
28
28
BEGIN
29
29
ROLLBACK
TRAN
30
30
31
31
RETURN
0
32
32
END
33
33
34
34
else
35
35
BEGIN
36
36
COMMIT
TRANSACTION
37
37
RETURN
1
38
38
END
39
39
40
40
41
41
GO
42
42
SET
QUOTED_IDENTIFIER
OFF
43
43
GO
44
44
SET
ANSI_NULLS
ON
45
45
GO
46
46
1
--
SQL事务处理:
2
2
3
3
SET
QUOTED_IDENTIFIER
ON
4
4
GO
5
5
SET
ANSI_NULLS
ON
6
6
GO
7
7
8
8
ALTER
procedure
myInsertTest
9
9
10
10
@job_desc
varchar
(
50
),
11
11
@min_lvl
tinyint
,
12
12
@max_lvl
tinyint
,
13
13
@id
int
,
14
14
@name
varchar
(
40
)
15
15
16
16
as
17
17
18
18
begin
tran
19
19
20
20
--
第一张表的插入语句
21
21
INSERT
INTO
dbo.jobs (job_desc, min_lvl,max_lvl)
VALUES
(
@job_desc
,
@min_lvl
,
@max_lvl
)
22
22
23
23
--
第二张表的插入语句
24
24
INSERT
INTO
test (id,name)
values
(
@id
,
@name
)
25
25
26
26
--
如果有一张表插入失败就回滚
27
27
IF
@@ERROR
<>
0
28
28
BEGIN
29
29
ROLLBACK
TRAN
30
30
31
31
RETURN
0
32
32
END
33
33
34
34
else
35
35
BEGIN
36
36
COMMIT
TRANSACTION
37
37
RETURN
1
38
38
END
39
39
40
40
41
41
GO
42
42
SET
QUOTED_IDENTIFIER
OFF
43
43
GO
44
44
SET
ANSI_NULLS
ON
45
45
GO
46
46