Multitenant : Database Triggers on Pluggable Databases (PDBs) in Oracle 12c

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.

◉ 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 or SCHEMA level.

- AFTER LOGON : Trigger fires when a client logs into the CDB or PDB. Available at [PLUGGABLE] DATABASE or SCHEMA level.

- BEFORE LOGOFF : Trigger fires when a client logs out of the CDB or PDB. Available at [PLUGGABLE] DATABASE or SCHEMA level.

- AFTER SUSPEND : Trigger fires when a server error causes a transaction to be suspended. Available at [PLUGGABLE] DATABASE or SCHEMA level.

- BEFORE SET CONTAINER : Trigger fires before the SET CONTAINER command executes. Available at [PLUGGABLE] DATABASE or SCHEMA level.

- AFTER SET CONTAINER : Trigger fires after the SET CONTAINER command executes. Available at [PLUGGABLE] DATABASE or SCHEMA 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.