can a trigger create a table? [message #21070] |
Wed, 10 July 2002 16:51 |
JSlick
Messages: 15 Registered: July 2002
|
Junior Member |
|
|
I'd like to have a trigger that creates a new child table whenever a new record is inserted into the parent table. I don't think this is possible, as PL/SQL seem to not like the CREATE keyword.
Is it possible to use PL/SQL to create a table?
|
|
|
yes, but problem with COMMIT... [message #21072 is a reply to message #21070] |
Wed, 10 July 2002 17:34 |
JSlick
Messages: 15 Registered: July 2002
|
Junior Member |
|
|
The execute statement line below works fine and creates the table from a PL/SQL block, but within the trigger, it generates an error:
"ORA-04092: cannot COMMIT or ROLLBACK in a trigger
Cause: A trigger attempted to COMMIT or ROLLBACK. This is not permitted.
Action: Rewrite the trigger so that COMMIT or ROLLBACK statements are not used. "
What gives here? How do I convince Oracle that there is not COMMIT statement here?
CREATE OR REPLACE TRIGGER newhistorytable
AFTER INSERT ON MATERIAL
DECLARE
BEGIN
execute immediate 'create table testtable1 (idfield integer )';
END newhistorytable;
|
|
|
|
|
Insufficient Priviliges [message #21084 is a reply to message #21073] |
Thu, 11 July 2002 09:59 |
JSlick
Messages: 15 Registered: July 2002
|
Junior Member |
|
|
I tried your sample code, running as role "system", but get privilige errors. I would appreciate a bit of guidance about this. I have been looking through the Oracle online docs, but have not been able to clarify the solution.
I would like to have web users be able to insert records into a table such as dept, and have a trigger automagically create a detail table, such as "new_table" (to be programatically created with a unique name... this I can do, but the current triggering priviliges are a problem).
SQL> create or replace procedure cr_tab
2 authid current_user
3 is
4 pragma AUTONOMOUS_TRANSACTION;
5 BEGIN
6 execute immediate 'CREATE TABLE new_table (col1 NUMBER)';
7 END;
8 /
Procedure created.
SQL> create or replace trigger d
2 after insert on dept
3 for each row
4 begin
5 cr_tab;
6 end;
7 /
Trigger created.
SQL> insert into dept values
2 (100, 'abc', 'def' );
insert into dept values
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYSTEM.CR_TAB", line 6
ORA-06512: at "SYSTEM.D", line 2
ORA-04088: error during execution of trigger 'SYSTEM.D'
THANKS!
|
|
|
|
|
|