STUDENT Szip StdMajo StdCity StdState StdClass StdGPA 101 Joe Smith FR 3.8 18121
ID: 3704303 • Letter: S
Question
STUDENT Szip StdMajo StdCity StdState StdClass StdGPA 101 Joe Smith FR 3.8 18121 IS 56352 98042 IS Eau Clare WI 102 Rob King Melrose MN IS UR 3.2 103 Dan Robinson Sartell MN JR 3.6 104 Williams St. Cloud MN 56301 ACCT SR 3.2 Sue 105 Don Robinson St. Paul MN 55103 MKTG SR 3.4 COURSE CrsDesc CrsCredits CSCI 200 Elements of Computing Application Program Dev.I Application Program Dev. II Data Mining for Decision Support Systems Analysis and Design I Project Management Accounting Principles II Database Design IS 250 IS 251 IS 454 IS 356 IS 460 ACCT 291 IS 443Explanation / Answer
Alter query:
ALTER TABLE STUDENT
ADD StdBalance DECIMAL(6,2)
Trigger for business rule 1:
create trigger trgCheckMaxNumberOfStudents
on STUDENT
AFTER INSERT
AS
-- Get student count
declare @tableCount int
select @tableCount = Count(1) -- 1 represent Stdid column here, it is unique and primary key
from STUDENT
-- Roll back the insertion if the row count exceeds 30
if @tableCount > 30
begin
rollback
end
go
Trigger for Business rule 2:
create trigger trgCheckMaxNumberOfStudents
on STUDENT
AFTER INSERT
AS
-- Roll back if the Std balance is more than 500
IF EXISTS (SELECT 1 FROM inserted Where StdBalance > 500)
begin
rollback
end
go
Note: You can use instead of trigger as well to achieve the same I have used After Insert trigger, so the insert operartion takes place but its rolled back if rules are not followed
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.