Oracle Crossedition Triggers
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Data Dictionary Objects
ALL_ERRORS_AE CDB_TRIGGERS TRIGGER$
ALL_OBJECTS_AE CDB_TRIGGER_ORDERING USER_ERRORS_AE
ALL_TRIGGERS DBA_OBJECTS_AE USER_OBJECTS_AE
ALL_TRIGGER_ORDERING DBA_ERRORS_AE USER_TRIGGERS
CDB_ERRORS_AE DBA_TRIGGERS USER_TRIGGER_ORDERING
CDB_OBJECTS_AE DBA_TRIGGER_ORDERING  
System Privileges
ALTER ANY TRIGGER CREATE TRIGGER DROP ANY TRIGGER
CREATE ANY TRIGGER    
 
CREATE
Create Forward Editioning Trigger CREATE OR REPLACE EDITIONABLE TRIGGER <trigger_name>
BEFORE <INSERT OR UPDATE OR DELETE>
ON <table_name>
FOR EACH ROW
<FORWARD CROSSEDITION [FOLLOWS <trigger_name>] |
 REVERSE CROSSEDITION [PRECEDES <trigger_name>]>
<ENABLE | DISABLE>
BEGIN
  <trigger_body_code>
END;
/
CREATE OR REPLACE TRIGGER Contacts_Fwd_Xed
BEFORE INSERT OR UPDATE ON Contacts_Table
FOR EACH ROW
FORWARD CROSSEDITION
DISABLE
BEGIN
  Set_First_And_Last_Name(:NEW.Name_1, :NEW.First_Name_2, :NEW.Last_Name_2);
  Set_Country_Code_And_Phone_No(:NEW.Phone_Number_1, :NEW.Country_Code_2, :NEW.Phone_Number_2);
END Contacts_Fwd_Xed;
/
Create Reverse Editioning Trigger CREATE OR REPLACE TRIGGER Contacts_Rvrs_Xed
BEFORE INSERT OR UPDATE ON Contacts_Table
FOR EACH ROW
REVERSE CROSSEDITION
DISABLE
BEGIN
  :NEW.Name_1 := :NEW.Last_Name_2||', '||:NEW.First_Name_2;
  :NEW.Phone_Number_1 :=
     CASE :New.Country_Code_2 WHEN '+1' THEN
       REPLACE(:NEW.Phone_Number_2, '-', '.')
     ELSE
       '011.'||LTRIM(:NEW.Country_Code_2, '+')||'.'|| REPLACE(:NEW.Phone_Number_2, '-', '.')
     END;
END Contacts_Rvrs_Xed;
/
FOLLOWS CLAUSE CREATE OR REPLACE TRIGGER follows_test
BEFORE INSERT OR UPDATE ON person_tab
FOR EACH ROW
FORWARD CROSSEDITION
FOLLOWS ebradmin.person_fwd_xed

ENABLE
BEGIN
  dbms_output.put_line('FOLLOWS_TEST');
END person_fwd_xed;
/
PRECEDES CLAUSE CREATE OR REPLACE TRIGGER precedes_test
BEFORE INSERT OR UPDATE ON person_tab
FOR EACH ROW
REVERSE CROSSEDITION
PRECEDES ebradmin.person_rev_xed

ENABLE
BEGIN
  dbms_output.put_line('PRECEDES_TEST');
END person_rev_xed;
/
 
ALTER
Alter Editioning Trigger Enable or Disable ALTER TRIGGER <trigger_name> <ENABLE | DISABLE>;
SELECT trigger_name, status
FROM user_triggers;

ALTER TRIGGER Contacts_Fwd_Xed DISABLE;

SELECT trigger_name, status
FROM user_triggers;

ALTER TRIGGER Contacts_Fwd_Xed ENABLE;

SELECT trigger_name, status
FROM user_triggers;
 
DROP
Drop Editioning Trigger DROP TRIGGER <trigger_name>;
DROP TRIGGER Contacts_Fwd_Xed;

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_EDITIONS_UTILITIES
DDL Event Triggers
Editions
Editioning Demo 1
Editioning Demo 2
Editioning Demo 3
Editioning Demo 4
Editioning Demo 5
Editioning Demo 6
Editioning Demo 7
Editioning Views
Instead Of Triggers
SYS_CONTEXT Function
System Event Triggers
Table Triggers
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx