t5tJz09D

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	select courses.enrolled, courses.lim,
43				 case
44					 when courses.enrolled >= courses.lim then
45						 --raise notice 'asdf'
46						 return null
47					 else 0
48					 end
49	from Courses where courses.num = New.course;
50
51	update courses
52	set enrolled = enrolled + 1
53	where courses.num = New.course;
54
55
56 return new;
57end;
58
59$BODY$
60	LANGUAGE plpgsql
61	VOLATILE;
62
63
64
65create trigger update_enrollment before insert
66	on enrollment for each row execute procedure update_enrolled_student_count();
67
68
69-- insert starting data
70
71insert into enrollment values(1, 'CS1501');
72insert into enrollment values(2, 'CS1501');
73insert into enrollment values (3, 'CS1501');
74insert into enrollment values (4, 'CS1501');
75
76select * from courses;
77
78--update courses set enrolled = 2 where num = 'CS1501';
79
80--insert into enrollment values(1, 'CS1555');
81--update courses set enrolled = 1 where num = 'CS1555';
82
83--insert into enrollment values(4, 'CS1520');
84--insert into enrollment values(5, 'CS1520');
85--update courses set enrolled = 2 where num = 'CS1520';