details: https://code.openbravo.com/erp/devel/pi/rev/f730600d5079
changeset: 31499:f730600d5079
user: Inigo Sanchez <inigo.sanchez <at> openbravo.com>
date: Mon Feb 06 12:56:44 2017 +0100
summary: Fixed issue 34833: audit triggers can have naming clashes
The problem occurs because of the way audit trigger name is generated,
which doesn't validate there is no function nor trigger with the same name.
In order to fix this problem a validation is added. Now, this problem has
been managed properly.
diffstat:
src-db/database/model/postscript-Oracle.sql | 24 +++++++++++++++---
src-db/database/model/postscript-PostgreSql.sql | 32 ++++++++++++++++++------
2 files changed, 44 insertions(+), 12 deletions(-)
diffs (152 lines):
diff -r 87f935ef1dde -r f730600d5079 src-db/database/model/postscript-Oracle.sql
--- a/src-db/database/model/postscript-Oracle.sql Mon Feb 06 12:35:12
2017 +0100
+++ b/src-db/database/model/postscript-Oracle.sql Mon Feb 06 12:56:44
2017 +0100
@@ -580,7 +580,7 @@
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
-* All portions are Copyright (C) 2009-2016 Openbravo SLU
+* All portions are Copyright (C) 2009-2017 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************/
@@ -595,7 +595,7 @@
cur_triggers RECORD;
cur_tables RECORD;
cur_cols RECORD;
- triggerName varchar2(30);
+ targetTriggerName varchar2(30);
recordIdName varchar2(30);
datatype varchar2(30);
clientinfo number;
@@ -603,6 +603,9 @@
created number :=0;
v_message varchar2(500);
v_isObps number;
+ isavailablename number :=0;
+ suffixNumber number :=0;
+ numberCharsToRemove number;
FUNCTION splitClob(code clob, splitcode out dbms_sql.varchar2s ) RETURN
number AS
@@ -644,7 +647,19 @@
and dataOriginType = 'Table'
order by tablename) loop
dbms_output.put_line('Creating trigger for table '||cur_tables.tablename);
- triggerName := 'AU_'||SUBSTR(cur_tables.tablename,1,23)||'_TRG';
+ targetTriggerName := 'AU_'||SUBSTR(cur_tables.tablename,1,23)||'_TRG';
+ LOOP
+ select count(*)
+ into isavailablename
+ from user_triggers
+ where upper(trigger_name) = upper(targetTriggerName);
+
+ EXIT WHEN isavailablename = 0;
+
+ suffixNumber := suffixNumber + 1;
+ numberCharsToRemove :=LENGTH(suffixNumber);
+ targetTriggerName :=
'AU_'||SUBSTR(cur_tables.tablename,1,23-numberCharsToRemove)||''||suffixNumber||'_TRG';
+ END LOOP;
select count(*) into clientinfo
from dual
@@ -662,7 +677,7 @@
where ad_table_id = cur_tables.ad_table_id
and iskey='Y';
- code := 'create or replace TRIGGER '||triggerName||'
+ code := 'create or replace TRIGGER '||targetTriggerName||'
AFTER INSERT OR UPDATE OR DELETE
ON '|| cur_tables.tablename||' FOR EACH ROW
DECLARE
@@ -824,6 +839,7 @@
DBMS_SQL.close_cursor(cursor_id);
created := created + 1;
+ suffixNumber :=0;
end loop;
v_Message := '@Deleted@: '||deleted||' @Created@: '||created;
diff -r 87f935ef1dde -r f730600d5079
src-db/database/model/postscript-PostgreSql.sql
--- a/src-db/database/model/postscript-PostgreSql.sql Mon Feb 06 12:35:12
2017 +0100
+++ b/src-db/database/model/postscript-PostgreSql.sql Mon Feb 06 12:56:44
2017 +0100
@@ -375,7 +375,7 @@
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
-* All portions are Copyright (C) 2009-2016 Openbravo SLU
+* All portions are Copyright (C) 2009-2017 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************/
@@ -383,7 +383,7 @@
cur_triggers RECORD;
cur_tables RECORD;
cur_cols RECORD;
- triggerName VARCHAR(30);
+ targetTriggerName VARCHAR(30);
recordIdName VARCHAR(30);
datatype VARCHAR(30);
clientinfo NUMERIC;
@@ -391,6 +391,9 @@
created NUMERIC :=0;
v_message VARCHAR(500);
v_isObps NUMERIC;
+ isavailablename NUMERIC :=0;
+ suffixNumber NUMERIC :=0;
+ numberCharsToRemove NUMERIC;
BEGIN
select count(*)
into v_isObps
@@ -418,8 +421,21 @@
and dataOriginType = 'Table'
order by tablename) loop
- triggerName := 'AU_'||SUBSTR(cur_tables.tablename,1,23)||'_TRG';
- raise notice '%', triggerName;
+ targetTriggerName := 'AU_'||SUBSTR(cur_tables.tablename,1,23)||'_TRG';
+ LOOP
+ select count(*)
+ into isavailablename
+ from user_triggers u
+ where upper(trigger_name) = upper(targetTriggerName);
+
+ EXIT WHEN isavailablename = 0;
+
+ suffixNumber := suffixNumber + 1;
+ raise notice '%', targetTriggerName || ' is already exists.
Renaming...';
+ numberCharsToRemove :=LENGTH(CAST(suffixNumber AS VARCHAR));
+ targetTriggerName :=
'AU_'||SUBSTR(cur_tables.tablename,1,23-numberCharsToRemove)||''||suffixNumber||'_TRG';
+ END LOOP;
+ raise notice '%', targetTriggerName;
select count(*) into clientinfo
from dual
@@ -437,7 +453,7 @@
where ad_table_id = cur_tables.ad_table_id
and iskey='Y';
- code := 'create or replace FUNCTION '||triggerName||'()
+ code := 'create or replace FUNCTION '||targetTriggerName||'()
RETURNS trigger AS
$BODY$
DECLARE
@@ -611,15 +627,15 @@
EXECUTE(code);
code :=
- 'CREATE TRIGGER '||triggerName||'
+ 'CREATE TRIGGER '||targetTriggerName||'
BEFORE INSERT OR UPDATE OR DELETE
ON '||cur_cols.table_name||'
FOR EACH ROW
- EXECUTE PROCEDURE '||triggerName||'()';
+ EXECUTE PROCEDURE '||targetTriggerName||'()';
execute(code);
created := created + 1;
-
+ suffixNumber :=0;
end loop;
v_Message := '@Deleted@: '||deleted||' @Created@: '||created;
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
_______________________________________________
Openbravo-commits mailing list
Openbravo-commits@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
