8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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.

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.

The following database event is only available at the CDB level.

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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.