Home » SQL & PL/SQL » SQL & PL/SQL » can a trigger create a table?
can a trigger create a table? [message #21070] Wed, 10 July 2002 16:51 Go to next message
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 Go to previous messageGo to next message
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;
Re: can a trigger create a table? [message #21073 is a reply to message #21070] Wed, 10 July 2002 17:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It is possible.
1 --> u can create a table inside a pl/sql block.
use dynamic sql for that.
2 --> In case of trigger, there is little change.
becase all DDL will result in an internal commit,
which is not allowed by trigger.
3 --> So use an autonomous transaction through a
procedure.

 
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  (32,'afd','ads');

1 row created.

 
SQL> desc new_table;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 COL1                                                           NUMBER
thx! [message #21074 is a reply to message #21073] Wed, 10 July 2002 17:46 Go to previous messageGo to next message
JSlick
Messages: 15
Registered: July 2002
Junior Member
No Message Body
Insufficient Priviliges [message #21084 is a reply to message #21073] Thu, 11 July 2002 09:59 Go to previous messageGo to next message
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!
Re: Insufficient Priviliges [message #21085 is a reply to message #21073] Thu, 11 July 2002 10:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
first creating user object in SYSTEM/SYS schema is not recomended.
this error is becuase the required privs might be granted through roles. procedure wont SEE that.
grant the privs directly to user.
login as sys and give

grant create table to user;
grant crate table to user;
Re: Insufficient Priviliges [message #21087 is a reply to message #21073] Thu, 11 July 2002 11:56 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
ie, grant both trigger and table privs
grant create table to user;
grant create trigger to user;
Re: can a trigger create a table? [message #21088 is a reply to message #21070] Thu, 11 July 2002 11:56 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
ie, grant both trigger and table privs
grant create table to user;
grant create trigger to user;
Previous Topic: Re: Column length...
Next Topic: Sampling
Goto Forum:
  


Current Time: Thu Apr 25 23:17:28 CDT 2024