в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:06:41Z

1 версия

Новая страница

== AFTER CREATE ON SCHEMA==



<source lang="sql">
SQL>
SQL> CREATE TABLE myAudit(
2 object_name VARCHAR2(30)NOT NULL,
3 Object_type VARCHAR2(30)NOT NULL,
4 WHEN_CREATED DATE NOT NULL,
5 WHO_CREATED VARCHAR2(30)NOT NULL,
6 WHEN_UPDATED DATE,
7 WHO_UPDATED VARCHAR2(30)
8 );
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER after_ddl_creation
2 AFTER CREATE ON SCHEMA
3 BEGIN
4 INSERT INTO myAudit VALUES
5 (SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_TYPE,SYSDATE,USER,NULL,NULL);
6 END;
7 /
Trigger created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE p_dummy
2 IS
3 BEGIN
4 NULL;
5 END;
6 /
Procedure created.
SQL>
SQL> SELECT * FROM myAudit;
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
WHEN_CREAT WHO_CREATED WHEN_UPDAT
---------- ------------------------------ ----------
WHO_UPDATED
------------------------------
P_DUMMY PROCEDURE
24-07-2008 sqle null
null

1 row selected.
SQL>
SQL>
SQL> drop table myAudit;
Table dropped.
SQL> drop trigger after_ddl_creation;
Trigger dropped.
SQL>
SQL></source>

== AFTER DDL ON SCHEMA==



<source lang="sql">
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER no_create
2 AFTER DDL ON SCHEMA
3 BEGIN
4 IF ORA_SYSEVENT = "CREATE" THEN
5 RAISE_APPLICATION_ERROR(-20000,"Cannot create the " || ORA_DICT_OBJ_TYPE ||
6 " named " || ORA_DICT_OBJ_NAME ||
7 " as requested by " || ORA_DICT_OBJ_OWNER);
8 END IF;
9 END;
10 /
Trigger created.
SQL>
SQL>
SQL> drop trigger no_create;
Trigger dropped.</source>

== AFTER SUSPEND ON SCHEMA==



<source lang="sql">
SQL>
SQL> CREATE OR REPLACE TRIGGER after_suspend
2 AFTER SUSPEND ON SCHEMA
3 DECLARE
4 CURSOR curs_get_extents IS
5 SELECT max_extents + 1
6 FROM user_tables
7 WHERE table_name = "MONTHLY_SUMMARY";
8 v_new_max NUMBER;
9 BEGIN
10 OPEN curs_get_extents;
11 FETCH curs_get_extents INTO v_new_max;
12 CLOSE curs_get_extents;
13 EXECUTE IMMEDIATE "ALTER TABLE MONTHLY_SUMMARY " ||
14 "STORAGE ( MAXEXTENTS " ||
15 v_new_max || ")";
16 DBMS_OUTPUT.PUT_LINE("Incremented MAXEXTENTS to " || v_new_max);
17 END;
18 /
Trigger created.
SQL> SHO ERR
No errors.
SQL>
SQL>
SQL>
SQL> drop trigger after_suspend;
Trigger dropped.
SQL></source>

== ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME==



<source lang="sql">
SQL>
SQL> CREATE OR REPLACE TRIGGER what_column
2 AFTER ALTER ON SCHEMA
3 DECLARE
4 -- cursor to get columns in a table
5 CURSOR curs_get_columns ( cp_owner VARCHAR2,
6 cp_table VARCHAR2 ) IS
7 SELECT column_name
8 FROM all_tab_columns
9 WHERE owner = cp_owner
10 AND table_name = cp_table;
11
12 BEGIN
13
14 IF ORA_DICT_OBJ_TYPE = "TABLE" THEN
15
16 FOR v_column_rec IN curs_get_columns(ORA_DICT_OBJ_OWNER,
17 ORA_DICT_OBJ_NAME) LOOP
18
19 IF ORA_IS_ALTER_COLUMN(v_column_rec.column_name) THEN
20
21 DBMS_OUTPUT.PUT_LINE(ORA_DICT_OBJ_OWNER || "." ||
22 ORA_DICT_OBJ_NAME || "." ||
23 v_column_rec.column_name);
24
25 END IF;
26
27 END LOOP;
28
29 END IF;
30
31 END;
32 /
Trigger created.</source>

== Schema trigger==



<source lang="sql">
SQL> CREATE TABLE ALTER_AUDIT_TRAIL (
2 object_owner VARCHAR2(30),
3 object_name VARCHAR2(30),
4 object_type VARCHAR2(20),
5 altered_by_user VARCHAR2(30),
6 alteration_time DATE
7 );
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER audit_schema_changes
2 AFTER ALTER ON jeff.SCHEMA
3 BEGIN
4 INSERT INTO alter_audit_trail
5 (object_owner,
6 object_name,
7 object_type,
8 altered_by_user,
9 alteration_time
10 )
11 VALUES (sys.dictionary_obj_owner,
12 sys.dictionary_obj_name,
13 sys.dictionary_obj_type,
14 sys.login_user,
15 sysdate);
16 END;
17 /
SQL> Drop TABLE ALTER_AUDIT_TRAIL;
Table dropped.
SQL></source>

== use event attributes to provide more info==



<source lang="sql">
SQL>
SQL> CREATE OR REPLACE TRIGGER town_crier
2 AFTER CREATE ON SCHEMA
3 BEGIN
4
5 DBMS_OUTPUT.PUT_LINE("I believe you have created a " ||
6 ORA_DICT_OBJ_TYPE || " called " ||
7 ORA_DICT_OBJ_NAME);
8 END;
9 /
Trigger created.
SQL>
SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE a_table
2 (col1 NUMBER);
I believe you have created a TABLE called A_TABLE
Table created.
SQL>
SQL>
SQL> CREATE INDEX an_index ON a_table(col1);
I believe you have created a INDEX called AN_INDEX
Index created.
SQL>
SQL> drop index an_index;
Index dropped.
SQL>
SQL>
SQL> CREATE FUNCTION a_function RETURN BOOLEAN AS
2 BEGIN
3 RETURN(TRUE);
4 END;
5 /
I believe you have created a FUNCTION called A_FUNCTION
Function created.
SQL>
SQL> DROP FUNCTION a_function;
Function dropped.
SQL>
SQL> EXEC DBMS_OUTPUT.PUT_LINE(CHR(10));

PL/SQL procedure successfully completed.
SQL>
SQL> drop trigger town_crier;
Trigger dropped.
SQL>
SQL> DROP TABLE a_table;
Table dropped.</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Trigger/Schema Trigger - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия