Schema compare, error in sync script for disabled trigger

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

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

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

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

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

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.

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: