pl sql tutorial#21 PL SQL DDL tringger in oracle database |pl sql triggers

pl sql triggers tutorial, we will learn the DDL trigger in oracle database, DDL trigger is nothing but it will get triggered at DDL activity like CREATE/DROP/TRUNCATE Obj(table/view) etc.
Syntax of DDL trigger in oracle database :
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER }
{CREATE | ALTER | TRUNCATE}
ON { DATABASE | SCHEMA}
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
DDL trigger example in oracle plsql tutorial :
CREATE OR REPLACE TRIGGER myFirstDDLTrigger1
after DDL on DATABASE
declare
a number(10);
event_Type VARCHAR2(15);
object_Type VARCHAR2(15);
object_name VARCHAR2(15);
begin
dbms_output.put_line('Performing operation: ' || ora_sysevent
|| ' On type of obj: ' || ORA_DICT_OBJ_TYPE
||' Name of obj: '|| ora_dict_obj_name
||' On Date: '||sysdate);
event_Type := ora_sysevent;
object_Type := ORA_DICT_OBJ_TYPE;
object_name := ora_dict_obj_name;
insert into schema_audit
values(sysdate,sys_context('USERENV','CURRENT_USER'),
event_Type,object_Type,object_name);
IF (ora_sysevent='TRUNCATE') THEN
dbms_output.put_line('Hey I have truncated table');
ELSIF(ora_sysevent = 'DROP') THEN
dbms_output.put_line('Hey I have dropped table');
ELSIF(ora_sysevent = 'CREATE') THEN
dbms_output.put_line('Hey I have Created table');
END IF;
end;
Oracle SQL Complete Tutorial/Training/Course for the beginner:
• Tutorial#1 Basics Of O...
follow me on:
Facebook Page:
www.facebook.c...
/ equalconnect
/ lrnwthr
#plsql #oracle

[@computerlearningbyargusaca5217]

🙏🙏It was fabulous . just provide 👍👍exact clarity . 😍😍what I was\ looking for

[@josephishaya9323]

Nice one keeping it up

[@ArvindSingh-mj3rb]

ALTER TABLE table_name DISABLE/ENABLE ALL TRIGGERS;

[@josephishaya9323]

The tutorial on oracle was interested keep up but tutorial #73 Was missing

[@josephishaya9323]

Did you have any tutorial on high programming language

[@EqualConnectCoach]

no

[@pradnyeshshimpi4041]

when i use sys_context it gives null value.. please help how i solved this

[@EqualConnectCoach]

Please let me know what u want to do

[@pradnyeshshimpi4041]

@@EqualConnectCoach sir i m creating DDL trigger on schema. when i try to fetch current user data by using sys_context('usernv','current_user') is returning null value in table. how i solved? i m just begginer.. plz help

[@EqualConnectCoach]

@@pradnyeshshimpi4041 please use select user into variableName from dual; now print this variableName

[@pradnyeshshimpi4041]

@@EqualConnectCoach sir thank you. sir but sys_context fuction useable only in license version?? why this fuction not working?

[@pradnyeshshimpi4041]

@@EqualConnectCoach my issue solved.. but i wanted to know why this happening.. why sys_context fuction not working ?or any other methos required to activate or something like. that.. please let me know..