| triggers dropped (unable to restore it) [message #572010] |
Wed, 05 December 2012 06:18  |
ishika_20
Messages: 226 Registered: December 2006 Location: delhi
|
Senior Member |
|
|
Dear All,
Some triggers are dropped and stored in recycle bin.
when i am trying to restore it by the command
1* alter trigger "BIN$FFRO1R1LSuSIZ6uyLocD6g==$0" rename to WFNOTIFICATION_GEN_PK
SQL> /
alter trigger "BIN$FFRO1R1LSuSIZ6uyLocD6g==$0" rename to WFNOTIFICATION_GEN_PK
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
Please suggest how to restore it from recycle bin.
Regards,
Ishika
|
|
|
|
|
|
|
|
| Re: triggers dropped (unable to restore it) [message #572025 is a reply to message #572010] |
Wed, 05 December 2012 08:02   |
joy_division
Messages: 4288 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
ishika_20 wrote on Wed, 05 December 2012 07:18Dear All,
Some triggers are dropped and stored in recycle bin.
when i am trying to restore it by the command
[/code]
I think I have seen this before. I don't know if it's a bug or what, but if you create an invalid trigger on a table, then drop the table, then either restore the table or flashback the table, I believe you will get what you are seeing.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: triggers dropped (unable to restore it) [message #572035 is a reply to message #572031] |
Wed, 05 December 2012 08:35   |
ishika_20
Messages: 226 Registered: December 2006 Location: delhi
|
Senior Member |
|
|
Dear Murali,
This post is for the same.
Dear Michel,
We found in toad tool that, all triggers are appearing as invalid. So we recomplied all the invalid objects. After doing recompilation, all triggers are working.
Thanks,
Ishika
|
|
|
|
| Re: triggers dropped (unable to restore it) [message #572036 is a reply to message #572030] |
Wed, 05 December 2012 08:35   |
 |
Michel Cadot
Messages: 54706 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I tried it in 10.2.0.4 and do not see anything wrong.
First the normal case (trigger is valid):
SQL> purge recyclebin;
Recyclebin purged.
SQL> create table t (v integer);
Table created.
SQL> create or replace trigger trg before insert on t
2 for each row
3 begin
4 dbms_output.put_line('Inserting '||:new.v);
5 end;
6 /
Trigger created.
SQL> select object_name, original_name, type from recyclebin;
no rows selected
SQL> drop table t;
Table dropped.
SQL> col original_name format a13
SQL> select object_name, original_name, type from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ ------------- -------------------------
BIN$5mEZGERISoy8hSEw2UUouA==$0 T TABLE
BIN$hVGh2OiPQO6h2sN7Pqii4g==$0 TRG TRIGGER
2 rows selected.
SQL> flashback table t to before drop;
Flashback complete.
SQL> select object_name, original_name, type from recyclebin;
no rows selected
SQL> select trigger_name from user_triggers where table_name='T';
TRIGGER_NAME
------------------------------
BIN$hVGh2OiPQO6h2sN7Pqii4g==$0
1 row selected.
We see what is expected the trigger is back (but for its name, known "feature").
Now with an invalid trigger:
SQL> drop table t;
Table dropped.
SQL> purge recyclebin;
Recyclebin purged.
SQL> create table t (v integer);
Table created.
SQL> create or replace trigger trg before insert on t
2 begin
3 error
4 end;
5 /
Warning: Trigger created with compilation errors.
SQL> drop table t;
Table dropped.
SQL> select object_name, original_name, type from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ ------------- -------------------------
BIN$+wNqPX8kTtKspfUWk8W50Q==$0 T TABLE
BIN$6Fo4oLpxT82556aUePrBsg==$0 TRG TRIGGER
2 rows selected.
SQL> flashback table t to before drop;
Flashback complete.
SQL> select object_name, original_name, type from recyclebin;
no rows selected
SQL> select trigger_name from user_triggers where table_name='T';
TRIGGER_NAME
------------------------------
BIN$6Fo4oLpxT82556aUePrBsg==$0
1 row selected.
SQL> @v
Version Oracle : 10.2.0.4.0
Same thing.
Regards
Michel
|
|
|
|
|
|
| Re: triggers dropped (unable to restore it) [message #572046 is a reply to message #572037] |
Wed, 05 December 2012 09:07   |
cookiemonster
Messages: 9289 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Trigger code really ought to be under source control. Then you wouldn't have to try and fish it out of the DB.
If your trigger code (and all your other DB code) isn't under source control I suggest you fix that asap.
|
|
|
|
|
|
|
|