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.
The difference between ON SCHEMA and ON DATABASE triggers
oracletrigger
Related Question
- Why I don’t need to COMMIT in database trigger
- Postgresql – Inherit audit columns and triggers
- Difference between full and level 0 rman backups
- Oracle – Permissions for Cross-Schema Materialized View
- Oracle Security – Separate Database vs Separate Schema
- SQL Server – Difference Between ALTER TABLE DISABLE TRIGGER and DISABLE TRIGGER
Best Answer
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
, andon schema
for usersfoo
andbar
:Now let's create a table in
foo
's schema, asfoo
:And let's create a table in
foo
's schema, asbar
:Here's what we've logged:
So:
create trigger
statements were logged by the "global"after create on database
trigger. That trigger also logged everything else.foo
'safter create on schema
logged the table creation that was done byfoo
bar
's trigger logged the table creation that was run bybar
himself, even thoughbar
created a table infoo
's schema.