sGc066KZ

1CREATE TABLE [dbo].[Studies] (
2    [ssn]        CHAR (10) NOT NULL,
3    [courseCode] CHAR (6)  NOT NULL,
4    PRIMARY KEY CLUSTERED ([ssn] ASC, [courseCode] ASC),
5    FOREIGN KEY ([courseCode]) REFERENCES [dbo].[Course] ([courseCode]),
6    FOREIGN KEY ([ssn]) REFERENCES [dbo].[Student] ([ssn])
7);
8
9
10GO
11CREATE TRIGGER CreditLimit 
12ON Studies
13AFTER INSERT
14AS
15DECLARE @ssn varchar(10)
16SELECT @ssn = ssn FROM inserted 
17IF EXISTS(
18			SELECT SUM(credits)
19						FROM Studies s 
20					INNER JOIN Assignment a
21					ON a.courseCode = s.courseCode
22						WHERE ssn = @ssn
23						HAVING SUM(credits) <= 45)
24BEGIN
25RAISERROR ('A student can not study more than 45 credits', 16, 1);
26ROLLBACK TRANSACTION;
27RETURN    
28END