USING SQL SERVER Design and code a system of triggers to enforce the M-M relatio
ID: 658272 • Letter: U
Question
USING SQL SERVER
Design and code a system of triggers to enforce the M-M relationship. Use Figure 10A-40 as an example, but assume that departments with only one employee can be deleted by assigning the last employee in a department to Human Resources. Create test data, and demonstrate that your triggers work.
Assume that the relationship between these tables is M-M,
DEPARTMENT (DepartmentName (primary key), BudgetCode, OfficeNumber, Phone)
EMPLOYEE (EmployeeNumber (primary key), FirstName, LastName, Department (foreign key), Phone, Email)
FIGURE 10A-40
WORK
Is Required Parent TRANS
Is Required Child
Action on WORK (Parent)
Action on TRANS (Child)
Insert
Create a TRANS row
New TRANS must have a valid WorkID (enforced by DBMS)
Modify key or foreign key
Prohibit
FIGURE 10A-40
WORK
Is Required Parent TRANS
Is Required Child
Action on WORK (Parent)
Action on TRANS (Child)
Insert
Create a TRANS row
New TRANS must have a valid WorkID (enforced by DBMS)
Modify key or foreign key
Prohibit
Explanation / Answer
Create Trigger Deletion_of_Departement
After Insert , Update on Trans
Declare Departement char(20);
Rowc int;
Begin
Select Count(*) into Rowc
From Employee
Where First Name=Name;
Select from Departement Where Departement==Distinct Departement
If Roec=1 then
Update Employee
Set Employee.Departement=
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.