- 浏览: 276206 次
- 性别:
- 来自: 北京
最新评论
-
gotosuzhou:
好的 谢谢分享
Spring 事务异常回滚 -
cd249745647:
哈哈
Spring MVC REST 例子 -
向日葵上的小蜜蜂:
代码都差不多贴出来了,为啥不直接提供下载呢
Spring MVC REST 例子 -
he3109006290:
我猜它应该有个算法,当出现长时间处理的情况的,它自动会启动另外 ...
netty 疑惑 -
yanghoho6:
很好, 学习了,
oracle基本的索引概念.doc
Controlling When a Trigger Is Fired (BEFORE and AFTER Options)
The BEFORE or AFTER option in the CREATE TRIGGER statement specifies exactly when to fire the trigger body in relation to the triggering statement that is being run. In a CREATE TRIGGER statement, the BEFORE or AFTER option is specified just before the triggering statement. For example, the PRINT_SALARY_CHANGES trigger in the previous example is a BEFORE trigger.
In general, you use BEFORE or AFTER triggers to achieve the following results:
Use BEFORE row triggers to modify the row before the row data is written to disk.
Use AFTER row triggers to obtain, and perform operations, using the row ID.
Views that Require INSTEAD OF Triggers
A view cannot be modified by UPDATE, INSERT, or DELETE statements if the view query contains any of the following constructs:
A set operator
A DISTINCT operator
An aggregate or analytic function
A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
A collection expression in a SELECT list
A subquery in a SELECT list
A subquery designated WITH READ ONLY
Joins, with some exceptions, as documented in Oracle Database Administrator's Guide
If a view contains pseudocolumns or expressions, then you can only update the view with an UPDATE statement that does not refer to any of the pseudocolumns or expressions.
INSTEAD OF Trigger Example
Note:
You may need to set up the following data structures for this example to work:
CREATE OR REPLACE TRIGGER logontrig AFTER LOGON ON DATABASE
-- Just call an existing procedure. The ORA_LOGIN_USER is a function
-- that returns information about the event that fired the trigger.
CALL foo (ora_login_user)
The BEFORE or AFTER option in the CREATE TRIGGER statement specifies exactly when to fire the trigger body in relation to the triggering statement that is being run. In a CREATE TRIGGER statement, the BEFORE or AFTER option is specified just before the triggering statement. For example, the PRINT_SALARY_CHANGES trigger in the previous example is a BEFORE trigger.
In general, you use BEFORE or AFTER triggers to achieve the following results:
Use BEFORE row triggers to modify the row before the row data is written to disk.
Use AFTER row triggers to obtain, and perform operations, using the row ID.
Views that Require INSTEAD OF Triggers
A view cannot be modified by UPDATE, INSERT, or DELETE statements if the view query contains any of the following constructs:
A set operator
A DISTINCT operator
An aggregate or analytic function
A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
A collection expression in a SELECT list
A subquery in a SELECT list
A subquery designated WITH READ ONLY
Joins, with some exceptions, as documented in Oracle Database Administrator's Guide
If a view contains pseudocolumns or expressions, then you can only update the view with an UPDATE statement that does not refer to any of the pseudocolumns or expressions.
INSTEAD OF Trigger Example
Note:
You may need to set up the following data structures for this example to work:
CREATE TABLE Project_tab ( Prj_level NUMBER, Projno NUMBER, Resp_dept NUMBER); CREATE TABLE Emp_tab ( Empno NUMBER NOT NULL, Ename VARCHAR2(10), Job VARCHAR2(9), Mgr NUMBER(4), Hiredate DATE, Sal NUMBER(7,2), Comm NUMBER(7,2), Deptno NUMBER(2) NOT NULL); CREATE TABLE Dept_tab ( Deptno NUMBER(2) NOT NULL, Dname VARCHAR2(14), Loc VARCHAR2(13), Mgr_no NUMBER, Dept_type NUMBER); The following example shows an INSTEAD OF trigger for inserting rows into the MANAGER_INFO view. CREATE OR REPLACE VIEW manager_info AS SELECT e.ename, e.empno, d.dept_type, d.deptno, p.prj_level, p.projno FROM Emp_tab e, Dept_tab d, Project_tab p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE OR REPLACE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW DECLARE rowcnt number; BEGIN SELECT COUNT(*) INTO rowcnt FROM Emp_tab WHERE empno = :n.empno; IF rowcnt = 0 THEN INSERT INTO Emp_tab (empno,ename) VALUES (:n.empno, :n.ename); ELSE UPDATE Emp_tab SET Emp_tab.ename = :n.ename WHERE Emp_tab.empno = :n.empno; END IF; SELECT COUNT(*) INTO rowcnt FROM Dept_tab WHERE deptno = :n.deptno; IF rowcnt = 0 THEN INSERT INTO Dept_tab (deptno, dept_type) VALUES(:n.deptno, :n.dept_type); ELSE UPDATE Dept_tab SET Dept_tab.dept_type = :n.dept_type WHERE Dept_tab.deptno = :n.deptno; END IF; SELECT COUNT(*) INTO rowcnt FROM Project_tab WHERE Project_tab.projno = :n.projno; IF rowcnt = 0 THEN INSERT INTO Project_tab (projno, prj_level) VALUES(:n.projno, :n.prj_level); ELSE UPDATE Project_tab SET Project_tab.prj_level = :n.prj_level WHERE Project_tab.projno = :n.projno; END IF; END; Example: Monitoring Logons with a Trigger Note: You may need to set up data structures similar to the following for certain examples to work: CONNECT system/manager GRANT ADMINISTER DATABASE TRIGGER TO scott; CONNECT scott/tiger CREATE TABLE audit_table ( seq number, user_at VARCHAR2(10), time_now DATE, term VARCHAR2(10), job VARCHAR2(10), proc VARCHAR2(10), enum NUMBER); CREATE OR REPLACE PROCEDURE foo (c VARCHAR2) AS BEGIN INSERT INTO Audit_table (user_at) VALUES(c); END;
CREATE OR REPLACE TRIGGER logontrig AFTER LOGON ON DATABASE
-- Just call an existing procedure. The ORA_LOGIN_USER is a function
-- that returns information about the event that fired the trigger.
CALL foo (ora_login_user)
发表评论
-
ORACLE 中 SQL语句优化总结
2010-02-23 19:54 1032ORACLE 中 SQL语句优化总结 ... -
有用的v$视图脚本
2010-01-10 16:15 12031、基本的数据库信息 版本信息: select * f ... -
runstart脚本 测试两个SQL性能
2010-01-10 11:58 1249grant select on sys.v_$time ... -
spfile总结 转
2010-01-10 10:27 1362pfile(Initialization Paramete ... -
Oracle数据库字符集问题解析(转)
2010-01-07 21:50 1465Oracle数据库字符集问题解析 经常看到一些朋友问ORAC ... -
分析函数
2010-01-07 09:53 1100--row_number() SELECT ... -
Oracle常用SQL脚本
2009-12-29 22:54 1285测定数据的命中率 sel ... -
explain plan,autotrace,tkprof
2009-12-29 18:56 1821explain plan,autotrace,tkpr ... -
Oracle创建用户、表空间、导入导出 删除命令
2009-12-29 18:54 1708创建临时表空间 create temporary tab ... -
oracle分区
2009-12-22 23:27 2218分区有利于管理非常大 ... -
oracle数据类型
2009-12-22 22:35 1757oracle数据类型 Oracl ... -
oracle索引
2009-12-22 22:23 1272什么情况下应该使用B* ... -
数据库表(临时表)
2009-12-17 23:05 2246Oracle中的段(segment)是占用磁盘上存储空间的一个 ... -
redo和undo(部分引用别人)
2009-12-17 20:34 1710redo重做信息是oracle在在线重做日志文件中记录的信息, ... -
java调用存储过程
2009-12-17 19:11 996这段时间开始学习写存 ... -
oracle入门
2009-12-17 19:05 13071.1 ORACLE数据库简介 Oracle简称甲骨文, ... -
Oracle中更新语句的重启动
2009-12-16 22:50 2139Oracle中更新语句的重启动 考虑一个简单的update语句 ... -
oracle深入体系结构 笔记
2009-12-16 22:48 1640... -
oracle各参数
2009-12-16 19:30 1206名称 类型 类别 ... -
pl/sql例子
2009-12-16 19:24 22801、使用游标、loop、%type、%rowtype DE ...
相关推荐
### Oracle触发器概念与应用详解 #### 一、引言 在现代数据库管理系统(DBMS)中,触发器是一种重要的机制,用于确保数据的完整性、安全性和一致性。Oracle数据库中的触发器功能尤其强大,允许开发人员根据不同的...
### Oracle Trigger 概述 #### 6.1 触发器的类型 ##### 6.1.1 DML 触发器 DML (Data Manipulation Language) 触发器是一种特殊的触发器类型,它会在特定的数据操纵语言操作(如 INSERT、UPDATE 或 DELETE)执行时...
标题“Oracle Trigger at a Certain Time”涉及到Oracle数据库中的定时触发器,这是一种数据库对象,它可以在特定时间或事件发生时自动执行预定义的SQL语句或PL/SQL块。Oracle Trigger是数据库应用程序的重要组成...
这是本人为公司ERP开发人员做的trigger报告,内容全面,并比较深入,有图例说明解释,以及一些流程图例等,是学习trigger的一个很好笔记。
在Oracle数据库中,跟踪用户活动是一项重要的管理任务,有助于监控系统的使用情况、保障安全和优化性能。Oracle从Oracle8i开始引入了一类特殊触发器,它们不再局限于传统的DML事件,而是扩展到了系统级别,包括...
Oracle Table Form Trigger是Oracle数据库应用开发中的重要概念,主要涉及三方面:Oracle表格(Table)、表单(Form)以及触发器(Trigger)。本篇将详细阐述这三个关键元素及其相互关系。 1. Oracle表格(Table)...
### Trigger语法详解 #### 一、引言 在数据库领域,触发器(Trigger)是一种特殊类型的存储过程,它被设计用于响应对特定表的数据修改操作(如INSERT、UPDATE或DELETE)。触发器能够在这些操作发生时自动执行,...
Oracle培訓Oracle Procedure﹑Function、Trigger等
Oracle 异常及触发器 Oracle 异常是一种运行时错误处理机制,可以捕捉和处理程序执行过程中的错误。异常可以分为预定义异常和用户定义异常两种。 预定义异常是 Oracle 提供的预定义错误类型,例如 Invalid_cursor...
Oracle DML 触发器在数据库编程中的应用 Oracle DML 触发器是关系型数据库系统中的一个重要组件,能够自动执行特定的操作,以响应数据库中的变化。...[4] Oracle Trigger. Oracle Corporation, 2022.
oracleform常用Trigger的触发时机.pdf
本篇将重点介绍如何在使用JDeveloper这款强大的集成开发环境(IDE)时,结合Oracle的sequence和trigger来实现高效的数据操作。 首先,让我们理解一下sequence和trigger的概念。在Oracle中,sequence是一种自动递增...
Oracle数据库中的触发器(Trigger)是一种数据库对象,用于在特定的数据库操作(如INSERT、UPDATE、DELETE)发生之前或之后自动执行预定义的SQL语句或PL/SQL代码块。触发器是数据库级别的事件响应机制,它允许开发...
在Oracle中,查看、编辑、重命名和删除触发器同样使用类似的操作,如`SELECT * FROM USER_TRIGGERS`来查看触发器,`ALTER TRIGGER`用于编辑,`RENAME TRIGGER`用于重命名,`DROP TRIGGER`用于删除。 通过学习和掌握...
### Oracle自治事务(Trigger)详解 #### 一、概述 Oracle数据库中的触发器是一种存储过程,它被设计为当特定事件发生时自动执行。这些事件包括数据修改操作,如INSERT、UPDATE或DELETE等。触发器可以确保数据的...
在深入探讨如何利用Oracle触发器备份表数据之前,我们首先需要理解几个关键概念:Oracle数据库、触发器以及备份策略。Oracle数据库是全球领先的数据库管理系统之一,以其强大的性能、可靠的安全性和丰富的功能受到...