# Triggers Exercises {:toc} - Upload all .sql in the __/highschool/triggers/__ folder in your Git repository. - Presentation will be taken into account. Using <a href="/DAM-BD/UD1/exercicis/01_ddl_highschool.html" target="_blank">the highschool database</a> __Files:__ - <a href="/itb/DAM-BD/files/en/highschool/schema/create_schema_highschool.sql" download="create_schema_highschool.sql">create_schema_highschool.sql</a> - <a href="/itb/DAM-BD/files/en/highschool/schema/create_schema_highschool_material.sql" download="create_schema_highschool_material.sql">create_schema_highschool_material.sql</a> - <a href="/itb/DAM-BD/files/en/highschool/data/insert_data_highschool.sql" download="insert_data_highschool.sql">insert_data_highschool.sql</a> - <a href="/itb/DAM-BD/files/en/highschool/data/insert_data_highschool_material.sql" download="insert_data_highschool_material.sql">insert_data_highschool_material.sql</a> <div class="mermaid"> erDiagram MATERIAL { int material_id PK varchar100 name int amount } MATERIAL_HISTORY }o--|| TEACHER : "" MATERIAL_HISTORY }o--|| MATERIAL : "" MATERIAL_HISTORY { int material_id FK varchar9 teacherDNI FK int amount timestamp timestamp } PERSON { char9 DNI PK varchar50 name varchar50 surname varchar30 phone date birth_date varchar100 personal_email varchar100 corporate_email } TEACHER |o--|| PERSON: "" TEACHER { char9 DNI FK } STUDENT |o--|| PERSON: "" STUDENT { char9 DNI FK char9 NIA int course_id FK char group_letter FK } GROUP |o--|| TEACHER: "is tutor of" GROUP ||--|{ STUDENT: "belogns" GROUP }|--|| COURSE: "" GROUP { int course_id FK char letter PK char9 tutorDNI FK } ENROLLED }|--|| STUDENT : "" ENROLLED }|--|| SUBJECT: "" ENROLLED }|--|| YEAR: "" ENROLLED { char9 studentDNI FK char4 subject_id FK int year FK decimal4_2 grade } YEAR { int start_year PK int end_year } TEACHES }|--|| TEACHER: "" TEACHES }|--|| SUBJECT: "" TEACHES }|--|| YEAR: "" TEACHES { char9 teacherDNI FK char4 subject_id FK int year FK } FAMILY { int family_id PK varchar100 name } COURSE }o--|| FAMILY : "" COURSE { int course_id PK int family_id FK varchar100 name varchar6 acronym enum level } SUBJECT }o--|| COURSE : "" SUBJECT { char4 subject_id PK int course_id FK varchar100 name varchar6 acronym int etcs int hours } </div> Create the following triggers: ## Triggers ### Material inventory - __File:__ create_trigger_material_inventory.sql Create all the triggers needed in the table __total_material__ so the table always has the total amount of materials available. ### Enrolled course - __File:__ create_trigger_check_enrolled_course.sql Create a trigger that prevents a student to enroll in any subject that doesn't belong to the course that the student is enrolled. The error message should be: ``` The subject "XXXX" doesn't belong to the same course as the student "XXXXXXXX" ``` ### Maximum subjects enrolled - __File:__ create_trigger_check_maximum_subjects_enrolled.sql Create a trigger that prevents a student to enroll in more than 6 subjects at the same time in the same year. ``` The student "XXXXXXXX" can't be enrolled in more than 6 subjects in the year "XXXX" ``` ### Arleady passed subject - __File:__ create_trigger_check_arleady_passed_subject.sql Create a trigger that prevents a student to enroll in a subject if he already passed the subject in previous years. ``` The student "XXXXXXXX" has already passed the subject "XXXX" in the year "XXXX" ``` ### Maximum subjects teached - __File:__ create_trigger_check_maximum_subjects_teached.sql Create a trigger that prevents a teacher to teach a subject if the total weekly hours is greater than 18. There is 33 weeks in one academic year. ``` The teacher "XXXXXXXX" can't teach the subject "XXXX" since the total weekly hours would be "XX.XX" ```