8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multitenant : Database Triggers on Pluggable Databases (PDBs) in Oracle 12c Release 1 (12.1)
With the introduction of the multitenant option, database event triggers can be created in the scope of the CDB or PDB. Some extra trigger events are also available.
Related articles.
- Database Triggers Overview
- Mutating Table Exceptions
- Trigger Enhancements in Oracle Database 11g Release 1
- Cross-Edition Triggers: Edition-Based Redefinition in Oracle Database 11g Release 2
- Multitenant : All Articles
Trigger Scope
To create a trigger on a database event in a CDB requires a connection to the CDB as a common user with the ADMINISTER DATABASE TRIGGER
system privilege.
CONN sys@cdb1 AS SYSDBA CREATE OR REPLACE TRIGGER cdb1_after_startup_trg AFTER STARTUP ON DATABASE BEGIN -- Do something. NULL; END; /
To create a trigger on a database event in a PDB requires a connection to the PDB as either a common or local user with the ADMINISTER DATABASE TRIGGER
system privilege in the context of the PDB. The ON DATABASE
and ON PLUGGABLE DATABASE
clauses are functionally equivalent within the PDB, but some events require the ON PLUGGABLE DATABASE
clause explicitly.
CONN sys@pdb1 AS SYSDBA CREATE OR REPLACE TRIGGER pdb1_after_startup_trg AFTER STARTUP ON PLUGGABLE DATABASE BEGIN -- Do something. NULL; END; / CREATE OR REPLACE TRIGGER pdb1_after_startup_trg AFTER STARTUP ON DATABASE BEGIN -- Do something. NULL; END; /
Some database event triggers are also available at schema level within the CDB or PDB. Functionally, these are unchanged by the multitenant option.
CONN sys@cdb1 AS SYSDBA CREATE OR REPLACE TRIGGER cdb1_after_logon_trg AFTER LOGON ON flows_files.SCHEMA BEGIN -- Do something. NULL; END; / CONN sys@pdb1 AS SYSDBA CREATE OR REPLACE TRIGGER cdb1_after_logon_trg AFTER LOGON ON test.SCHEMA BEGIN -- Do something. NULL; END; /
Event Availability
The following database events are available at both the CDB and PDB level.
AFTER STARTUP
: Trigger fires after the CDB or PDB opens.BEFORE SHUTDOWN
: Trigger fires before the CDB shuts down or before the PDB closes.AFTER SERVERERROR
: Trigger fires when a server error message is logged and it is safe to fire error triggers. Available at[PLUGGABLE] DATABASE
orSCHEMA
level.AFTER LOGON
: Trigger fires when a client logs into the CDB or PDB. Available at[PLUGGABLE] DATABASE
orSCHEMA
level.BEFORE LOGOFF
: Trigger fires when a client logs out of the CDB or PDB. Available at[PLUGGABLE] DATABASE
orSCHEMA
level.AFTER SUSPEND
: Trigger fires when a server error causes a transaction to be suspended. Available at[PLUGGABLE] DATABASE
orSCHEMA
level.BEFORE SET CONTAINER
: Trigger fires before theSET CONTAINER
command executes. Available at[PLUGGABLE] DATABASE
orSCHEMA
level.AFTER SET CONTAINER
: Trigger fires after theSET CONTAINER
command executes. Available at[PLUGGABLE] DATABASE
orSCHEMA
level.
The following database event is only available at the CDB level.
AFTER DB_ROLE_CHANGE
: Fires when the database role switches from primary to standby or from standby to primary in a Data Guard configuration.
The following database events are only available at the PDB level and require the ON PLUGGABLE DATABASE
clause explicitly. Using the ON DATABASE
clause results in an error.
AFTER CLONE
: After a clone operation, the trigger fires in the new PDB and then the trigger is deleted. If the trigger fails, the clone operation fails.BEFORE UNPLUG
: Before an unplug operation, the trigger fires in the PDB and then the trigger is deleted. If the trigger fails, the unplug operation fails.
For more information see:
- CREATE TRIGGER Statement
- Database Triggers Overview
- Mutating Table Exceptions
- Trigger Enhancements in Oracle Database 11g Release 1
- Cross-Edition Triggers: Edition-Based Redefinition in Oracle Database 11g Release 2
- Multitenant : All Articles
Hope this helps. Regards Tim...