oracle 11g PL/SQL Programming学习八

最新推荐文章于 2024-01-24 13:47:50 发布

Cryking

最新推荐文章于 2024-01-24 13:47:50 发布

阅读量1.7k

点赞数

分类专栏： oracle PLSQL学习系列

本文链接：https://blog.csdn.net/edcvf3/article/details/9035147

版权

oracle 同时被 2 个专栏收录

92 篇文章 3 订阅

订阅专栏

PLSQL学习系列

26 篇文章 13 订阅

订阅专栏

----------------------------------------------------------------------------
-----------------PL/SQL学习笔记系列 By Cryking-----------------
------------------------转载请注明出处,谢谢!------------------------

DDL触发器
原型:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} ddl_event ON {DATABASE | SCHEMA}
[WHEN (logical_expression)]
[DECLARE]
declaration_statements;
BEGIN
execution_statements;
END [trigger_name];
注:INSTEAD OF子句只能用在审计创建的事件中.(如:INSTEAD OF CREATE ON SCHEMA)
一个典型的DDL触发器例子:

13:08:58 SYS@orcl> conn scott/tiger
已连接。
13:09:14 SCOTT@orcl> CREATE TABLE audit_creation
13:10:49   2  ( audit_creation_id NUMBER
13:10:49   3  , audit_owner_name VARCHAR2(30) CONSTRAINT audit_creation_nn1 NOT NULL
13:10:49   4  , audit_obj_name VARCHAR2(30) CONSTRAINT audit_creation_nn2 NOT NULL
13:10:49   5  , audit_date DATE CONSTRAINT audit_creation_nn3 NOT NULL
13:10:49   6  , CONSTRAINT audit_creation_p1 PRIMARY KEY (audit_creation_id));

表已创建。

已用时间:  00: 00: 00.92
13:10:50 SCOTT@orcl> CREATE SEQUENCE audit_creation_s1;

序列已创建。

已用时间:  00: 00: 00.03
13:10:50 SCOTT@orcl> CREATE OR REPLACE TRIGGER audit_creation
13:10:50   2    BEFORE CREATE ON SCHEMA
13:10:50   3  BEGIN
13:10:50   4    INSERT INTO audit_creation
13:10:50   5    VALUES
13:10:50   6      (audit_creation_s1.nextval,
13:10:50   7       ORA_DICT_OBJ_OWNER,
13:10:50   8       ORA_DICT_OBJ_NAME,
13:10:50   9       SYSDATE);
13:10:50  10  END audit_creation;
13:10:50  11  /

触发器已创建

已用时间:  00: 00: 00.37
13:11:24 SCOTT@orcl> CREATE SYNONYM mythology FOR plsql.some_myth;

同义词已创建。

已用时间:  00: 00: 00.35
13:11:39 SCOTT@orcl> COL audit_creation_id FORMAT 99999999 HEADING "Audit|Creation|ID #"
13:12:13 SCOTT@orcl> COL audit_owner_name FORMAT A6 HEADING "Audit|Owner|Name"
13:12:19 SCOTT@orcl> COL audit_obj_name FORMAT A8 HEADING "Audit|Object|Name"
13:12:24 SCOTT@orcl> COL audit_obj_name FORMAT A9 HEADING "Audit|Object|Name"
13:12:29 SCOTT@orcl> SELECT * FROM audit_creation;

    Audit Audit  Audit
 Creation Owner  Object
     ID # Name   Name      AUDIT_DATE
--------- ------ --------- --------------
        2 SCOTT  MYTHOLOGY 04-6月 -13

已选择 1 行。

已用时间:  00: 00: 00.01

DML触发器
DML触发器可在INSERT,UPDATE,DELETE语句之前或之后触发.
DML触发器可以是语句级或行级的.语句级触发器触发和执行一条语句或一组语句一次,而不管有多少行被DML事件影响.
行级触发器在由DML语句影响的每行发生改变时都触发和执行一条语句或一组语句.
应当注意的是你不能使用DCL语句(如:COMMIT、ROLLBACK、SAVEPOINT等)在DML触发器中,除非你使用了自治事务。
原型:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER}
{INSERT | UPDATE | UPDATE OF column1 [, column2 [, column(n+1)]] | DELETE}
ON table_name
[FOR EACH ROW]
[WHEN (logical_expression)]
[DECLARE]
[PRAGMA AUTONOMOUS_TRANSACTION;]
declaration_statements;
BEGIN
execution_statements;
END [trigger_name];

你不能在视图上定义BEFORE或AFTER子句.你可以使用OR来连接几种触发事件,如:INERT OR UPDATE OR DELETE ON ...
WHEN子句控制触发器语句执行的条件.
与存储过程、函数不同的是,当你要声明局部变量的时候,你必须加上DECLARE.

语句级触发器(Statement-Level Triggers)
因为它被表上的一个更改触发,也被称为表级触发器.当用户向表插入、更新、删除一行或多行数据时,语句级触发器捕获并处理信息.
你也可以针对指定列的改变来触发.(使用UPDATE OF子句)
注意:你不能在语句级触发器中使用WHEN子句,你也不能引用NEW或OLD伪记录,否则会报ORA-04082错误.
oracle 10g不支持引用序列的.nextval或.currval伪列在PL/SQL块中.(没有环境没有验证)
语句级触发器使用示例:

23:30:53 SCOTT@orcl> CREATE TABLE price_type_log
23:31:16   2  ( price_id NUMBER CONSTRAINT price_type_log_nn1 NOT NULL
23:31:16   3  , user_id VARCHAR2(32) CONSTRAINT price_type_log_nn2 NOT NULL
23:31:16   4  , action_date DATE CONSTRAINT price_type_log_nn3 NOT NULL
23:31:16   5  , CONSTRAINT price_type_log_p1 PRIMARY KEY (price_id));
Elapsed: 00:00:00.10
23:31:18 SCOTT@orcl> SET FEEDBACK ON
23:31:28 SCOTT@orcl> CREATE TABLE price(
23:34:15   2  ID NUMBER ,
23:34:15   3  price_type VARCHAR2(10),
23:34:15   4  actual_price number
23:34:15   5  );

Table created.

Elapsed: 00:00:00.06
23:34:36 SCOTT@orcl> create sequence price_log_s1;

Sequence created.

Elapsed: 00:00:00.12
23:34:58 SCOTT@orcl> CREATE OR REPLACE TRIGGER price_t1
23:35:08   2    AFTER UPDATE OF price_type ON price
23:35:08   3  BEGIN
23:35:08   4    -- This statement only works in Oracle 11g forward.
23:35:08   5    INSERT INTO price_type_log VALUES (price_log_s1.nextval, USER, SYSDATE);
23:35:08   6  END price_t1;
23:35:09   7  /

Trigger created.

Elapsed: 00:00:00.13
23:35:10 SCOTT@orcl> UPDATE price p
23:35:46   2     SET p.price_type = p.price_type
23:35:46   3   WHERE EXISTS (SELECT NULL FROM price q WHERE q.id = p.id);

0 rows updated.

Elapsed: 00:00:00.08
23:35:47 SCOTT@orcl> SELECT * FROM price_type_log;

  PRICE_ID USER_ID                          ACTION_DATE
---------- -------------------------------- ------------
         1 SCOTT                            04-JAN-13

1 row selected.

Elapsed: 00:00:00.03

行级触发器(Row-Level Triggers)
行级触发器让你捕获每行的新值和旧值.
这些信息可以让你审计改变、分析行为、不用数据库恢复操作来恢复以前的数据.
当你在行级触发器中使用FOR EACH ROW子句时,有两个伪记录,它们是复合变量.
在WHEN子句中,new或old是伪记录的变量名,在触发体中:new和:old作为绑定变量使用.它们在声明块
和触发体之所以不同是因为它们是不同的PL/SQL块.触发器声明部分是调用块,触发体是被调用块.
伪记录的元素是伪字段.
new或old伪记录是会话级复合变量.
它们在DML触发事件范围内隐式声明.
示例1：

23:36:02 SCOTT@orcl> CREATE TABLE contact(
00:32:21   2  ID NUMBER,
00:32:21   3  FIRST_NAME VARCHAR2(20),
00:32:21   4  last_name VARCHAR2(20)
00:32:21   5  );

Table created.

Elapsed: 00:00:00.06
00:32:22 SCOTT@orcl> CREATE OR REPLACE TRIGGER contact_insert_t1
00:32:29   2    BEFORE INSERT OR UPDATE OF last_name ON contact
00:32:29   3    FOR EACH ROW
00:32:29   4    WHEN (REGEXP_LIKE (new.last_name, ' '))
00:32:29   5  BEGIN
00:32:29   6    :new.last_name := REGEXP_REPLACE(:new.last_name, ' ', '-', 1, 1);
00:32:29   7  END contact_insert_t1;
00:32:30   8  /

