Press "Enter" to skip to content

Oracle Form’s Trigger Tutorial With Sample FMB

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

One Comment

Comments are closed.