| Trigger related question [message #6688] |
Fri, 02 May 2003 00:08  |
shawlin
Messages: 25
Registered: April 2003
|
Junior Member |
|
|
Hello,
I am in oracle8i,i have a question on this.
Is it possible to write a trigger based on
the table creation and create a synonym for
the created table.every time i create a table
i want to perform this task regularily.
As in oracle8i we can write trigger for the
after table creation.
Please help me in this regard
Thanks and regards
shawlin
|
|
|
|
| Re: Trigger related question [message #6700 is a reply to message #6688] |
Fri, 02 May 2003 03:45  |
jigar
Messages: 74
Registered: July 2002
|
Member |
|
|
Hi, Hope this may give you some idea,
1.
CREATE OR REPLACE TRIGGER on_Create_trg
AFTER CREATE ON SCOTT.SCHEMA
Declare
str1 Varchar2(4000);
Begin
if DICTIONARY_OBJ_TYPE = 'TABLE' then
str1:='Create synonym a for emp';
execute immediate (str1);
end if;
END;
2.
Create table b ( b number);
Error:
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 6
This shows the Create Trigger is going into Recursive Loop as Create Synonym will fire the same trigger again
.
You can fire any DDL statement except Create in this trigger.
Regards,
Jigar
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
