SQLServer/SaferUpdate.sql at master · vijaybandi/SQLServer · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
/*

UPDATE Production Receipt

*/

BEGIN TRAN

DECLARE @check INT
SELECT @check = COUNT(*) FROM OurTable WHERE OurColumn = Criteria

UPDATE OurTable
SET OurColumn = NEWVALUE
WHERE OurColumn = Criteria
IF @@ROWCOUNT <> @check
BEGIN
	ROLLBACK TRAN
END
ELSE
	COMMIT TRAN


/* EXAMPLE */

-- Test table with test values
CREATE TABLE #t(
	Number INT
)

INSERT INTO #t VALUES (1)
INSERT INTO #t VALUES (2)
INSERT INTO #t VALUES (5)
INSERT INTO #t VALUES (4)
SELECT * FROM #t

-- Begin the safe update
BEGIN TRAN

DECLARE @check INT
SELECT @check = COUNT(*) FROM #t WHERE Number = 5

UPDATE #t
SET Number = 3
WHERE Number = 5
IF @@ROWCOUNT <> @check
BEGIN
	ROLLBACK TRAN
END
ELSE
	COMMIT TRAN

SELECT * FROM #t