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

Oracle之Triggers管理_20091229

相關文章