Trigger created.

Elapsed: 00:00:00.10
00:34:27 SCOTT@orcl> INSERT INTO contact VALUES(1,'Catherine','Zeta Jones');

1 row created.

Elapsed: 00:00:00.00
00:34:30 SCOTT@orcl> SELECT * FROM contact;

        ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
         1 Catherine            Zeta-Jones

1 row selected.

Elapsed: 00:00:00.01

示例2:

--控制自增列,在插入操作不提供event_id值时触发
CREATE OR REPLACE TRIGGER connection_log_t1
  BEFORE INSERT ON connection_log
  FOR EACH ROW
  WHEN (new.event_id IS NULL)
BEGIN
  :new.event_id := connection_log_s1.nextval;
END;

组合触发器(Compound Triggers)
当你向表增、删、改数据时,组合触发器就是语句级和行级触发器的组合.
你可以使用组合触发器捕获4个时间点的信息:
A,语句触发前;B,触发语句影响每行改变之前;C,触发语句影响每行改变之后;D,语句触发后
注意:组合触发器不支持WHEN子句,也不支持自治事务参数(可在子存储、函数中使用自治事务).
组合触发器不支持EXCEPTION块.
:new和:old伪记录只在BEFORE EACH ROW和AFTER EACH ROW块中有效.
组合触发器在你的DML语句使用批量操作时,有显著的性能优势。
原型:
CREATE [OR REPLACE] TRIGGER trigger_name
FOR {INSERT | UPDATE | UPDATE OF column1 [, column2 [, column(n+1)]] | DELETE}
ON table_name
COMPOUND TRIGGER
[BEFORE STATEMENT IS
[declaration_statement;]
BEGIN
execution_statement;
END BEFORE STATEMENT;]
[BEFORE EACH ROW IS
[declaration_statement;]
BEGIN
execution_statement;
END BEFORE EACH ROW;]
[AFTER EACH ROW IS
[declaration_statement;]
BEGIN
execution_statement;
END AFTER EACH ROW;]
[AFTER STATEMENT IS
[declaration_statement;]
BEGIN
execution_statement;
END AFTER STATEMENT;]
END [trigger_name];

使用示例:

13:34:14 SCOTT@orcl> CREATE TABLE price_event_log
13:52:27   2  ( price_log_id NUMBER
13:52:27   3  , price_id NUMBER
13:52:27   4  , created_by NUMBER
13:52:27   5  , creation_date DATE
13:52:27   6  , last_updated_by NUMBER
13:52:27   7  , last_update_date DATE );

表已创建。

已用时间:  00: 00: 00.92
13:52:29 SCOTT@orcl> CREATE SEQUENCE price_event_log_s1;

序列已创建。

已用时间:  00: 00: 00.04
13:52:34 SCOTT@orcl> EXEC dbms_application_info.set_client_info('3');

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.00
13:52:47 SCOTT@orcl> CREATE TABLE price
13:54:05   2  ( price_id NUMBER
13:54:05   3  , last_updated_by NUMBER
13:54:05   4  , last_update_date DATE );

表已创建。

已用时间:  00: 00: 00.01
13:54:06 SCOTT@orcl> CREATE OR REPLACE TRIGGER compound_price_update_t1
13:54:10   2    FOR UPDATE ON price COMPOUND TRIGGER
13:54:10   3  -- Declare a global record type.
13:54:10   4  TYPE price_record IS RECORD(price_log_id price_event_log.price_log_id%TYPE
13:54:10   5  , price_id price_event_log.price_id%TYPE
13:54:10   6  , created_by price_event_log.created_by%TYPE
13:54:10   7  , creation_date price_event_log.creation_date%TYPE
13:54:10   8  , last_updated_by price_event_log.last_updated_by%TYPE
13:54:10   9  , last_update_date price_event_log.last_update_date%TYPE);
13:54:10  10  -- Declare a global collection type.
13:54:10  11  TYPE price_list IS TABLE OF PRICE_RECORD;
13:54:10  12  -- Declare a global collection and initialize it.
13:54:10  13  price_updates PRICE_LIST := price_list();
13:54:10  14    BEFORE EACH ROW IS
13:54:10  15  -- Declare or define local timing point variables.
13:54:10  16  c NUMBER;
13:54:10  17  user_id NUMBER := NVL(TO_NUMBER(SYS_CONTEXT('userenv', 'client_info')), -1);
13:54:10  18  BEGIN
13:54:10  19    -- Extend space and assign dynamic index value.
13:54:11  20    price_updates.EXTEND;
13:54:11  21    c := price_updates.LAST;
13:54:11  22    price_updates(c).price_log_id := price_event_log_s1.nextval;
13:54:11  23    price_updates(c).price_id := :old.price_id;
13:54:11  24    price_updates(c).created_by := user_id;
13:54:11  25    price_updates(c).creation_date := SYSDATE;
13:54:11  26    price_updates(c).last_updated_by := user_id;
13:54:11  27    price_updates(c).last_update_date := SYSDATE;
13:54:11  28  END BEFORE EACH ROW;
13:54:11  29    AFTER STATEMENT IS BEGIN
13:54:11  30  -- Bulk insert statement.
13:54:11  31  FORALL i IN price_updates.FIRST .. price_updates.LAST
13:54:11  32  INSERT INTO price_event_log
13:54:11  33  VALUES(price_updates(i).price_log_id
13:54:11  34  , price_updates(i).price_id
13:54:11  35  , price_updates(i).created_by
13:54:11  36  , price_updates(i).creation_date
13:54:11  37  , price_updates(i).last_updated_by
13:54:11  38  , price_updates(i).last_update_date);
13:54:11  39  END AFTER STATEMENT;
13:54:11  40  END;
13:54:11  41  /

触发器已创建

已用时间:  00: 00: 00.53
--未更改行时,组合触发器不执行
13:54:12 SCOTT@orcl> UPDATE price
13:54:41   2     SET last_updated_by = NVL(TO_NUMBER(SYS_CONTEXT('userenv', 'client_info')),-1);

已更新0行。

已用时间:  00: 00: 00.07
13:54:42 SCOTT@orcl> SELECT * FROM price_event_log;

未选定行

已用时间:  00: 00: 00.01
13:54:52 SCOTT@orcl> insert into price select 1,11,sysdate from dual;

已创建 1 行。

已用时间:  00: 00: 00.25
13:55:42 SCOTT@orcl> commit;

提交完成。

已用时间:  00: 00: 00.00
13:55:46 SCOTT@orcl> UPDATE price
13:55:52   2     SET last_updated_by = NVL(TO_NUMBER(SYS_CONTEXT('userenv', 'client_info')),-1);

已更新 1 行。

已用时间:  00: 00: 00.03
--CREATED_BY及LAST_UPDATED_BY是由set_client_info这里设置的
13:55:54 SCOTT@orcl> SELECT * FROM price_event_log;

PRICE_LOG_ID   PRICE_ID CREATED_BY CREATION_DATE  LAST_UPDATED_BY LAST_UPDATE_DA
------------ ---------- ---------- -------------- --------------- --------------
           1          1          3 05-6月 -13                   3 05-6月 -13

已选择 1 行。

已用时间:  00: 00: 00.00

上面的例子捕获行级数据后保存到全局的集合变量里,然后在语句级块里(AFTER STATEMENT)重用集合变量.

Instead-of触发器(Instead-of Triggers)
你能使用Instead-of触发器来拦截INSERT,UPDATE,DELETE语句,并以任意的存储代码进行替换.
不可更新的视图一般都采用Instead-of触发器来进行更新.
原型:
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF {dml_statement }
ON {object_name | database | schema}
FOR EACH ROW
[WHEN (logical_expression)]
[DECLARE]
declaration_statements;
BEGIN
execution_statements;
END [trigger_name];

注意你只能建立一个INSTEAD OF的DML触发器在视图上.
示例:

15:17:09 SCOTT@orcl> create view emp_dept as
15:17:21   2  select a.empno
15:17:21   3  ,a.ename
15:17:21   4  ,b.deptno
15:17:21   5  ,b.loc
15:17:21   6  from emp_bak2013 a,dept_bak b
15:17:21   7  where a.deptno=b.deptno;

视图已创建。

已用时间:  00: 00: 00.04
15:17:22 SCOTT@orcl> update emp_dept set ename='err';
update emp_dept set ename='err'
                    *
