Home p Insert Backspace Page Ervd PgUp Delete Home Enter End Ctr Q1. CSUEB is a

Question

Home p Insert Backspace Page Ervd PgUp Delete Home Enter End Ctr Q1. CSUEB is a university. Its database includes a Student Table and a StudentMajor table. Each Student can declare at most two Include in your description the type of SQL statement to be used, the tables involved, and whether the rule is checked before inserting records into a table or after they are inserted. majors. Describe how this rule can be enforced in an enterprise database. Student Table StudentMajor Table StudentNo StudentName S StudentNo MajorlD Alice -- ILI Bob nort its sales process. The information to be

Explanation / Answer

The above rule can be enforced in the database by using a BEFORE INSERT TRIGGER on StudentMajor table, which will allow insertion only if the number of majors for the student is less than.

The trigger used for the scenario is :

CREATE OR REPLACE TRIGGER trg_before_studMajor_insert

BEFORE INSERT

on StudentMajor

FOR EACH ROW

DECLARE

num_record number;

BEGIN

select count(*) into num_record from StudentMajor where StudentNo = :new.StudentNo ;

IF( num_record >= 2)

RAISE_APPLICATION_ERROR(-20000,'Student can select at most 2 Major subjects');

END IF;

END;

Whenever, a record is inserted into StudentMajor table the above trigger is executed and if the number of records for the StudentNo to be inserted is less than 2, then insertion happens else it raises an error and the record is not inserted in StudentMajor table.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.