SLnv2UgX

1-- setup tables
2
3drop table if exists students cascade;
4drop table if exists courses cascade;
5drop table if exists enrollment cascade;
6
7create table students (
8	id integer primary key,
9	name varchar(24)
10);
11
12insert into students values(1, 'Alice');
13insert into students values(2, 'Bob');
14insert into students values(3, 'Charlie');
15insert into students values(4, 'Denise');
16insert into students values(5, 'Edward');
17
18drop table if exists courses cascade;
19
20create table courses (
21	num varchar(6) primary key,
22	open_class boolean not null,
23	enrolled integer default 0,
24	lim integer default 3
25);
26
27insert into courses values('CS1555', True);
28insert into courses values('CS1501', True);
29insert into courses values('CS1520', True);
30
31drop table if exists enrollment cascade;
32
33create table enrollment (
34	student integer references students(id),
35	course varchar(6) references courses(num)
36);
37
38create or replace function update_enrolled_student_count()
39	returns trigger as $BODY$
40BEGIN
41
42	perform courses.enrolled, courses.lim,
43				 case
44					 when courses.enrolled >= courses.lim then
45							 courses.open_class = FALSE
46					 end
47	from Courses where courses.num = New.course;
48
49	update courses
50	set enrolled = enrolled + 1
51	where courses.num = New.course;
52
53
54 return new;
55end;
56
57$BODY$
58	LANGUAGE plpgsql
59	VOLATILE;
60
61
62
63create trigger update_enrollment before insert
64	on enrollment for each row execute procedure update_enrolled_student_count();
65
66
67-- insert starting data
68
69insert into enrollment values(1, 'CS1501');
70insert into enrollment values(2, 'CS1501');
71insert into enrollment values (3, 'CS1501');
72insert into enrollment values (4, 'CS1501');
73
74select * from courses;
75
76--update courses set enrolled = 2 where num = 'CS1501';
77
78--insert into enrollment values(1, 'CS1555');
79--update courses set enrolled = 1 where num = 'CS1555';
80
81--insert into enrollment values(4, 'CS1520');
82--insert into enrollment values(5, 'CS1520');
83--update courses set enrolled = 2 where num = 'CS1520';