To create (or replace) a DDL trigger, use the syntax shown here:
1 CREATE [OR REPLACE] TRIGGER trigger name 2 {BEFORE | AFTER| { DDL event } ON {DATABASE | SCHEMA} 3 DECLARE 4 Variable declarations 5 BEGIN 6... some code ... 7 END;The following table summarizes what is happening in this code:
| Line(s) | Description |
| Specifies that a trigger is to be created with the name supplied. Specifying OR REPLACE is optional. If the trigger exists and REPLACE is not specified, then good old Oracle error 4081 will appear stating just that. | |
| This line has a lot to say. It defines whether the trigger will fire before or after the particular DDL event as well as whether it will fire for all operations within the database or just within the current schema. | |
| 3-7 | These lines simply demonstrate the PL/SQL contents of the trigger. |
Here's an example of a somewhat uninformed town crier trigger that announces the creation of all objects:
/* File on web: uninformed_town_crier.sql */SQL> CREATE OR REPLACE TRIGGER town_crier 2 AFTER CREATE ON SCHEMA 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('I believe you have created something!'); 5 END; 6 / Trigger created. SQL> SET SERVEROUTPUT ON SQL> CREATE TABLE a_table 2 (col1 NUMBER); Table created. SQL> CREATE INDEX an_index ON a_table(col1); Index created. SQL> CREATE FUNCTION a_function RETURN BOOLEAN AS 2 BEGIN 3 RETURN(TRUE); 4 END; 5 / Function created. SQL> /*-- flush the DBMS_OUTPUT buffer */ SQL> BEGIN NULL; END; 2 / I believe you have created something!I believe you have created something!I believe you have created something! PL/SQL procedure successfully completed.
|
Over time, this town crier would be ignored due to a lack of information, always proudly announcing that something had been created but never providing any details. Thankfully, there is a lot more information available to DDL triggers, allowing for a much more nuanced treatment, as shown in this version:
/* File on web: informed_town_crier.sql */SQL> CREATE OR REPLACE TRIGGER town_crier 2 AFTER CREATE ON SCHEMA 3 BEGIN 4 -- use event attributes to provide more info 5 DBMS_OUTPUT.PUT_LINE('I believe you have created a ' || 6 ORA_DICT_OBJ_TYPE || ' called ' || 7 ORA_DICT_OBJ_NAME); 8 END; 9 / Trigger created. SQL> SET SERVEROUTPUT ON SQL> CREATE TABLE a_table 2 (col1 NUMBER); Table created. SQL> CREATE INDEX an_index ON a_table(col1); Index created. SQL> CREATE FUNCTION a_function RETURN BOOLEAN AS 2 BEGIN 3 RETURN(TRUE); 4 END; 5 / Function created. SQL> /*-- flush the DBMS_OUTPUT buffer */ SQL> BEGIN NULL; END;/ I believe you have created a TABLE called A_TABLEI believe you have created a INDEX called AN_INDEXI believe you have created a FUNCTION called A_FUNCTION PL/SQL procedure successfully completed.Much more attention will be paid now that the town crier is more forthcoming. The above examples touch upon two important aspects of DDL triggers: the specific events to which they can be applied and the event attributes available within the triggers.
