| Trigger To Drop Table [message #1397] |
Thu, 25 April 2002 20:23  |
Amit
Messages: 166
Registered: February 1999
|
Senior Member |
|
|
I want to write a Trigger in such way
that if I enter any value in one of the table and if
table is exist of the same value which I enter in that table then the trigger will drop the table of that value
Help me
|
|
|
|
| Re: Trigger To Drop Table [message #1404 is a reply to message #1397] |
Fri, 26 April 2002 08:06  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
are u lookin for something like this?
*******************
source table
******************
SQL> desc e
Name Null? Type
----------------------------------------------------- -------- -------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
SQL> desc sample
Name Null? Type
----------------------------------------------------- -------- ------------
DEPTNO NUMBER(2)
LOC VARCHAR2(13)
HIREDATE DATE
*****************
SQL> ed
Wrote file afiedt.buf
1* insert into e values (3,'SAMPLE')
SQL> /
drop table SAMPLE
1 row created.
SQL> DESC SAMPLE
ERROR:
ORA-04043: object SAMPLE does not exist
SQL>
****************
CODE IS
*****************
SQL> GET TA
1 create or replace trigger drop_tab
2 after insert on e
3 for each row
4 declare
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 tname varchar2(30);
7 str varchar2(300);
8 begin
9 select table_name into tname from user_tables where table_name=:new.ename;
10 str:='drop table '|| tname;
11 execute immediate str;
12 dbms_output.put_line(str);
13 exception
14 when no_data_found then
15 dbms_output.put_line('no matching tables found');
16* end;
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
