PL/SQL tutorial 18: DDL Trigger with Schema Auditing Example

Learn how to create a DDL Trigger in Oracle Database with Schema Auditing Example where we will learn how to keep an any on which object is created, altered or drop and by who. Manish Sharma From RebellionRider

------------------------------------------------------------------------
►►►LINKS◄◄◄
Previous Tutorial
►PL/SQL Tut 14 Triggers Introduction
► PL/SQL Tut 17 Table Backup Using DML Trigger
-------------------------------------------------------------------------
►►►Let's Get Free Uber Cab◄◄◄
Use Referral Code UberRebellionRider and get $20 free for your first ride.

-------------------------------------------------------------------------
►Make sure you SUBSCRIBE and be the 1st one to see my videos!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
►►►Find me on Social Media◄◄◄
Follow What I am up to as it happens on

You can also Email me at
for E-mail address please check About section

Please please LIKE and SHARE my videos it makes me happy.
Thanks for liking, commenting, sharing and watching more of our videos

♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS

[pavanmadamset]

Manish Sir, Watching And Practicising Your Video Tutorials Is The Most Constructive Part Of My Day!!! Your Videos Are Helping a Lot!!! Thank You Very Much Sir!!!

[motohuzu]

+Manish Sharma, Great Job Brother... Thankyousoomuch for such free tutorials :)

[nadunsamarasinghe]

Hi Manish, I was very much interest of your PLSQL tutorials and those are very helpful. need to know whether we can audit the execution of a stored procedure by trigger if it possible. Thanks. Keep your journey with much more adventures.

[TheTaushikdasgupta]

hi Manish,

great stuff about TABLE/SCHEMA auditing. I would like to know if we can enable auditing on multiple schemas using a single trigger.

Looking forward to hear it back from you.

[NigelCoutinhodec]

Hi Manish, I used sys_context('USERENV', 'CURRENT_USER') for DDL trigger on database, but it gave me the same user SYS. instead of Nigel. Then I used user and it worked. Did I miss something? Please help. Thanks in advance.

[kebingli]

Thank man, you did a great job. Can you tell what is the hotkey for format the code when you create the table schema_audit?

[himanshuchouhan]

can we use 'DML' in DML triggers just like we used 'DDL' here ?

[muhammadbilalliaquat]

hi +Manish Sharma Great job man... kindly if you can help on which trigger should i use if i only want to delete data from one table and make a replica of it on the other??? Kindly do answer

[syedsabeer]

Hi Manish, I am not able to drop SCHEMA_AUDIT table with this query "drop table schema_audit" after performing all DDL operations

[prakash.r]

Can i use tablename in the 'schema' field in given example so as to audit ddl on that table only ?

[Ganeshay-]

sir I am facing one proble in SQL developer soft .. into the other user there is available HR table they how to use how to backup from hr table ..in left side is hr users down side plz tell me about this

[mugilans]

I want to create a trigger after insert and body of the trigger should create a user in database.. Is it possible?

[lkeditz]

SYS_context('USERENV', 'CURRENT_USER') is error...how to find my sys_context please help me brother... I'm big fan of your oracle video's

[rupamsinharoy]

i tried the same code on hr account in oracle 10g express edition but the table schema_audit remains empty . can anyone help me on this please !

[lkeditz]

ORA-04098: trigger 'HR.HR_TABLE_LOG_TR' is invalid and failed re-validation

This my error to my toad for oracle tool.

How to solve the issue?

[akashjain]

1.what if we drop the trigger itself ?
2. what if we drop the audit table itself ?

[VishalFTW]

How did drop command worked and It did not worked. like it have not drop the table

[technologyfollowing]

Hi Manish..Thanks for the video..But I am getting a error when trying to create or alter table and the message is:ORA-00604: error occurred at recursive SQL level 1.
I tried to solve, but I can't able to get it. Can you tell me the solution..

[kirannikam]

I tried creating DDL trogger on table.
Well, we can not do that, it was an interview question for me.

CREATE OR REPLACE TRIGGER ddl_emo_demo
AFTER ALTER ON emp_demo
BEGIN

DBMS_OUTPUT.PUT_LINE(sys_context('userenv', 'current_user'), ora_dct_obj_type, ora_dct_obj_name);
END;

it gives error:
ORA 30506: system triggers cannot be based on tables or views

[luiizmorales]

"insufficient privileges" What kind of GRANT can I use?