Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

need help with DDL trigger

Jana KralovaJun 13 2010 — edited Jun 13 2010
Hi,
I'm quite new to PL/SQL and I was wondering if someone could help me do this simpler...

I created these three triggers:

create or replace trigger create_obj
after create on schema
begin
dbms_output.put_line
('You created '||' '||ora_dict_obj_type||' called '||ora_dict_obj_name);
end;
/

create or replace trigger drop_obj
after drop on schema
begin
dbms_output.put_line
('You droped '||' '||ora_dict_obj_type||' called '||ora_dict_obj_name);
end;
/

create or replace trigger alter_obj
after alter on schema
begin
dbms_output.put_line
('You altered '||' '||ora_dict_obj_type||' called '||ora_dict_obj_name);
end;
/

and if I run this sql


set serveroutput on
create table volcano
(hey varchar2(5));
alter table volcano
add name varchar2(15);
drop table volcano;

I get the following message

create table succeeded.
You created TABLE VOLCANO

alter table volcano succeeded.
You altered TABLE VOLCANO

drop table volcano succeeded.
You droped TABLE VOLCANO

with DML triggers you can declare a variable and insert transaction into it...
example

create or replace
triggervolcano
after insert or update on instructor
declare
v_type varchar(20);
begin
if updating then
v_type:='Update';
else
v_type:='Insert';
dbms_output.put_line(v_type);
end;
/

I tried to work with DDLtriggers like

if createing then
......

but I only got the error message that creating needs to be declared....
Anyways, what I want to know is if there is a way to combine the three triggers above into one and how can I do that.
In advance, thanks for answer or any hints you can provide.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 11 2010
Added on Jun 13 2010
7 comments
1,689 views