Home » SQL & PL/SQL » SQL & PL/SQL » Create table using trigger (11.2.0.3.0)
Create table using trigger [message #576192] Fri, 01 February 2013 04:33 Go to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Hi Experts,

Is it possible to create table using trigger?

I tried but failed...

CREATE OR REPLACE TRIGGER tri1
AFTER UPDATE
   ON dept
   FOR EACH ROW
   
declare
 pragma AUTONOMOUS_TRANSACTION;
   
BEGIN

   execute immediate 'create table dept_dum as select  * from dept';
     
END;
/



SQL> update dept set deptno=23 where deptno=10;
update dept set deptno=23 where deptno=10
       *
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TRI1", line 6
ORA-04088: error during execution of trigger 'SCOTT.TRI1'


I am running this as scott user

My requirement is to

create table as select * from table_name where flag=1


This has to be done parallel for all the tables for which this flag is enabled and by that trigger delete all those rows which were backed up as table

Thanks in advance..


[Updated on: Fri, 01 February 2013 04:45]

Report message to a moderator

Re: Create table using trigger [message #576194 is a reply to message #576192] Fri, 01 February 2013 04:41 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why would you ever want to do that?

[EDIT] Your code is OK, but it is going to fail whenever the DEPT table gets updated for the second time or if you update more than a single record.

SQL> create or replace trigger tri1
  2     after update
  3     on dept
  4     for each row
  5  declare
  6     pragma autonomous_transaction;
  7  begin
  8     execute immediate 'create table dept_dum as select  * from dept';
  9  end;
 10  /

Trigger created.

SQL>
SQL> update dept
  2     set dname = 'SALES'
  3   where deptno = 30;

1 row updated.

SQL> /
update dept
       *
ERROR at line 1:
ORA-00955: name is already used by an existing object
ORA-06512: at "SCOTT.TRI1", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRI1'


SQL> drop table dept_dum;

Table dropped.

SQL> update dept set dname = dname;
update dept set dname = dname
       *
ERROR at line 1:
ORA-00955: name is already used by an existing object
ORA-06512: at "SCOTT.TRI1", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRI1'


SQL>

Therefore, that's rather unusual requirement.

[Updated on: Fri, 01 February 2013 04:48]

Report message to a moderator

Re: Create table using trigger [message #576195 is a reply to message #576194] Fri, 01 February 2013 04:49 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Littlefoot,

I want to take backup of those records put it in other table and then delete it from main table and this i want to do for 'n' number of tables simultaneously

[EDIT] I Think i shouldn't rely on triggers than ,Pl/sql block should be best bet here but i want to take backup and then delete from tables simultaneously but i don't know which all tables will need this that why i was hoping for trigger to work .

Thanks Littlefoot

[Updated on: Fri, 01 February 2013 04:57]

Report message to a moderator

Re: Create table using trigger [message #576196 is a reply to message #576195] Fri, 01 February 2013 04:58 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I told you - that won't work, generally. Will be OK in a case you update a single record in a table, only once.

If you want to save certain records in another table, then yes - a trigger is a right choice, but you should use DML in there, not DDL. Create your "backup" table(s) once, insert into them anytime you want. Search for it, there are plenty of examples both here, on OraFAQ and the Internet.
Re: Create table using trigger [message #576208 is a reply to message #576192] Fri, 01 February 2013 07:34 Go to previous message
Michel Cadot
Messages: 59510
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My advice: NEVER use "pragma AUTONOMOUS_TRANSACTION;" in a trigger.
If you (think you) need it then it is most likely your design is wrong.

Regards
Michel
Previous Topic: how to create the trigger ?
Next Topic: Date time format error : ORA-01830
Goto Forum:
  


Current Time: Sat Nov 01 05:24:31 CDT 2014

Total time taken to generate the page: 0.08254 seconds