ok - thanks
i edited the trigger to insert a row into a dummy table, just to see if it
works, and all works fine.
Now - the next piece.
I want this trigger to call a stored procedure that runs as an autonomous
txn and creates a public synonym for the newly-created table.
I've verified independently that the proc works (from the SQL*Plus command
line) and I know now that the trigger is being called, based on the previous
test.
so why wouldn't the public synonym be created?
THANKS!
**************************
Here's the text of the new trigger and procedure:
CREATE OR REPLACE procedure CREATE_SYNONYMS (
p_lwowner varchar2,
p_objname varchar2,
p_objtype varchar2) AS
pragma autonomous_transaction;
v_lwowner varchar2(30) := p_lwowner;
v_objname varchar2(30) := p_objname;
v_objtype varchar2(30) := p_objtype;
v_sql varchar2(200);
begin
v_sql := 'create public synonym ' || v_objname || ' for ' ||
v_lwowner || '.' || v_objname;
execute immediate (v_sql);
end;
/
CREATE OR REPLACE TRIGGER after_create_trg
after create
on lwdev.schema
begin
if sys.dictionary_obj_type='TABLE'
OR sys.dictionary_obj_type='SEQUENCE' then
begin
create_synonyms ('lwdev', sys.dictionary_obj_name,
sys.dictionary_obj_type);
exception
when others then null;
end;
end if;
end;
/
-----Original Message-----
Sent: Friday, May 10, 2002 1:14 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Bill,
A trigger cannot display output.
Where would it go? A trigger does not run in an interactive
session, it runs in the database independently, regardless
of the presence or absence of a SQL Plus session.
Jared
"Magaliff, Bill" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
05/10/2002 09:08 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: AFTER CREATE trigger help, please
I'm creating an AFTER CREATE ddl trigger:
CREATE OR REPLACE TRIGGER after_create_trg
after create
on lwdev.schema
begin
if sys.dictionary_obj_type='TABLE'
OR sys.dictionary_obj_type='SEQUENCE'
then
begin
dbms_output.put_line ('TEST');
dbms_output.put_line
(sys.dictionary_obj_name || ' , ' ||
sys.dictionary_obj_type);
exception
when others then
dbms_output.put_line
('ERROR');
end;
end if;
end;
/
Trigger created as user LWDEV
I have serverout set to ON, but I get nothing when creating a new table .
.
.
any ideas, please??
thanks, all
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
