Home » SQL & PL/SQL » SQL & PL/SQL » unable to drop a trigger (linux oracle 10.2.0.1)
- unable to drop a trigger [message #511568] Tue, 14 June 2011 00:50 Go to next message
rahuljain43
Messages: 2
Registered: June 2011
Junior Member
[Several messages merged by various moderators (LF being the last, so far)]


Hi

I have a trigger called DDL_TRIG which is used to audit all DDL level operation on the Database. The trigger script is as below:-

CREATE OR REPLACE TRIGGER ddl_trig
AFTER DDL
ON DATABASE

BEGIN
INSERT INTO ddl_log
(user_name, ddl_date, ddl_type,
object_type, owner,
object_name)
VALUES
(ora_login_user, SYSDATE, ora_sysevent,
ora_dict_obj_type, ora_dict_obj_owner,
ora_dict_obj_name);
END ddl_trig;

The Table DDL_LOG exists.Now i want to disable or drop this trigger and it wont allow us. While doing on one schema it fails and with below error:

when logged in schema1 and give drop or disable command it fails

ORA:04098:trigger schema2.ddl_trig is invalid and failed re-validation.

when logged in schema2 and give drop or disable command it fails

ORA:04098:trigger schema1.ddl_trig is invalid and failed re-validation.

can you please let us know how can we drop this trigger in both the schema's. This trigger is not allowing us to do any activity on the Database and causing lot of problem.


[Updated on: Tue, 14 June 2011 05:35] by Moderator

Report message to a moderator

- Re: DDL_TRIGGER [message #511571 is a reply to message #511568] Tue, 14 June 2011 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SYS to drop the trigger.
And if you are in 11g (this is why we ask to post the version), always create your trigger in DISABLE state, this will prevent you from this kind of problem.

In addition, why don't you use Oracle native audit for this instead of creating your own trigger?

Regards
Michel

[Updated on: Tue, 14 June 2011 00:58]

Report message to a moderator

- unable to drop a trigger [message #511581 is a reply to message #511568] Tue, 14 June 2011 02:58 Go to previous messageGo to next message
rahuljain43
Messages: 2
Registered: June 2011
Junior Member
Hi

I am using oracle 10g on linux server. The Db has been recently moved from AIX to Linux server.

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production



Now when i am trying to disable or drop a trigger it gives an error :

ORA:06531 and ORA:06512

Could you please provide me the solution.
- Re: unable to drop a trigger [message #511586 is a reply to message #511581] Tue, 14 June 2011 03:21 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

ORA-06531 Reference to uninitialized collection
Cause: An element or member function of a nested table or VARRAY was referenced (where an initialized collection is needed) without the collection having been initialized.

Action: Initialize the collection with an appropriate constructor or whole-object assignment.

so check it.

regards
Halim
- Re: unable to drop a trigger [message #511590 is a reply to message #511586] Tue, 14 June 2011 03:29 Go to previous messageGo to next message
Littlefoot
Messages: 21825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Check what, exactly?

This is how I imagine a test case: I have a table and a trigger:
SQL> create table test (col number);

Table created.

SQL> create or replace trigger trg_test
  2    before insert on test
  3    for each row
  4  begin
  5    null;
  6  end;
  7  /

Trigger created.

Now, drop a trigger:
SQL> drop trigger trg_test;

Trigger dropped.

SQL>

What does the DROP command have to do with "initializing the collection with an appropriate constructor etc."?


@rahuljain43, could you provide a similar test case? Copy-paste your SQL*Plus session.
- Re: unable to drop a trigger [message #511593 is a reply to message #511590] Tue, 14 June 2011 03:44 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member



@rahuljain43, whats your trigger code ?

regards
Halim

- Re: unable to drop a trigger [message #511594 is a reply to message #511581] Tue, 14 June 2011 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Could you please provide me the solution.

I already gave it to you 2 hours before in another topic you created.
Did you try? It is a good practice to feedback.

Regards
Michel
- Re: unable to drop a trigger [message #511600 is a reply to message #511568] Tue, 14 June 2011 04:12 Go to previous message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Why do you post the same question over and over without actually reading the replies?
Previous Topic: Functions
Next Topic: Hierarchy record manipultion
Goto Forum:
  


Current Time: Tue Jul 15 17:18:02 CDT 2025