Sponsored

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: Form_Triggers.Zip


Oracle Forms Recipes eBookDownload

Tutorial is given on Oracle form's event with an example form fmb file.

Post a Comment

  1. I have looking Export and import .fmb file exampale.if have u any one please share my mail id shashi634@gmail.com

    ReplyDelete

V. Kapoor

{picture#https://3.bp.blogspot.com/-q3wCssWUHo8/ViDFfCpPEuI/AAAAAAAAD0M/MHPvgnl3T3U/s1600/authpic.JPG} Vinish Kapoor is a Software Consultant, currently working in a reputed IT company in Noida, India and doing blogging on Oracle Forms, SQL and PLSQL topics. He is an author of an eBook Oracle Forms Recipes, which is available on Google play. {facebook#https://www.facebook.com/foxinfotech2014} {twitter#https://twitter.com/foxinfotech} {google#https://plus.google.com/u/0/+VKapoor/posts}
Fox Infotech. Powered by Blogger.