Oracle9i笔记2(4)

LOGON and LOGOFF Trigger example

1. CREATE OR REPLACE TRIGGER logon_trig AFTER LOGON ON SCHEMA BEGIN

INSERT INTO log_trig_table(user_id,log_date,action) VALUES(USER,SYSDATE,’logging on ’); End;

2. CREATE OR REPLACE TRIGGER logoff_trig

BEFORE LOGOFF ON SCHEMA

BEGIN

INSERT INTO log_trig_table(user_id,log_date,action) VALUES (USER,SYSDATE,’Logging off’); End;

Call statement

Trigger 中的body部分可以調用內部或外部過程。 Implementating triggers You can use trigger for: ? Security ? Auditing

? Data integrity ? Referential integrity ? Table replication

? Computing derived data automativally ?

Event logging

CREATE SNAPSHOT emp_copy AS SELECT * FROM emp@ny;

導出數據：

Computing drived data within the server

Update

dept

set

total_sal=(select

sum(salary)

from

emp.deportno=dept.dept_no)

Benefits of database triggers

? Improved data security:

? Provide enhanced and complex security checks ? Provide enhanced and complex auditing ? Improved data integrity:

? Enforce dynamic data integrity constraints

? Enforce complex referential integrity constraints

? Ensure that related operations are performed together implicitly.

emp

where

Summary

In this lesson, you should have learned how to: ? Use advanced database triggers(還沒用過) ? List mutating and constraining rules for triggers

列出表的突然變化和限制規則對觸發器的影響（還不是很清楚） ? Describe the real-world application of triggers（還不清楚） ? Manage triggers ? View trigger information

Chapter 18 managing dependencies

After completing this lesson, you should be able to do the following: ? Track procedural dependencies. 了解過程的關系

? Predict the effect of changing a database object upon stored procedures and functions

預見過程或函數改變后產生的后果。 ? * manage procedural dependencies 管理過程的關系。

This lesson introduces you to object dependencies and implicit and explicit recompilation of invalid objects.

Recompiling a PL/SQL Program Unit 重新編譯過程，函數，包及觸發器。

ALTER PROCEDURE [SCHEMA.] procedure_name COMPILE; ALTER FUNCTION [SCHEMA.] function_name COMPILE

ALTER PACKAGE [SCHEMA.] package_name COMPILE[PACKAGE]; ALTER PACKAGE[SCHEMA.] package_name COMPILE BODY; ALTER TRIGGER trigger_name [COMPILE[DEBUG]];

? Using DEPTEREE_FILL and IDEPTREE to view dependencies ? Recompiling procedures, functions, and packages

APPENDIX

REF Cursors

? Cursor variables are like C or Pascal pointers, which hold the memory location(address) of an

item instead of the item itself

? In PL/SQL, a pointer is declared as REF X, where REF is short for REFERENCE and X stands for a class of objects.

? A cursor variable has the data type REF CURSOR ? A cursor is static, but a cursor variable is dynamic ? Cursor variables give you more flexibility.

Defining REF CURSOR Types ? Define a REF CURSOR type

TYPE ref_type_name IS REF CURSOR [RETURN return_type]; ? Declare a cursor variable of that type Ref_cv ref_type_name; ? Example

DECLARE

TYPE deptcurtyp IS REF CURSOR RETURN departments%type Dept_cv deptcurtyp; 例：(open… for…) Begin

Open dept_cv FOR ‘SQLSTR’ [USING]…; LOOP

FETCH dept_cv INTO 行變量 END LOOP; END;

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库Oracle9i笔记2(4)在线全文阅读。