What is the difference between ON SCHEMA and ON DATABASE triggers?

Question

We can create the database trigger on concrete schema event (ON SCOTT.SCHEMA) or on all schemas (ON SCHEMA). However, we can also use ON DATABASE when creating database trigger. What is the difference between them? Is it some legacy stuff? ON DATABASE should be used when using AFTER STARTUP or AFTER STARTUP because it's definitely related only to database but the same stuff that is done using ON SCHEMA might be done using ON DATABASE, so what's the difference? I can't find references in Oracle docs about that.

Answer 1

A trigger on schema (with no schema specified) doesn't fire for all schemas. It only fires when the triggering action is run by the user who owns that trigger.

So they are quite different, and are not interchangeable.

Here's an example that sets up a logging table, and three create triggers: on database, and on schema for users foo and bar:

Connected. -- as mat
SQL> create table mat.log (dt timestamp, who varchar(3),
  2      cur varchar(10), own varchar(42), obj varchar(42));
Table created.

SQL> create or replace trigger db_trig
  2  after create on database
  3  begin
  4    insert into mat.log values (systimestamp, 'db', user,
  5      ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME);
  6  end;
  7  /
Trigger created.

SQL> connect foo/foo
Connected.
SQL> create or replace trigger foo_trig
  2  after create on schema
  3  begin
  4    insert into mat.log values (systimestamp, 'foo', user,
  5      ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME);
  6  end;
  7  /
Trigger created.

SQL> connect bar/bar
Connected.
SQL> create or replace trigger bar_trig
  2  after create on schema
  3  begin
  4    insert into mat.log values (systimestamp, 'bar', user,
  5      ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME);
  6  end;
  7  /

Trigger created.

Now let's create a table in foo's schema, as foo:

SQL> connect foo/foo
Connected.
SQL> create table foo.foo_stuff (id number);
Table created.

And let's create a table in foo's schema, as bar:

SQL> connect bar/bar
Connected.
SQL> create table foo.bar_stuff (id number);
Table created.

Here's what we've logged:

SQL> select * from mat.log order by dt;

DT                             WHO CUR        OWN        OBJ
------------------------------ --- ---------- ---------- ---------------
25-NOV-12 07.52.03.797794 PM   db  FOO        FOO    FOO_TRIG
25-NOV-12 07.52.03.828670 PM   db  BAR        BAR    BAR_TRIG
25-NOV-12 07.52.03.865334 PM   foo FOO        FOO    FOO_STUFF
25-NOV-12 07.52.03.865579 PM   db  FOO        FOO    FOO_STUFF
25-NOV-12 07.52.03.894672 PM   bar BAR        FOO    BAR_STUFF
25-NOV-12 07.52.03.894911 PM   db  BAR        FOO    BAR_STUFF

6 rows selected.

So:

• the two create trigger statements were logged by the "global" after create on database trigger. That trigger also logged everything else.
• foo's after create on schema logged the table creation that was done by foo
• bar's trigger logged the table creation that was run by bar himself, even though bar created a table in foo's schema.