Oracle command to create a table from another schema, including triggers?
Using this command, I am able to create a table from another schema, but it does not include triggers. Is it possible to create a table from another schema, including triggers?
create table B.tablename unrecoverable as select * from A.tablename where 1 = 0;
First option is to run CREATE script for those objects, if you have a code repository. I suppose you don't.
If you use any GUI tool, things are getting simpler as they contain the SCRIPT tab that enables you to copy code from source and paste it into target user.
If you're on SQLPlus, it means that you should, actually, know what you're supposed to do. Here's a short demo.
SQL> connect hr/hr@xe Connected. SQL> create table detail (id number); Table created. SQL> create or replace trigger trg_det 2 before insert on detail 3 for each row 4 begin 5 :new.id := 1000; 6 end; 7 / Trigger created. SQL> SQL> -- you'll have to grant privileges on table to another user SQL> grant all on detail to scott; Grant succeeded.
Connect as SCOTT and check what we've got:
SQL> connect scott/tiger@xe Connected. SQL> -- now, query ALL_SOURCE and you'll get trigger code SQL> set pagesize 0 SQL> col text format a50 SQL> select text from all_source where name = 'TRG_DET' order by line; trigger trg_det before insert on detail for each row begin :new.id := 1000; end; 6 rows selected. SQL>
Yet another option is to export & import table, which will get the trigger as well (I've removed parts that aren't relevant, as Oracle database version):
C:\>exp hr/hr@xe tables=detail file=detail.dmp About to export specified tables via Conventional Path ... . . exporting table DETAIL 0 rows exported Export terminated successfully without warnings. C:\>imp scott/tiger@xe file=detail.dmp full=y . importing HR's objects into SCOTT . importing HR's objects into SCOTT . . importing table "DETAIL" 0 rows imported Import terminated successfully without warnings. C:\>
Check what's imported (should be both table and trigger):
SQL> desc detail Name Null? Type ----------------------------------------- -------- --------------- ID NUMBER SQL> select * From detail; no rows selected SQL> insert into detail (id) values (-1); 1 row created. SQL> select * From detail; ID ---------- 1000 SQL>
Cool; even the trigger works.
There might be some other options, but these 4 should be enough to get you started.