|
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
|