第 1 行出现错误:
ORA-01779: 无法修改与非键值保存表对应的列


已用时间:  00: 00: 00.00
15:25:13 SCOTT@orcl> CREATE OR REPLACE TRIGGER emp_dept_update
15:30:33   2    INSTEAD OF UPDATE ON emp_dept
15:30:33   3  BEGIN
15:30:33   4    UPDATE emp_bak2013 SET ename = :new.ename WHERE empno=:old.empno;
15:30:33   5  END;
15:30:34   6  /

触发器已创建

已用时间:  00: 00: 00.01
15:30:35 SCOTT@orcl> update emp_dept set ename='err';

已更新14行。

已用时间:  00: 00: 00.01
15:31:21 SCOTT@orcl> select * from emp_dept;

     EMPNO ENAME          DEPTNO LOC
---------- ---------- ---------- -------------
      7369 err                20 DALLAS
      7499 err                30 CHICAGO
      7521 err                30 CHICAGO
      7566 err                20 DALLAS
      7654 err                30 CHICAGO
      7698 err                30 CHICAGO
      7782 err                10 NEW YORK
      7788 err                20 DALLAS
      7839 err                10 NEW YORK
      7844 err                30 CHICAGO
      7876 err                20 DALLAS
      7900 err                30 CHICAGO
      7902 err                20 DALLAS
      7934 err                10 NEW YORK

已选择14行。

已用时间:  00: 00: 00.04
15:31:29 SCOTT@orcl> ROLLBACK;

回退已完成。

已用时间:  00: 00: 00.03
15:34:09 SCOTT@orcl> select * from emp_dept;

     EMPNO ENAME          DEPTNO LOC
---------- ---------- ---------- -------------
      7369 smith              20 DALLAS
      7499 allen              30 CHICAGO
      7521 ward               30 CHICAGO
      7566 jones              20 DALLAS
      7654 martin             30 CHICAGO
      7698 blake              30 CHICAGO
      7782 clark              10 NEW YORK
      7788 scott              20 DALLAS
      7839 king               10 NEW YORK
      7844 turner             30 CHICAGO
      7876 adams              20 DALLAS
      7900 james              30 CHICAGO
      7902 ford               20 DALLAS
      7934 miller             10 NEW YORK

已选择14行。

已用时间:  00: 00: 00.03

INSERT、UPDATE、DELETE合在一起的触发器简单示例:
15:40:23 SCOTT@orcl> CREATE OR REPLACE TRIGGER empbak_dml
15:40:25   2    INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_dept
15:40:25   3    FOR EACH ROW
15:40:25   4  BEGIN
15:40:25   5    IF INSERTING THEN
15:40:25   6      dbms_output.put_line('INSERTING on view emp_dept');
15:40:25   7    ELSIF UPDATING THEN
15:40:25   8      dbms_output.put_line('UPDATING on view emp_dept');
15:40:25   9    ELSIF DELETING THEN
15:40:25  10      dbms_output.put_line('DELETING on view emp_dept');
15:40:25  11    END IF;
15:40:25  12  END;
15:40:26  13  /

触发器已创建

已用时间:  00: 00: 00.03
15:42:06 SCOTT@orcl> update emp_dept set ename='err' where ename='scott';
UPDATING on view emp_dept

已更新 1 行。

已用时间:  00: 00: 00.01
15:42:09 SCOTT@orcl> delete from  emp_dept where ename='scott';
DELETING on view emp_dept

已删除 1 行。

已用时间:  00: 00: 00.00
15:42:11 SCOTT@orcl> insert into emp_dept select 111,'111',55,'loc' from dual;
INSERTING on view emp_dept

已创建 1 行。

已用时间:  00: 00: 00.01
--由于INSTEAD OF触发器,所有的DML操作都屏蔽掉了
15:43:03 SCOTT@orcl> select * from emp_dept;

     EMPNO ENAME          DEPTNO LOC
---------- ---------- ---------- -------------
      7369 smith              20 DALLAS
      7499 allen              30 CHICAGO
      7521 ward               30 CHICAGO
      7566 jones              20 DALLAS
      7654 martin             30 CHICAGO
      7698 blake              30 CHICAGO
      7782 clark              10 NEW YORK
      7788 scott              20 DALLAS
      7839 king               10 NEW YORK
      7844 turner             30 CHICAGO
      7876 adams              20 DALLAS
      7900 james              30 CHICAGO
      7902 ford               20 DALLAS
      7934 miller             10 NEW YORK

