Created an Oracle Form to handle specific events / triggers like When-New-Form-Instance, Pre-Insert, Post-Insert, Pre-Update, Post-Update, Post-Query and Post-Forms-Commit.

I am doing the following simple tasks on these events to give you an example:

When-New-Form-Instance Trigger

Picking up Oracle Session ID through USERENV function and User to display below the title of the form, the following is the code written:

BEGIN

   SELECT    'ORACLE SESSION ID: '

          || USERENV ('SESSIONID')

          || ' USER NAME: '

          || USER

     INTO :VAL_FORM_INSTANCE

     FROM DUAL;

END;

Post-Query Trigger

Populating the Department Name.

BEGIN

   SELECT department_name

     INTO :scott_emp.dptname

     FROM dept

    WHERE department_id = :scott_emp.deptno;

EXCEPTION

   WHEN OTHERS

   THEN

      NULL;

END;

Pre-Insert Trigger

Checking if the Hiredate is current date or not.

BEGIN

   IF :SCOTT_emp.HIREDATE <> TRUNC (SYSDATE)

   THEN

      :VAL_PRE_INSERT := 'Hire Date must be current date.';

      RAISE form_trigger_failure;

   END IF;

   -- else ok

   :VAL_PRE_INSERT := 'Hire Date is valid.';

END;

Post-Insert Trigger

Counting total number of employees in table.

BEGIN

   SELECT 'Employee Count After: ' || COUNT ( * )

     INTO :val_pOST_insert

     FROM scott_emp;

END;

Pre-Update Trigger

Checking if current day is Sunday then stopping the user the update the record.

BEGIN

   IF TO_CHAR (SYSDATE, 'DAY') = 'SUN'

   THEN

      :VAL_PRE_UPDATE := 'Update is not allowed on Sundays';

      RAISE form_trigger_failure;

   END IF;

   :VAL_PRE_UPDATE := 'Update is allowed today.';

END;

Post-Update Trigger

Just giving a simple message.

BEGIN

   :VAL_POST_UPDATE := 'You updated ' || :scott_emp.ename || '''s record.';

END;

Post-Forms-Commit Trigger

Displaying Date and Time of Last Commit

BEGIN

   :VAL_POST_COMMIT :=

      'Last Commit executed on '

      || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS');

END;

The following is the screen shot of this form and source code(Table's Script and FMB file) can be download from the following link: