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
最新文章
- 关于 矩阵在ACM中的应用
- HTML5做的浏览器欢迎界面自动跳转
- myeclipse 10 优化
- jquery.validate中使用remote,remote相同值不校验问题解决
- webservice调用接口,接口返回数组类型
- Hadoop Failed to set permissions of path
- 读改善c#代码157个建议:建议13~15
- Winsock SPI-Socks5-SSL
- Java并发框架——AQS中断的支持
- Centos6.5 pppoe-server
- 闲谈REST API
- [20190214]11g Query Result Cache RC Latches补充.txt
- 修改主机IP地址
- weichat Small 程序
- Mockito/PowerMockito Straige Issues
- ECharts配置项之title(标题)
- bzoj5003: 与链 5004: 开锁魔法II 5005:乒乓游戏
- GDBT
- HTML5学习笔记(二十五):事件
- 题目1102:最小面积子矩阵(暴力求解&;最大连续子序列)