Home » SQL & PL/SQL » SQL & PL/SQL » triggers dropped (unable to restore it) (10.2.0.3, windows)
triggers dropped (unable to restore it) [message #572010] Wed, 05 December 2012 06:18 Go to next message
ishika_20
Messages: 339
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 #572012 is a reply to message #572010] Wed, 05 December 2012 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Restore the table the trigger will come back.

Regards
Michel
Re: triggers dropped (unable to restore it) [message #572018 is a reply to message #572012] Wed, 05 December 2012 07:31 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Tables are there. Only triggers are dropped. How to restore triggers?
Re: triggers dropped (unable to restore it) [message #572025 is a reply to message #572010] Wed, 05 December 2012 08:02 Go to previous messageGo to next message
joy_division
Messages: 4520
Registered: February 2005
Location: East Coast USA
Senior Member
ishika_20 wrote on Wed, 05 December 2012 07:18
Dear 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 #572029 is a reply to message #572018] Wed, 05 December 2012 08:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ishika_20 wrote on Wed, 05 December 2012 14:31
Tables are there. Only triggers are dropped. How to restore triggers?


Trigger does into the recyclebin ONLY when a table does.
So the table associated to the trigger is in the recycle bin.
Query DBA_TRIGGERS to get the table name.

Regards
Michel
Re: triggers dropped (unable to restore it) [message #572030 is a reply to message #572025] Wed, 05 December 2012 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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.


Interesting, will try it.

Regards
Michel
Re: triggers dropped (unable to restore it) [message #572031 is a reply to message #572029] Wed, 05 December 2012 08:24 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

@Michal.. i have one more doubt.. if few drop trigger can we restore it.
Re: triggers dropped (unable to restore it) [message #572033 is a reply to message #572031] Wed, 05 December 2012 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on what you mean by restore.
Is there any command? no.
Is this possible to get the trigger definition? yes, if it has not been dropped so long ago and the trigger metadata are still in the undo segments.

Regards
Michel
Re: triggers dropped (unable to restore it) [message #572035 is a reply to message #572031] Wed, 05 December 2012 08:35 Go to previous messageGo to next message
ishika_20
Messages: 339
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
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 #572037 is a reply to message #572035] Wed, 05 December 2012 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ishika_20 wrote on Wed, 05 December 2012 15:35
...
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.
...


The problem and the "solution" have no relation.

Regards
Michel

Re: triggers dropped (unable to restore it) [message #572046 is a reply to message #572037] Wed, 05 December 2012 09:07 Go to previous messageGo to next message
cookiemonster
Messages: 10989
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.
Re: triggers dropped (unable to restore it) [message #572064 is a reply to message #572036] Wed, 05 December 2012 13:18 Go to previous messageGo to next message
joy_division
Messages: 4520
Registered: February 2005
Location: East Coast USA
Senior Member
Michel Cadot wrote on Wed, 05 December 2012 09:35
I tried it in 10.2.0.4 and do not see anything wrong.
First the normal case (trigger is valid):
...
Now with an invalid trigger:
...

Version Oracle : 10.2.0.4.0[/code]
Same thing.

Regards
Michel


Thanks for the great demonstration Michel. I wasn't quite sure what the circumstances were, but I do remember someone once bringing it up. I am most likely remembering incorrectly, or it just got twisted over time what the scenario was Sad
Re: triggers dropped (unable to restore it) [message #572070 is a reply to message #572064] Wed, 05 December 2012 16:53 Go to previous message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
Or the bug was specific to a version other than the one Michel is using.
Previous Topic: Object dependencies in PL/SQL code
Next Topic: Access programatically a record (%rowtype)
Goto Forum:
  


Current Time: Wed Oct 01 16:20:35 CDT 2014

Total time taken to generate the page: 0.28329 seconds