RE: AFTER CREATE trigger help, please
Chris,
Well, dang, it does work!
Guess I should have tried it myself first.
It will only work from SQL Plus though, or other
tools that capture DBMS_OUTPUT.
Oracle must send it to the big bit bucket in the
sky otherwise.
Jared
"Grabowy, Chris" <[EMAIL PROTECTED]>
05/10/2002 11:25 AM
To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>,
"'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>,
"'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>
cc:
Subject:RE: AFTER CREATE trigger help, please
Actually...
SQL> create or replace trigger test
2 after insert on test_table
3 begin
4 dbms_output.put_line ('Hello from trigger!!!');
5 end;
6 /
Trigger created.
Elapsed: 00:00:00.02
SQL> insert into test_table values ('Hello');
Hello from trigger!!!
1 row created.
Elapsed: 00:00:00.06
Execution Plan
--
0 INSERT STATEMENT Optimizer=CHOOSE
Statistics
--
3 recursive calls
5 db block gets
7 consistent gets
0 physical reads
372 redo size
550 bytes sent via SQL*Net to client
627 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Bill, I would simplify the trigger, and then work out from there.
Chris
-Original Message-
Sent: Friday, May 10, 2002 2:23 PM
To: Multiple recipients of list ORACLE-L
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:
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:
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).
RE: AFTER CREATE trigger help, please
Limited yes, but invaluable when debugging a trigger, and other possible
uses.
Chris
-Original Message-
Sent: Friday, May 10, 2002 2:33 PM
To: [EMAIL PROTECTED]
Cc: Grabowy, Chris
Chris,
Well, dang, it does work!
Guess I should have tried it myself first.
It will only work from SQL Plus though, or other
tools that capture DBMS_OUTPUT.
Oracle must send it to the big bit bucket in the
sky otherwise.
Jared
"Grabowy, Chris" <[EMAIL PROTECTED]>
05/10/2002 11:25 AM
To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>,
"'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>,
"'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>
cc:
Subject:RE: AFTER CREATE trigger help, please
Actually...
SQL> create or replace trigger test
2 after insert on test_table
3 begin
4 dbms_output.put_line ('Hello from trigger!!!');
5 end;
6 /
Trigger created.
Elapsed: 00:00:00.02
SQL> insert into test_table values ('Hello');
Hello from trigger!!!
1 row created.
Elapsed: 00:00:00.06
Execution Plan
--
0 INSERT STATEMENT Optimizer=CHOOSE
Statistics
--
3 recursive calls
5 db block gets
7 consistent gets
0 physical reads
372 redo size
550 bytes sent via SQL*Net to client
627 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Bill, I would simplify the trigger, and then work out from there.
Chris
-Original Message-
Sent: Friday, May 10, 2002 2:23 PM
To: Multiple recipients of list ORACLE-L
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:
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: Grabowy, Chris
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).
RE: AFTER CREATE trigger help, please
Actually...
SQL> create or replace trigger test
2 after insert on test_table
3 begin
4 dbms_output.put_line ('Hello from trigger!!!');
5 end;
6 /
Trigger created.
Elapsed: 00:00:00.02
SQL> insert into test_table values ('Hello');
Hello from trigger!!!
1 row created.
Elapsed: 00:00:00.06
Execution Plan
--
0 INSERT STATEMENT Optimizer=CHOOSE
Statistics
--
3 recursive calls
5 db block gets
7 consistent gets
0 physical reads
372 redo size
550 bytes sent via SQL*Net to client
627 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Bill, I would simplify the trigger, and then work out from there.
Chris
-Original Message-
Sent: Friday, May 10, 2002 2:23 PM
To: Multiple recipients of list ORACLE-L
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:
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: Grabowy, Chris
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).
RE: AFTER CREATE trigger help, please
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).
Re: AFTER CREATE trigger help, please
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:
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).
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).
Trigger help
Dear DBA Gurus, I have a table with two fields name and status. What I want to do is once the current system date rolls over from 30th to 31st the status should be entered as "closed" other wise from 1st to 30th the status should be entered as open. How do I do this using trigger or any dbms packages? Any help in this regard will be very much appreciated. TIA and Regards, Ranganath DISCLAIMER: This correspondence is confidential and intended for the named recipient(s) only. If you are not the named recipient and receive this correspondence in error, you must not copy, distribute or take any action in reliance on it and you should delete it from your system and notify the sender immediately. Unless otherwise stated, any views or opinions expressed are solely those of the author and do not represent those of Subex Systems Limited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K 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).
