Schema compare, error in sync script for disabled trigger

Toad for Oracle - Beta
1

In the sync script when comparing schemas, an incorrect statement is generated for disabled triggers with a WHEN clause:

CREATE OR REPLACE TRIGGER trigger_name
   BEFORE DELETE
   ON table_name
   FOR EACH ROW
   WHEN(when_clause)   DISABLE
BEGIN
   ...

The DISABLE keyword must precede the WHEN clause:


BR
Dirk

4

Hi Dirk,

Sorry I spoke too soon on my first reply.

As far as I can tell, we are putting DISABLE where it belongs (before WHEN).

I am still trying to get it to come up somewhere else.

-John

5

Lol, and I thought I was hallucinating. :slight_smile:
You deleted your first post and I was slightly confused. :wink:

The version I'm using in this case is
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

6

I can only get DISABLE to show up in the right place. Can you send your trigger source?

image
image716×875 36.2 KB

7

Maybe it's because of the comment. This trigger

CREATE OR REPLACE TRIGGER tr_test
   AFTER DELETE
   ON address
   FOR EACH ROW
   /******************************************************************************
      This is a comment.
   ********************************************************************************/
   DISABLE
   WHEN(1 = 1)
BEGIN
   NULL;
END tr_test;
/

becomes

CREATE OR REPLACE TRIGGER tr_test
   AFTER DELETE
   ON address
   FOR EACH ROW
   /******************************************************************************
      This is a comment.
   ********************************************************************************/
   WHEN(1 = 1) DISABLE
BEGIN
   NULL;
END tr_test;
/

in the sync script.

Dirk

8

Ah. Yes, it only happens when the comment is in the header AND when you have a WHEN clause

Header comments are tricky because they exist in DBA_TRIGGERS but not DBA_SOURCE. There is a reason why we get the source from DBA_SOURCE but I can't remember what it is at the moment.

If you move your comment so it comes after the BEGIN, then the problem will go away.

9

Good to know. Unfortunately, we have about 600 triggers each in various schemas, basically all of which have the comment in the header.
Luckily most of them are active :wink: