home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  


6.3 Schema-Level Event Triggers

Oracle8 i also allows you to define triggers to respond to schema-level events (also known as user-level events), including the following:

  • Logon to and logoff from a schema

  • Response to a server error

  • CREATE, DROP, and ALTER DDL commands

Good news! We are finally able to place triggers on this broad set of DDL! We can keep track of any attempts to drop tables (successful or otherwise), can notify DBAs of changes to tables or types, and so on.

Here is the syntax for these triggers:

CREATE [ OR REPLACE ] TRIGGER 
trigger_name

   { BEFORE | AFTER }
   { SERVERERROR |
     LOGON | LOGOFF | 
     CREATE | DROP | ALTER }
   ON 
schema_name
.SCHEMA
BEGIN

   pl/sql_statements

END;
schema_name

The name of the schema in which the trigger will fire

pl/sql_statements

The PL/SQL block

trigger_name

The name of the trigger being created

You must have the CREATE ANY TRIGGER system privilege to create a trigger in any schema, on a table in any schema, or on another user's schema ( schema _ name .SCHEMA in the syntax just given). You will also need privileges to access any of the external references in the trigger's PL/SQL code.

You can define triggers for the same event (such as SERVERERROR) on both the schema and database levels. In this case, the schema-level trigger will fire before the database level, but both will fire.

Table 6.4 describes the different schema-level events on which you may define a trigger.


Table 6.4: Schema-Level Events for Trigger Definitions

Event

Description

SERVERERROR

Oracle fires the trigger whenever a server error message is logged.

LOGON

Oracle fires the trigger after a client application logs on to the database successfully.

LOGOFF

Oracle fires the trigger before a client application logs off the database.

CREATE

Oracle fires the trigger whenever a CREATE statement adds a new database object to the schema.

DROP

Oracle fires the trigger whenever a DROP statement removes an existing database object from the schema.

ALTER

Oracle fires the trigger whenever an ALTER statement modifies an existing database object in the schema.

Each user event has an associated set of attributes. These attributes are actually functions owned by SYS that return the values of characteristics relevant to the event. The current set of available attributes includes those listed in the previous section on database-level events (see Table 6.2 ), plus those listed in Table 6.5 .


Table 6.5: Additional Attributes for Schema-Level Events

Name

Datatype

Description

DICTIONARY_OBJ_OWNER

VARCHAR2(30)

Owner of the dictionary object on which the DDL operation occurred

DICTIONARY_OBJ_NAME

VARCHAR2(30)

Name of the dictionary object on which the DDL operation occurred

DICTIONARY_OBJ_TYPE

VARCHAR2(30)

Type of the dictionary object on which the DDL operation occurred

DES_ENCRYPTED_PASSWORD

VARCHAR2(30)

DES-encrypted password of the user being created or altered

Table 6.6 lists the restrictions and attributes for each schema-level event.


Table 6.6: Restrictions and Attributes for Schema-Level Events

Event

Conditions/Restrictions

Attributes

LOGON

You can specify a condition using either USERID( ) or USERNAME( ).

SYSEVENT

LOGIN_USER

INSTANCE_NUM

DATABASE_NAME

LOGOFF

You can specify a condition using either USERID( ) or USERNAME( ).

SYSEVENT

LOGIN_USER

INSTANCE_NUM

DATABASE_NAME

BEFORE CREATE

AFTER CREATE

Inside either of these triggers, you cannot drop the object being created. The trigger executes in the current transaction.

SYSEVENT

LOGIN_USER

INSTANCE_NUM

DATABASE_NAME

DICTIONARY_OBJ_TYPE

DICTIONARY_OBJ_NAME

DICTIONARY_OBJ_OWNER

BEFORE ALTER

AFTER ALTER

Inside either of these triggers, you cannot drop the object being altered. The trigger executes in the current transaction.

SYSEVENT

LOGIN_USER

INSTANCE_NUM

DATABASE_NAME

DICTIONARY_OBJ_TYPE

DICTIONARY_OBJ_NAME

DICTIONARY_OBJ_OWNER

BEFORE DROP

AFTER DROP

Inside either of these triggers, you cannot alter the object being dropped. The trigger executes in the current transaction.

SYSEVENT

LOGIN_USER

INSTANCE_NUM

DATABASE_NAME

DICTIONARY_OBJ_TYPE

DICTIONARY_OBJ_NAME

DICTIONARY_OBJ_OWNER

6.3.1 A Schema-Level Event Trigger Example

Suppose that I want to make sure that even if a user is able to connect to a schema, she or he will not be able to drop tables from that schema. Without these DDL triggers, that would be impossible because, if I can connect, I "own" everything and can do with them what I want.

With the DROP trigger, however, I can add this extra level of security. The following trigger asserts the following rule: you cannot drop any tables starting with "EMP" in the SCOTT schema:

/* Filename on companion disk: stopdrop.trg */
CREATE OR REPLACE TRIGGER no_drop_trg 
  BEFORE DROP ON SCOTT.SCHEMA 
DECLARE
   v_msg VARCHAR2(1000) :=
      'No drop allowed on ' ||
       DICTIONARY_OBJ_OWNER || '.' ||
       DICTIONARY_OBJ_NAME || ' from ' ||
       LOGIN_USER;  
BEGIN
  IF DICTIONARY_OBJ_OWNER = 'SCOTT' AND
     DICTIONARY_OBJ_NAME LIKE 'EMP%' AND
     DICTIONARY_OBJ_TYPE = 'TABLE'
  THEN
     watch.topipe;
     watch.action (
        'BEFORE DROP trigger',  v_msg);
     RAISE_APPLICATION_ERROR (
        -20905, v_msg);
  END IF;
END;
/

I rely on the special attributes available to me in this trigger -- namely, the functions returning values for DICTIONARY_OBJ_OWNER, DICTIONARY_OBJ_NAME, and DICTIONARY_OBJ_TABLE, to indicate whether the specified table being dropped is out of bounds. If so, I send a message to my watch pipe and then stop the drop request by raising an exception.

Here's what happens when I attempt to drop a now-undroppable table:

SQL> drop table emp2;
drop table emp2
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20905: No drop allowed on SCOTT.EMP2 from SCOTT


Previous: 6.2 Database-Level Event Triggers Oracle PL/SQL Programming Guide to Oracle 8i Features Next: 7. New and Enhanced Built-in Packages in Oracle8i
6.2 Database-Level Event Triggers Book Index 7. New and Enhanced Built-in Packages in Oracle8i

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference