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 Oracle SQL for B...
follow me on:
Facebook Page:
https://www.facebook.com/Equalconnect...
/ equalconnect
/ lrnwthr
#plsql #oracle
Watch video pl sql tutorial#21 PL SQL DDL tringger in oracle database |pl sql triggers online without registration, duration hours minute second in high quality. This video was added by user EqualConnect Coach 11 October 2020, don't forget to share it with your friends and acquaintances, it has been viewed on our site 3,776 once and liked it 49 people.