в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:58:26Z

1 версия

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

==log drop trigger on table schema==



<source lang="sql">

SQL>
SQL>
SQL> create table dropped_objects(
2 object_name varchar2(30),
3 object_type varchar2(30),
4 dropped_on date );
Table created.
SQL>
SQL> create or replace
2 trigger log_drop_trigger
3 before drop
4 on scott.schema
5 begin
6 insert into dropped_objects
7 values ( ora_dict_obj_name,
8 ora_dict_obj_type,
9 sysdate );
10 end;
11 /
SQL>
SQL> create table drop_me(x number );
Table created.
SQL>
SQL> create view drop_me_view as select * from drop_me;
View created.
SQL>
SQL> drop view drop_me_view;
View dropped.
SQL>
SQL> drop table drop_me;
Table dropped.
SQL>
SQL> select * from dropped_objects;
no rows selected
SQL>
SQL> drop table dropped_objects;
Table dropped.
SQL>
SQL>
</source>

==Logon schema logon to a table==



<source lang="sql">

SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER LogUserAConnects
2 AFTER LOGON ON SCHEMA
3 BEGIN
4 INSERT INTO MyTable
5 VALUES (1, "LogUserAConnects fired!");
6 END LogUserAConnects;
7 /
Trigger created.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
</source>

==Trigger on schema creation==



<source lang="sql">

SQL>
SQL> CREATE OR REPLACE TRIGGER town_crier
2 AFTER CREATE ON SCHEMA
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE("I believe you have created something!");
5 END;
6 /
Trigger created.
SQL>
SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE a_table
2 (col1 NUMBER);
I believe you have created something!
Table created.
SQL>
SQL> DROP TABLE a_table;
Table dropped.
SQL>
</source>

==trigger to log the schema altercation==



<source lang="sql">

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>
SQL> CREATE OR REPLACE TRIGGER audit_schema_changes
2 AFTER ALTER ON 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 /
Trigger created.
SQL>
SQL> drop table ALTER_AUDIT_TRAIL;
Table dropped.
SQL>
SQL> drop trigger AUDIT_SCHEMA_CHANGES;
Trigger dropped.
SQL> --
</source>

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

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

в 13:45, 26 мая 2010

Admin: 1 версия