I AM USING SQL SERVER 2012 - any solution in their format would help greatly Des
ID: 658370 • Letter: I
Question
I AM USING SQL SERVER 2012 - any solution in their format would help greatly
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.
DEPARTMENT (DepartmentName (primary key), BudgetCode, OfficeNumber, Phone)
EMPLOYEE (EmployeeNumber (primary key), FirstName, LastName, Department (foreign key), Phone, Email)
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
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_Dept
After Insert , Update on Trans
Declare Dept char(20);
Rowc int;
Begin
Select Count(*) into Rowc
From Employee
Where First Name=Name;
Select
from
Dept Where
Dept==Distinct
Dept
If Roec=1 then
Update Employee
Set Employee.Dept=
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.