SQL & PL/SQL

Hi all,

I am using Oracle 11g on windows 2003 server R2 standard edition service pack 2. I am trying to create a trigger within a schema called "inventory" by logging in as the schema owner. Here is my code for the same:

 create or replace TRIGGER AUDIT_SYS_TR_TEST
   AFTER  ALTER OR DROP ON DATABASE
  BEGIN
    IF(ora_sysevent = 'DROP' AND UPPER(ora_login_user) not in ('SYS','SYSTEM')) THEN
     DBMS_OUTPUT.PUT_LINE(ora_sysevent);
    ELSIF(ora_sysevent = 'ALTER' AND UPPER(ora_login_user) not in ('SYS','SYSTEM')) THEN
     DBMS_OUTPUT.PUT_LINE(ora_sysevent);
    END IF;
  END; 

The aim is to fire the trigger whenever alter or drop occurs on a database object(ie any database object). So I created a table called trigtest like this within the same schema as the trigger:

CREATE TABLE TRIGTEST(COL1 VARCHAR(20));

Then I tried dropping the table:

DROP TABLE TRIGTEST;

This is what I got as serveroutput after the drop statement :

ALTER
DROP

Can someone please throw some light as to why both the alter and drop events are being fired when only the drop statement is called?