1

Can't understand why Oracle did not fire any DDL trigger.

>SELECT * FROM V$VERSION 
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

Example from https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-triggers.html#GUID-6CF3A208-0BE7-45FF-928C-A755526933D0

STUDENT> CREATE OR REPLACE TRIGGER drop_trigger
           BEFORE DROP ON student.SCHEMA
           BEGIN
             RAISE_APPLICATION_ERROR (
               num => -20000,
               msg => 'Cannot drop object');
           END;
[2022-04-29 11:28:09] completed in 293 ms
STUDENT> create table  tbl1 (a integer)
[2022-04-29 11:28:16] completed in 124 ms
STUDENT> drop table tbl1
[2022-04-29 11:28:17] completed in 155 ms

user and schema - both "Student";

Absolutely the same with DDL-logging example from http://www.dba-oracle.com/sf_ora_04098_trigger_string_string_is_invalid_and_failed_re_validation.htm - no any results for

after ddl on schema 
after ddl on student.schema 
after ddl on database   -- created from name of SYS user

All triggers a visible and shown as ENABLED at all_triggers . There no problem-related message at user_errors or all_errors

Any suggestion?

potapuff
  • 1,839
  • 4
  • 18
  • 36
  • What is the purpose of this trigger? If you want to prevent the drop, then simply revoke the privilege for it. – Wernfried Domscheit Apr 29 '22 at 08:44
  • I tried the trigger and it works, try to connect to the schema u want to put the trigger on and just write `before drop on schema` instead of `BEFORE DROP ON student.SCHEMA`. – Toni Antunović Apr 29 '22 at 09:11
  • @WernfriedDomscheit the privilege to drop objects cannot be revoked from the owner of the objects. Only the `DROP ALL ...` system-level privileges can be revoked., assuming they have been granted. – pmdba Apr 29 '22 at 09:47
  • @WernfriedDomscheit "What is the purpose of this trigger?" - this is just example from documentation - it MUST work but did not work – potapuff Apr 29 '22 at 09:57
  • @toni-antunović for this and second example for question I have try all variants "ON SCHEMA", "ON name-of-schema.SCHEMA" and "ON DATABASE" - same results - triggers present in all_triggers but still no effect – potapuff Apr 29 '22 at 10:01
  • @potapuff Hard to help you when I cant replicate the problem, can you try to create the trigger on sys with `before drop on schema` and see if it works. – Toni Antunović Apr 29 '22 at 11:31
  • @ToniAntunović can't do for SYS because of " ORA-30510: system triggers cannot be defined on the schema of SYS user" but "BEFORE DROP ON student.SCHEMA" - from sys name has no effect . And "BEFORE DROP ON SCHEMA" from student name - same has no effect. – potapuff Apr 29 '22 at 11:50
  • @potapuff Sorry I forgot about ORA-30510, what I meant to ask you to try is that u create the trigger on a schema where u have all the privileges. – Toni Antunović Apr 29 '22 at 12:03
  • @ToniAntunović second part about that case. User "Student", schema "student", And "BEFORE DROP ON SCHEMA" from student name - same has no effect. – potapuff Apr 29 '22 at 13:02

0 Answers0