已选择14行。

已用时间:  00: 00: 00.01

注:不可更新的视图是指:
1.有SET操作符
2.有聚合函数
3.有CASE或DECODE
4.有CONNECT BY,GROUP BY,HAVING,START WITH子句
5.有DISTINCT
6.表连接

系统或数据库事件触发器(System or Database Event Triggers)
系统触发器可以审计服务启动和关闭、服务器错误、用户登入和登出等活动.
原型:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} database_event ON {database | schema}
[DECLARE]
declaration_statements;
BEGIN
execution_statements;
END [trigger_name];

使用示例:

--创建连接日志表
20:55:48 SYS@orcl> CREATE TABLE connection_log
20:56:04   2  ( event_id NUMBER
20:56:04   3  , event_user_name VARCHAR2(30) CONSTRAINT log_event_nn1 NOT NULL
20:56:04   4  , event_type VARCHAR2(14) CONSTRAINT log_event_nn2 NOT NULL
20:56:04   5  , event_date DATE CONSTRAINT log_event_nn3 NOT NULL);

Table created.

Elapsed: 00:00:00.15
--创建用户连接包
20:56:05 SYS@orcl> CREATE OR REPLACE PACKAGE user_connection AS
20:56:12   2    PROCEDURE connecting(user_name IN VARCHAR2);
20:56:12   3    PROCEDURE disconnecting(user_name IN VARCHAR2);
20:56:12   4  END user_connection;
20:56:13   5  /

Package created.

Elapsed: 00:00:00.10
20:56:16 SYS@orcl> CREATE OR REPLACE PACKAGE BODY user_connection AS
20:56:37   2    PROCEDURE connecting(user_name IN VARCHAR2) IS
20:56:37   3    BEGIN
20:56:37   4      INSERT INTO connection_log
20:56:37   5        (event_user_name, event_type, event_date)
20:56:37   6      VALUES
20:56:37   7        (user_name, 'CONNECT', SYSDATE);
20:56:37   8    END connecting;
20:56:37   9    PROCEDURE disconnecting(user_name IN VARCHAR2) IS
20:56:37  10    BEGIN
20:56:37  11      INSERT INTO connection_log
20:56:37  12        (event_user_name, event_type, event_date)
20:56:37  13      VALUES
20:56:37  14        (user_name, 'DISCONNECT', SYSDATE);
20:56:37  15    END disconnecting;
20:56:37  16  END user_connection;
20:56:37  17  /

Package body created.

Elapsed: 00:00:00.21
20:56:37 SYS@orcl> CREATE OR REPLACE TRIGGER connecting_trigger
20:56:37   2    AFTER LOGON ON DATABASE
20:56:37   3  BEGIN
20:56:37   4    user_connection.connecting(sys.login_user);
20:56:37   5  END;
20:56:37   6  /

Trigger created.

Elapsed: 00:00:00.03
20:56:37 SYS@orcl> CREATE OR REPLACE TRIGGER disconnecting_trigger
20:56:37   2    BEFORE LOGOFF ON DATABASE
20:56:37   3  BEGIN
20:56:37   4    user_connection.disconnecting(sys.login_user);
20:56:37   5  END;
20:56:37   6  /

Trigger created.

Elapsed: 00:00:00.11
20:56:39 SYS@orcl> select * from connection_log;

no rows selected

Elapsed: 00:00:00.02
20:56:55 SYS@orcl> conn scott/tiger
Connected.
20:57:27 SCOTT@orcl> conn / as sysdba
Connected.
20:57:48 SYS@orcl> select * from connection_log;

  EVENT_ID EVENT_USER_NAME                EVENT_TYPE     EVENT_DATE
---------- ------------------------------ -------------- ------------
<null>     SYS                            DISCONNECT     04-JAN-13
<null>     SCOTT                          CONNECT        04-JAN-13
<null>     SCOTT                          DISCONNECT     04-JAN-13
<null>     SYS                            CONNECT        04-JAN-13

4 rows selected.

Elapsed: 00:00:00.02

注意如果user_connection包中存在错误,用户登入登出时是不会报错的.

触发器限制
1.最大长度,触发器主体不能超过32760字节.
2.非系统触发器,其主体不能包含DDL语句,也不能包含DCL语句或TCL语句.
使用自治事务,非系统触发器可以包含DCL语句.
验证:

21:16:33 SCOTT@orcl> CREATE OR REPLACE TRIGGER TRI_EMP
21:16:49   2    AFTER UPDATE  ON emp
21:16:49   3  BEGIN
21:16:49   4    grant select on emp to cry;--DCL 语句
21:16:49   5  END TRI_EMP;
21:16:51   6  /

Warning: Trigger created with compilation errors.

Elapsed: 00:00:00.07
21:16:52 SCOTT@orcl> show err
Errors for TRIGGER TRI_EMP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3      PLS-00103: Encountered the symbol "GRANT" when expecting one of
         the following:
         ( begin case declare exit for goto if loop mod null pragma
         raise return select update while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         continue close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge pipe purge

21:16:56 SCOTT@orcl> CREATE OR REPLACE TRIGGER TRI_EMP
21:17:19   2    AFTER UPDATE  ON emp
21:17:19   3  BEGIN
21:17:19   4    execute immediate 'grant select on emp to cry';--DCL 语句
21:17:19   5  END TRI_EMP;
21:17:20   6  /

Trigger created.

Elapsed: 00:00:00.03
21:17:21 SCOTT@orcl> update emp set ename='cc' where ename='scott';
update emp set ename='cc' where ename='scott'
       *
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SCOTT.TRI_EMP", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRI_EMP'


Elapsed: 00:00:00.01

3.当字段是LONG或LONG RAW时,行级触发器不能使用:new或:old
验证:

21:22:41 SCOTT@orcl> CREATE TABLE LONG_TAB(ID LONG);

Table created.

Elapsed: 00:00:00.07
21:24:39 SCOTT@orcl> CREATE OR REPLACE TRIGGER TRI_LONG_TAB
21:25:49   2    AFTER UPDATE  ON LONG_TAB
21:25:49   3    FOR EACH ROW
21:25:49   4  BEGIN
21:25:49   5    :NEW.ID='Im long col';
21:25:49   6  END TRI_LONG_TAB;
21:25:50   7  /
CREATE OR REPLACE TRIGGER TRI_LONG_TAB
                          *
ERROR at line 1:
ORA-04093: references to columns of type LONG are not allowed in triggers


Elapsed: 00:00:00.08

4.变异表(Mutating Tables)
变异表是一个正在改变的表.这种错误只发生在行级触发器中.
当表发生改变时,你不能查询或者修改它.
如果一个触发器因为表的改变而触发,你不能看到这个改变直到最后.但此时你可以访问new和old伪记录,
你不能读表的状态.任何尝试去读表都会获得ORA-04091错误.
如:

21:25:51 SCOTT@orcl> CREATE TABLE mutant
21:35:55   2  ( mutant_id NUMBER
21:35:55   3  , mutant_name VARCHAR2(20));

Table created.

Elapsed: 00:00:00.11
21:36:10 SCOTT@orcl> INSERT INTO mutant VALUES (1,'Leonardo');

1 row created.

Elapsed: 00:00:00.04
21:36:20 SCOTT@orcl> INSERT INTO mutant VALUES (2,'Leonardo');

1 row created.

Elapsed: 00:00:00.01
21:36:34 SCOTT@orcl> commit;

Commit complete.

Elapsed: 00:00:00.01
21:36:38 SCOTT@orcl> CREATE OR REPLACE TRIGGER TRI_mutator
21:36:45   2    AFTER DELETE ON mutant
21:36:45   3    FOR EACH ROW
21:36:45   4  DECLARE
21:36:45   5    rows NUMBER;
21:36:45   6  BEGIN
21:36:45   7    SELECT COUNT(*) INTO rows FROM mutant;--查询表数据,而此时表正发生改变
21:36:45   8    dbms_output.put_line('[rows] has ' || rows || ']');
21:36:45   9  END;
21:36:46  10  /

Trigger created.

Elapsed: 00:00:00.11
21:36:47 SCOTT@orcl> delete from mutant where mutant_id=1;
delete from mutant where mutant_id=1
            *
ERROR at line 1:
ORA-04091: table SCOTT.MUTANT is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRI_MUTATOR", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRI_MUTATOR'


Elapsed: 00:00:00.03

禁用触发器:
ALTER TRIGGER 触发器名 DISABLE;
编译触发器:
ALTER TRIGGER 触发器名 COMPILE;