Create Instead of Insert Trigger

Question

I am a beginner at Oracle. I am trying to create an INSTEAD OF trigger to enforce a rule that no tutor should work more than 60 hours in a month

This is what I have so far

    CREATE TRIGGER limit_hour
    INSTEAD OF INSERT ON SESSIONHOURS 
    DECLARE
    totalHours NUMBER := 60;
    monthOnly DATE;
    totalSession NUMBER;
    FOR EACH ROW
    BEGIN
      INSERT INTO SESSIONHOURS(SESSIONDATEKEY, TUTORKEY, TOTALSESSION)
      SELECT EXTRACT (MONTH FROM DATE S.SESSIONDATEKEY), S.TOTALSESSION
      INTO monthOnly, totalSession
    FROM SESSIONHOURS S
    END;

The error "inappropriate INTO" keeps popping up. Also I need to assign the total sum of each session(30 min each) for the extracted month and then compare it with the "totalHour". How do I assign a time value to a date value? Any suggestions would be appreciated

Answer 1

Your INSERT statement is improperly written. It should be:

INSERT INTO SESSIONHOURS(SESSIONDATEKEY, TOTALSESSION)
  SELECT EXTRACT (MONTH FROM DATE S.SESSIONDATEKEY), S.TOTALSESSION
    FROM SESSIONHOURS S

This won't solve your "total hours" issues, but it takes care of the error you reported.

Best of luck.

Answer 2

Rather than use an INSTEAD OF trigger, it seems to me that a BEFORE INSERT trigger would be more appropriate. INSTEAD OF triggers are commonly used to map INSERTs on non-insertable views into INSERTs into the desired tables. A BEFORE INSERT trigger, on the other hand, is fired before each row is inserted into the table, allowing the values in the row to be checked for consistency, etc. Such a trigger might be used as follows:

CREATE TRIGGER SESSIONHOURS_BI
  BEFORE INSERT INTO SESSIONHOURS
  FOR EACH ROW
DECLARE
  nTotal_tutor_hours  NUMBER;
BEGIN
  SELECT SUM(HOURS)
    INTO nTotal_tutor_hours
    FROM SESSIONHOURS s
    WHERE s.TUTORKEY = :new.TUTORKEY;

  IF nTotal_tutor_hours + :new.HOURS > 60 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Addition of ' || :new.HOURS ||
                                    ' for tutor ' || :new.TUTORKEY ||
                                    ' exceeds monthly limit of 60');
  END IF;
END SESSIONHOURS_BI;

This trigger will be fired before an INSERT into SESSIONHOURS is processed. It queries the database to determine the total number of hours worked by the tutor whose key is in the INSERT statement (:new.TUTORKEY). If the total hours worked PLUS the hours in the new record exceeds 60 an exception is raised, which causes the INSERT to be aborted. Otherwise the trigger returns normally and the INSERT proceeds.

HOWEVER - even this won't work. The problem is that the trigger is defined on the table SESSIONHOURS, and inside the trigger there is a SELECT on the SESSIONHOURS table. This will cause the database to throw the dreaded ORA-04091 exception, with explanatory text table SESSIONHOURS is mutating, trigger/function may not see it. There are several ways to fix this, the BEST of which is to follow a simple rule:

***NEVER* IMPLEMENT BUSINESS LOGIC IN A TRIGGER!!!!

A rule such as "tutors may not work more than 60 hours" is a business rule. This should be implemented in your application logic, not in a trigger. Create a procedure or function in the database to perform the INSERT INTO SESSIONHOURS and any needed validation logic, and call that procedure every time you need to insert data into SESSIONHOURS. Don't try putting the validation logic into a trigger - you'll find it's rather difficult, and will lead to never-ending debugging sessions, as noted here.

Best of luck.