if @@ROWCOUNT > 1, rollback : rowcount « System Settings « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. System Settings
3. rowcount

3> CREATE TABLE BUDGET
4>     (dept_name      varchar(30)    not null,
5>      parent_name    varchar(30)    null,
6>      budget_amt     money          not null)
7> GO
1> INSERT INTO budget values ('Internal Training', 'Training', $10)
2> INSERT INTO budget values ('Training', 'Services', $100)
3> INSERT INTO budget values ('Services', NULL, $500)
4> GO
1>
2> CREATE TRIGGER update_budget
3> ON budget FOR update AS
4> DECLARE @rows int
5> SELECT @rows = @@ROWCOUNT
6> IF (@rows=0) RETURN
7> IF (@rows > 1) BEGIN
8>     PRINT 'Only one row can be updated at a time'
9>     ROLLBACK TRAN
10>     RETURN
11> END
12> IF (SELECT parent_name FROM inserted) IS NULL RETURN
13> UPDATE budget
14>     SET budget_amt = budget_amt + (SELECT budget_amt FROM inserted) -
15>                                   (SELECT budget_amt FROM deleted)
16> WHERE dept_name = (SELECT parent_name FROM inserted)
17>
18>
19> drop TRIGGER update_budget;
20> GO
1>
2> drop table BUDGET;
3> GO