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  |
 |
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   |
 |
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
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Jul 15 17:18:02 CDT 2025
|