USING Microsoft SQL Server Management Studio-- NOTE: This is SQL NOT MySQL Table

Question

USING Microsoft SQL Server Management Studio-- NOTE: This is SQL NOT MySQL

Tables:

Employee: EMP_ID, EMP_FNAME, EMP_LNAME, EMP_STREET, EMP_CITY, EMP_STATE, EMP_ZIP_EMP_START_DATE

Table: TABLE_ID, AREA_ID, TABLE_SEATS

Area: AREA_ID, AREA_NAME, AREA_SUPERVISOR_EMPLOYEE_ID

Customer: CUST_ID, CUST_LAST_NAME, CUST_NUMBER_OF_GUESTS

Assignment: EMP_ID, TABLE_ID

Seating: CUST_ID, TABLE_ID, SEATING_COST, SEATING_DATE, SEATING_TIP

Implement a trigger on the seating table that prevents the assignment of a customer to a table if there are not sufficient seats at that table. Need two test scripts. One that allows data in and one that rejects data.

Explanation / Answer

Hi

Test scripts
1.
Insert into Table(1,2,3)
Insert into Seating(1,1,30,'2018-05-04',5)
Insert into Seating(2,1,30,'2018-05-04',5)
this both inserts will be passed because, table has 3 seats and only 2 are filled
2. failure script
Insert into Table(1,2,3)
Insert into Seating(1,1,30,'2018-05-04',5)
Insert into Seating(2,1,30,'2018-05-04',5)
Insert into Seating(3,1,30,'2018-05-04',5)
Insert into Seating(4,1,30,'2018-05-04',5) --- this will fail because table is already full
Thumbs up if this was helpful, otherwise let me know in comments

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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