DDL Triggers in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

DDL Triggers in Oracle in Examples

In this article, I am going to discuss Creating DDL Triggers in Oracle with Examples. Please read our previous article where we discussed On Delete Cascade in Oracle with Examples.

Creating DDL Triggers in Oracle

We created a trigger on the table and a trigger on the view. Now, let us try to create a trigger on schema and on a database. Before trying to create a trigger on the user, let us try to create a DDL trigger. The DDL operations are as below.

ALTER, ANALYZE, ASSOCIATE STATISTICS, AUDIT, COMMENT, CREATE, DISASSOCIATE STATISTICS, DROP, GRANT, NOAUDIT, RENAME, REVOKE, TRUNCATE.

We can create DDL triggers on all the above DDL operations. Let us see the syntax of the DDL triggers in Oracle as follows.

CREATE OR REPLACE TRIGGER TRIGGER_NAME
BEFORE | AFTER --- TIMING
[DDL EVENT1 OR DDL EVENT 2] ----DDL STATEMENTS
ON (DATABASE | SCHEMA)
TRIGGER_BODY

Let us see an example to understand how the DDL trigger works in Oracle. So, create a DDL Trigger in Oracle by executing the below statements.

CREATE OR REPLACE TRIGGER before_create_trigger
BEFORE CREATE
 ON SCHEMA
BEGIN
if to_number(to_char(sysdate,'hh24')) not between 8 and 16 then
raise_application_error(-20001, 'Create not Allowed now');
end if;
END;

So, from the above trigger statement, we can see the trigger name is before_create_trigger and the timing is before and the event is create on the schema. In the if condition we are converting the system date and time to 24-hour format. The main logic of this trigger is when we execute any create statement on the schema between 8 to 15, we will not face any issues. Apart from these hours if we execute any create statement, we will get the application error “20001”. Let us try to compile this trigger.

The trigger is successfully compiled. Let us try to run a create statement on the schema hr. We will be creating a table T3 by executing the below statement.

CREATE TABLE T3 (VALUE1 NUMBER);

We are getting the error showing “Create not allowed now”. So, creating a table on schema is working fine. Let us try to create a view by executing the below statement and see how it works.

CREATE OR REPLACE VIEW X_Y AS SELECT * FROM EMPLOYEES;

Let us execute this view creation.

We are getting the same error with the view creation as well. So, our trigger is working fine.

Note: If we don’t want to make any changes in the database, we have stopped executing all the DDL operations in the database like create, alter, drop, insert, and update. We have to mention the DDL clause in the trigger if we need to use the event as an entire DDL operation.

Let us try to create a table named ddl_log by executing the below CREATE TABLE Statement. This table will store all the information that the DDL operation performed on the schema.

CREATE TABLE ddl_log 
(
 operation VARCHAR2(30),
 obj_owner VARCHAR2(30),
 object_name VARCHAR2(30),
 attempt_by VARCHAR2(30),
 attempt_dt DATE
); 

We are creating a table named ddl_log with columns operation, obj_owner, attempt_by, attempt_dt. Let us try to create this table.

So, the table is created. Let us try to create a trigger on the entire DDL operations by executing the below statements.

CREATE OR REPLACE TRIGGER before_create_trigger
AFTER DDL
 ON SCHEMA
BEGIN
   INSERT INTO ddl_log
   SELECT ora_sysevent, ora_dict_obj_owner,
   ora_dict_obj_name, USER, SYSDATE
   FROM DUAL;
END;

From the above statement we can see the trigger name is before_create_trigger and the event is DDL (which includes all the DDL operations) and the timing is after the DDL operation on the schema hr. The main logic is once the DDL operation is performed on the schema hr then the trigger will insert the system event, object name, object owner, user, and system date into the table ddl_log. Let us try to compile the trigger.

So, the trigger is compiled successfully. Let us check the table ddl_log table.

So, the table is empty now. Let us try to create a table in the schema by executing the below statement.

CREATE TABLE T3 (N NUMBER);

Let us check the table ddl_log by executing the below SELECT Statement.

SELECT * FROM DDL_LOG;

You can see the operation name is create and the object owner is hr. The object_name is t3 and the operation is attempted by the hr user. The attempted date is 03 Feb 2023. Let us try to add a column in table t3 by executing the following ALTER TABLE Statement.

ALTER TABLE T3 ADD X NUMBER;

So, table t3 is altered. Let us check the table ddl_log again and see if the entry is inserted or not by executing the below SELECT Statement.

SELECT * FROM DDL_LOG;

So, the alter record is also inserted. Now, let us try to drop the table t3 by executing the below DROP TABLE Statement.

DROP TABLE T3;

So, the table is dropped. Let us check the table if the dropped record is inserted or not by executing the below SELECT Statement

SELECT * FROM DDL_LOG;

So, the dropped record is also inserted. So, this is also working fine. We understood that we can use the triggers to prevent any user to perform any operation in the database or we can store the detail that is inserted data into the database.

Database Triggers vs Stored Procedures in Oracle

Until now, we understood triggers, compound triggers, and mutating tables. Now, let us compare the differences between the database triggers and the stored procedures.

Triggers	Procedures
Defined with CREATE TRIGGER	Defined with CREATE PROCEDURE
Data Dictionary contains source code in USER_TRIGGERS	Data Dictionary contains source code in USER_SOURCE
Implicitly invoked by DML	Explicitly invoked
COMMIT, SAVEPOINT, and ROLLBACK are not allowed	COMMIT, SAVEPOINT, and ROLLBACK are allowed.

Triggers: Trigger executes automatically whenever an event has occurred in the database. We cannot roll back the trigger.

Stored Procedures: Stored procedures are used to perform one or multiple SQL statements. This is nothing but a group of DML or DDL statements. The stored procedures may accept some input value and may not return a value.

In the next article, I am going to discuss System Event Trigger in Oracle with Examples. Here, in this article, I try to explain Creating DDL Triggers in Oracle with Examples. I hope you enjoy this Creating DDL Triggers in Oracle with Examples article.