Create an INSTEAD OF trigger on INSERT Session Hours to enforce the rule that no

Question

Create an INSTEAD OF trigger on INSERT Session Hours to enforce the rule that no tutor should work more than 60 hours in a month. The logical steps before trying to actually write the trigger. It is easy to get confused if you don't have a clear recipe to follow: Get the date from the INSERTED table. Extract the month. Create a variable for the total hours. Assign to total the sum of each session for that month (assuming 30 minutes each). Check to see if the sum > 60 Check if it is output a message. Otherwise complete the insert into the Session table.

Explanation / Answer

If no. of sessions are greater than 120, that means no. of hours > 60. As each session is of 30 minutes or 0.5 Hours.

Using Teachers table name

CREATE TRIGGER SessionHours
ON Teachers
INSTEAD OF INSERT
AS
BEGIN

SET NOCOUNT ON;
DECLARE @currentMonth INT
DECLARE @totalHours REAL

SET @currentMonth = EXTRACT(YEAR FROM INSERT(Date))
SELECT @totalHours = SUM(Sessions)
FROM Teachers
WHERE Month = @currentMonth

IF @totalHours > 120
BEGIN

RAISEERROR('Cant work more than 60 Hours')

END

END

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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