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.
|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.