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;