need help with DDL trigger
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.