Oracle之Triggers管理_20091229

Oracle之Triggers管理

参考文档<>P567

目录

1   Designing Triggers

2   Creating Triggers

2.1 可用的选项

2.2 创建触发器

2.3 触发器与Import , SQL*Loader的关系

2.4 AFTER and BEFORE 触发器的细微差别

2.5 When Clause

3   Modifying Triggers

3.1 Compiling Triggers

3.2 Enabling Triggers

3.3 Disable Triggers

4   View Information about Triggers

5   Notes

 

1          Designing Triggers

 

Some guidelines:

1)     Limit the size of triggers.  PL/SQL代码的行数不能超过60行.如超过,用存储过程代替.

2)     Use triggers only for centralized, global operations that should be fire for the triggering statement, regardless of which users or database application issues the statement.

3)     Don’t create recursive triggers.

4)     Use triggers on DATABASE judiciously.

 

2          Creating Triggers

 

When using an interactive tool like (SQL*PLUS) to create triggers, a single slash (/) on the last line is necessary to activate the CREATE TRIGGER statement.(记得加 / 号)

 

2.1  可用的选项

 

The FOR EACH ROW option determines whether the trigger is a row trigger or a statement trigger.

 

The REFERENCING option can be specified in a trigger body of a row trigger to avoid name conflicts among the correlation names and tables that might be named old or new. Because this is rare, this option is infrequently used.

 

2.2    创建触发器

 

Before行触发器

 

CREATE OR REPLACE TRIGGER Print_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON employee

FOR EACH ROW

WHEN (new.id > 0)

DECLARE

sal_diff number;

BEGIN

sal_diff := :new.sal - :old.sal;

dbms_output.put('Old salary: ' || :old.sal);

dbms_output.put(' New salary: ' || :new.sal);

dbms_output.put_line(' Difference ' || sal_diff);

END;

/

Trigger created.

 

 

LOGON事件 DATABASE 触发器

 

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)

/

 

REFERENCING option 的使用避免命名冲突

 

CREATE OR REPLACE TRIGGER Print_salary_changes

BEFORE UPDATE ON new

REFERENCING new AS Newest

FOR EACH ROW

BEGIN

:Newest.Field2 := TO_CHAR (:newest.field1);

END;

/

 

Instead of Trigger

 

CREATE OR REPLACE VIEW manager_info

AS select * from employee;

 

CREATE OR REPLACE TRIGGER manager_info_insert

     INSTEAD OF INSERT ON manager_info

     REFERENCING NEW AS n -- new manager information

     FOR EACH ROW

DECLARE

     xxx 

END;

/

 

2.3 触发器与Import , SQL*Loader的关系

 

INSERT triggers fire during SQL*Loader conventional loads.(For direct loads, triggers are disabled before the load.).

 The IGNORE parameter of the IMP command determines whether triggers fire during import operations. (IGNORE=N 不引发触发器 IGNORE=Y  引发触发器)

 

2.4 AFTER and BEFORE 触发器的细微差别

 

AFTER row triggers are slightly more efficient than BEFORE row triggers. With BEFORE row triggers, affected data blocks must be read (logical read, not physical read) once for the trigger and then again for the triggering statement. Alternatively, with AFTER row triggers, the data blocks must be read only once for both the triggering statement and the trigger.(在访问表的次数上有差别)

 

2.5    When Clause

A WHEN clause cannot be included in the definition of a STATEMENT trigger and INSTEAD OF trigger. (语句触发器和Instead of触发器都不能包含WHEN clause).

 

3   Modifying Triggers

Like a stored procedure, a trigger cannot be explicitly altered: It must be replaced with a new definition. (The ALTER TRIGGER statement is used only to recompile, enable, or disable a trigger.)

 

3.1  Compiling Triggers

 

ALTER TRIGGER Print_salary_changes COMPILE;

 

3.2    Enabling Triggers

 

单个:

Alter trigger reorder ENABLE.

 

表上所有的

ALTER TABLE inventory ENABLE ALL TRIGGERS;

 

3.3    Disable Triggers

 

单个:

Alter trigger reorder DISABLE.

 

表上所有的

ALTER TABLE inventory DISABLE ALL TRIGGERS;

 

 

4          View Information about Triggers

 

SELECT Trigger_type, Triggering_event, Table_name

FROM USER_TRIGGERS  WHERE Trigger_name = 'PRINT_SALARY_CHANGES';

 

TRIGGER_TYPE

----------------

TRIGGERING_EVENT

----------------------------------------------------------------------------------------------------

TABLE_NAME

------------------------------

BEFORE EACH ROW

INSERT OR UPDATE OR DELETE

EMPLOYEE

 

5   Notes

1 A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERTS statement, or a table that might be updated by the effects of a DELETE CASCADE constraint

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10248702/viewspace-623919/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10248702/viewspace-623919/