Oracle PL/SQL Tutorial/Trigger/Schema Trigger

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>