# Rebuild table trigger definition **URL:** \ **Category:** Toad for Oracle & DevOps Toolkit\ **Created:** [July 24, 2017, 4:28am UTC](https://forums.toadworld.com/t/rebuild-table-trigger-definition/39746 "2017-07-24T04:28:32Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [July 24, 2017, 4:28am UTC](https://forums.toadworld.com/t/rebuild-table-trigger-definition/39746/1 "2017-07-24T04:28:32Z")
Hi, Toad 12.12 x64. Rebuild table, in trigger definition is changed (formatted) so when you compare source and new version it shows differences. it has no functional but formatting differences. However, it should stay as it was-without formatting from Toad. Possible in new release? Or at least to have a option “leave sorce as is” or “do not format source” …you know what I mean. thx Damir ---
**Author:** ![ty.cheng](https://avatars.discourse-cdn.com/v4/letter/t/bbce88/32.png) [@ty.cheng](https://forums.toadworld.com/u/ty.cheng)\ **Post date:** [July 24, 2017, 4:59pm UTC](https://forums.toadworld.com/t/rebuild-table-trigger-definition/39746/2 "2017-07-24T16:59:40Z")
Over a year now I have not yet resolve issue with toad schema browser is running slow in oracle 12c RAC database but running ok with oracle 10g RAC database. Does anyone come up with any resolution to this issue yet. ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [July 24, 2017, 10:08pm UTC](https://forums.toadworld.com/t/rebuild-table-trigger-definition/39746/3 "2017-07-24T22:08:47Z")
Please open new thread. This has nothing with mine topic. ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [July 25, 2017, 5:40am UTC](https://forums.toadworld.com/t/rebuild-table-trigger-definition/39746/4 "2017-07-25T05:40:17Z")
Damir, as far as I can tell, the trigger formatting is not changed. The only difference that I could find is the addition of the schema name before the table name in the trigger description. I have made a change so that we don’t add this unless necessary (that is, unless you are rebuilding a table in a different schema) ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [July 26, 2017, 3:35am UTC](https://forums.toadworld.com/t/rebuild-table-trigger-definition/39746/5 "2017-07-26T03:35:42Z")
Hi John, Here is example where trigger source is different (and not only in schema name part): org: create or replace TRIGGER PURGE\_NOT\_USED\_PREPAID\_RE BEFORE INSERT ON APE1\_REJECTED\_EVENT FOR EACH ROW when (NEW.cycle\_code = '80') DECLARE vResult NUMBER(9); BEGIN vResult := 0; IF (:NEW.resource\_type = 'I') THEN SELECT COUNT(_) INTO vResult FROM XX WHERE COL1 = :NEW.resource\_value OR COL1 = 'PREBOB' OR COL1 = 'B'; ELSIF (:NEW.resource\_type = 'C') THEN SELECT COUNT(_) INTO vResult FROM YY WHERE col1 = :NEW.resource\_value or col1 = 'PREBOB' or col1 = 'B'; END IF; ``` IF (vResult > 0) THEN :NEW.RESOURCE_TYPE := 'PRE'; END IF; ``` END; / here is generated one -- Rebuild triggers for the new table. DROP TRIGGER Q383211.PURGE\_NOT\_USED\_PREPAID\_RE; CREATE OR REPLACE TRIGGER Q383211.PURGE\_NOT\_USED\_PREPAID\_RE BEFORE INSERT ON Q383211.APE1\_REJECTED\_EVENT FOR EACH ROW **WHEN (** **NEW.cycle\_code = '80'** \*\* )\*\* DECLARE vResult NUMBER(9); BEGIN vResult := 0; IF (:NEW.resource\_type = 'I') THEN SELECT COUNT(_) INTO vResult FROM XX WHERE COL1 = :NEW.resource\_value OR COL1 = 'PREBOB' OR COL1 = 'B'; ELSIF (:NEW.resource\_type = 'C') THEN SELECT COUNT(_) INTO vResult FROM YY WHERE col1 = :NEW.resource\_value or col1 = 'PREBOB' or col1 = 'B'; END IF; ``` IF (vResult > 0) THEN :NEW.RESOURCE_TYPE := 'PRE'; END IF; ``` END; / SHOW ERRORS; or as original picture from Toad (read only script part) ![trigger source.png](https://global.discourse-cdn.com/quest/original/2X/e/ec848bb461ad9820fcb3a0622fe601f01707408f.png) Brg Damir ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [July 26, 2017, 3:38am UTC](https://forums.toadworld.com/t/rebuild-table-trigger-definition/39746/6 "2017-07-26T03:38:30Z")
and later when I was comparing line by line from source and “old source” … i got more than dozen of differences (all lines were different by line number compassion) …and seems to be only one. ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [July 26, 2017, 3:42am UTC](https://forums.toadworld.com/t/rebuild-table-trigger-definition/39746/7 "2017-07-26T03:42:19Z")
And here is the same error confirmed from last beta (12.12.0.2) ![4403.trigger source.png](https://global.discourse-cdn.com/quest/original/2X/7/777f6ed15b4adf929cff91669d36f4769ffb121f.png) ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [July 26, 2017, 5:19am UTC](https://forums.toadworld.com/t/rebuild-table-trigger-definition/39746/8 "2017-07-26T05:19:22Z")
Oh, the WHEN part, OK, yeah, I see that Toad is “fixing” the whitespace around it. I’ll fix that. ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [July 26, 2017, 11:10pm UTC](https://forums.toadworld.com/t/rebuild-table-trigger-definition/39746/9 "2017-07-26T23:10:01Z")
Hi John, Thx for reply. Indeed Toad is adding extra lines as you said. But for space I could not reproduce your case. Played with spaces in several ways in your example and do not see that Oracle is having problems. If you could show pics or real test where you saw spacing problem, so I can see what did I missed. I am on 11.2.0.3 x64 DB. Brg Damir Brg,\*\* \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\*\* **Damir Vadas** [http://damir-vadas.blogspot.com](http://damir-vadas.blogspot.com) It does not matter what you have in your life, but who! **Razmislite o čuvanju okoliša prije nego odštampate ovaj e-mail** **Please consider the environment before printing this email** On Wed, Jul 26, 2017 at 3:29 PM, John Dorlon [bounce-jdorlon@toadworld.com](mailto:bounce-jdorlon@toadworld.com) wrote: > **[RE: Rebuild table trigger definition](http://www.toadworld.com/products/toad-for-oracle/f/10/p/34047/66849#66849)** > Reply by John Dorlon > Oh, the WHEN part, OK. > Oracle doesn't always preserve the formatting around that part, but I see that Toad is adding a line feed before and after. I'll remove it. > As a test, try creating this trigger with different spacing and line feeds around the "(1=1) " part. Then look in dba\_triggers.WHEN\_CLAUSE. You'll see that the spacing is not always preserved. > CREATE OR REPLACE TRIGGER JDORLON.TRG\_TEST1 > BEFORE DELETE > ON JDORLON.DEPT > REFERENCING NEW AS New OLD AS Old > FOR EACH ROW > WHEN (1=1) > begin > null; > end; > / > To reply, please reply-all to this email. > > [Stop receiving emails](http://www.toadworld.com/products/toad-for-oracle/f/10/t/34047?subscribe=false) on this subject. > > Or [Unsubscribe from Toad for Oracle Forum](http://www.toadworld.com/products/toad-for-oracle/f/10?subscribe=false) notifications altogether. > > Toad for Oracle - Discussion Forum > [Flag](http://www.toadworld.com/products/toad-for-oracle/f/10/p/34047/66849?AbuseContentId=31e13fa0-2c5a-47a2-80f7-9b91bb3bbced&AbuseContentTypeId=f586769b-0822-468a-b7f3-a94d480ed9b0&AbuseFlag=true) this post as spam/abuse. ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [July 27, 2017, 4:49am UTC](https://forums.toadworld.com/t/rebuild-table-trigger-definition/39746/10 "2017-07-27T04:49:33Z")
try this trigger. add spaces or lines before or after 1=1, but inside the parens. Prior versions of Toad would remove all that whitespace and then define its own whitespace… Now it will be preserved. Oracle eliminates the whitespace outside the parens. CREATE OR REPLACE TRIGGER JDORLON.DEPT\_TEST BEFORE DELETE ON JDORLON.DEPT REFERENCING NEW AS New OLD AS Old FOR EACH ROW WHEN (1=1) begin null; end; / ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [July 27, 2017, 6:40am UTC](https://forums.toadworld.com/t/rebuild-table-trigger-definition/39746/11 "2017-07-27T06:40:36Z")
I have made a test. Here are the results: SQL\*Plus: Release 11.2.0.3.0 Production on Thu Jul 27 15:38:48 2017 Copyright © 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning option ## BANNER Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for HPUX: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production Elapsed: 00:00:00.02 15:38:49 Q383211@SMOBCST1\> 15:38:49 Q383211@SMOBCST1\>CREATE OR REPLACE TRIGGER TRG\_TEST1 15:38:49 2 BEFORE DELETE 15:38:49 3 ON DEPARTMENTS 15:38:49 4 REFERENCING NEW AS New OLD AS Old 15:38:49 5 FOR EACH ROW 15:38:49 6 WHEN (1 = 1) 15:38:49 7 BEGIN 15:38:49 8 NULL; 15:38:49 9 END; 15:38:49 10 / Trigger created. Elapsed: 00:00:00.02 15:38:49 Q383211@SMOBCST1\> 15:38:49 Q383211@SMOBCST1\>select ‘"’|| WHEN\_CLAUSE|| ‘"’ from user\_triggers where table\_name=‘DEPARTMENTS’ and trigger\_name =‘TRG\_TEST1’; ## ‘"’||WHEN\_CLAUSE||’"’ “1 = 1” Elapsed: 00:00:00.02 15:38:49 Q383211@SMOBCST1\> 15:38:49 Q383211@SMOBCST1\> 15:38:49 Q383211@SMOBCST1\>CREATE OR REPLACE TRIGGER TRG\_TEST2 15:38:49 2 BEFORE DELETE 15:38:49 3 ON DEPARTMENTS 15:38:49 4 REFERENCING NEW AS New OLD AS Old 15:38:49 5 FOR EACH ROW 15:38:49 6 WHEN ( 1 = 1 ) 15:38:49 7 BEGIN 15:38:49 8 NULL; 15:38:49 9 END; 15:38:49 10 / Trigger created. Elapsed: 00:00:00.03 15:38:49 Q383211@SMOBCST1\> 15:38:49 Q383211@SMOBCST1\>select ‘"’|| WHEN\_CLAUSE|| ‘"’ from user\_triggers where table\_name=‘DEPARTMENTS’ and trigger\_name =‘TRG\_TEST2’; ## ‘"’||WHEN\_CLAUSE||’"’ " 1 = 1 " Elapsed: 00:00:00.02 15:38:49 Q383211@SMOBCST1\> 15:38:49 Q383211@SMOBCST1\> 15:38:49 Q383211@SMOBCST1\>CREATE OR REPLACE TRIGGER TRG\_TEST3 15:38:49 2 BEFORE DELETE 15:38:49 3 ON DEPARTMENTS 15:38:49 4 REFERENCING NEW AS New OLD AS Old 15:38:49 5 FOR EACH ROW 15:38:49 6 WHEN ( 1 =1) 15:38:49 7 BEGIN 15:38:49 8 NULL; 15:38:49 9 END; 15:38:49 10 / Trigger created. Elapsed: 00:00:00.04 15:38:49 Q383211@SMOBCST1\> 15:38:49 Q383211@SMOBCST1\>select ‘"’|| WHEN\_CLAUSE|| ‘"’ from user\_triggers where table\_name=‘DEPARTMENTS’ and trigger\_name =‘TRG\_TEST3’; ## ‘"’||WHEN\_CLAUSE||’"’ " 1 =1" Elapsed: 00:00:00.02 15:38:49 Q383211@SMOBCST1\> 15:38:49 Q383211@SMOBCST1\> 15:38:49 Q383211@SMOBCST1\>CREATE OR REPLACE TRIGGER TRG\_TEST4 15:38:49 2 BEFORE DELETE 15:38:49 3 ON DEPARTMENTS 15:38:49 4 REFERENCING NEW AS New OLD AS Old 15:38:49 5 FOR EACH ROW 15:38:49 6 WHEN (1= 1 ) 15:38:49 7 BEGIN 15:38:49 8 NULL; 15:38:49 9 END; 15:38:49 10 / Trigger created. Elapsed: 00:00:00.05 15:38:49 Q383211@SMOBCST1\> 15:38:49 Q383211@SMOBCST1\>select ‘"’|| WHEN\_CLAUSE|| ‘"’ from user\_triggers where table\_name=‘DEPARTMENTS’ and trigger\_name =‘TRG\_TEST4’; ## ‘"’||WHEN\_CLAUSE||’"’ "1= 1 " Elapsed: 00:00:00.02 15:38:49 Q383211@SMOBCST1\> Do not understand part “Oracle removes space outside of parenthesis” However, if all is clear to you…I can find this thread closed. 🙂 ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [July 27, 2017, 6:44am UTC](https://forums.toadworld.com/t/rebuild-table-trigger-definition/39746/12 "2017-07-27T06:44:39Z")
15:43:34 Q383211@SMOBCST1\>select \* from user\_source where name =‘TRG\_TEST4’; NAME TYPE LINE TEXT * * * TRG\_TEST4 TRIGGER 1 TRIGGER TRG\_TEST4 TRG\_TEST4 TRIGGER 2 BEFORE DELETE TRG\_TEST4 TRIGGER 3 ON DEPARTMENTS TRG\_TEST4 TRIGGER 4 REFERENCING NEW AS New OLD AS Old TRG\_TEST4 TRIGGER 5 FOR EACH ROW TRG\_TEST4 TRIGGER 6 WHEN (1= 1 ) TRG\_TEST4 TRIGGER 7 BEGIN TRG\_TEST4 TRIGGER 8 NULL; TRG\_TEST4 TRIGGER 9 END; 9 rows selected. Elapsed: 00:00:00.00 15:43:34 Q383211@SMOBCST1\> ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [July 27, 2017, 6:49am UTC](https://forums.toadworld.com/t/rebuild-table-trigger-definition/39746/13 "2017-07-27T06:49:22Z")
![4452.trigger source.png](https://global.discourse-cdn.com/quest/original/2X/f/f789df9e401a0e7b9ad38d9f15bc0d1bcfd53fb3.png)