Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0012693Openbravo ERPZ. Otherspublic2010-03-16 11:262010-05-18 12:12
Reporteradrianromero 
Assigned Toadrianromero 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version2.50MP12 
Target Version2.50MP17Fixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customerNo
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0012693: Review on delete triggers
DescriptionCopied ALO's mail

Fixing this issue [1], I've realized that triggers work in a slightly
different way between Oracle and PostrgreSQL:

-Oracle:
  1."before" triggers are executed
  2. "after" triggers are executed
  3. constraints are verified

-PostgreSQL
  1."before" triggers are executed
  2. constraints are verified
  3. "after" triggers are executed

This means that triggers to be executed on delete that are used to do
cascade deletions (as the one reported in the issue), in Oracle can be
defined as before or after, but in Postgre they will not work unless
they are "before".

Looking in the core code I've found these candidates to be reviewed:

grep -i 'delete\s*from' `grep -l 'fires="after".*delete="true"' *`
AD_REFERENCE_TRG2.xml: DELETE FROM AD_MODEL_OBJECT_MAPPING WHERE
AD_MODEL_OBJECT_ID IN (
AD_REFERENCE_TRG2.xml: DELETE FROM AD_MODEL_OBJECT WHERE ACTION = 'S'
AND AD_MODEL_OBJECT.AD_REFERENCE_ID = :OLD.AD_REFERENCE_ID;
AD_TAB_TRG2.xml: DELETE FROM AD_MODEL_OBJECT_MAPPING WHERE
AD_MODEL_OBJECT_ID IN (
AD_TAB_TRG2.xml: DELETE FROM AD_MODEL_OBJECT WHERE ACTION = 'W' AND
AD_MODEL_OBJECT.AD_TAB_ID = :OLD.AD_TAB_ID;
C_BPARTNER_TRG.xml: DELETE FROM AD_TreeNodeBP WHERE AD_Client_ID =
:old.AD_Client_ID AND Node_ID = :old.C_BPartner_ID;
C_INVOICELINETAX_TRG.xml: DELETE FROM C_INVOICETAX
C_INVOICELINE_TRG2.xml: DELETE FROM C_INVOICELINE_OFFER
C_INVOICELINE_TRG2.xml: DELETE FROM C_INVOICELINETAX WHERE
C_INVOICELINE_ID = :new.C_InvoiceLine_ID;
C_ORDERLINETAX_TRG.xml: DELETE FROM C_ORDERTAX
C_ORDERLINE_TRG2.xml: DELETE FROM C_ORDERLINE_OFFER
C_ORDERLINE_TRG2.xml: DELETE FROM C_ORDERLINETAX WHERE C_ORDERLINE_ID
= :new.C_OrderLine_ID;
M_PRODUCT_TRG.xml: DELETE FROM AD_TreeNodePR WHERE AD_Client_ID =
:old.AD_Client_ID AND Node_ID = :old.M_Product_ID;

I will review and fix the platform ones (ad_reference_trg2,
ad_tab_trg2), and I'd like to someone else to review the other ones.

You also should take this note into account when creating new triggers.

[1] https://issues.openbravo.com/view.php?id=12601 [^]
Steps To Reproduce
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
related to defect 00126012.50MP14 closed alostale Is not possible to delete a window tab using postgresql 
depends on defect 0012771pi closed adrianromero New trigger C_BPartner_TRG2 makes the build fail 
related to backport 00119332.40MP16 closed adrianromero It is not possible to delete a Header in Goods Movements 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2010-03-16 11:26adrianromeroNew Issue
2010-03-16 11:26adrianromeroAssigned To => adrianromero
2010-03-16 11:26adrianromeroOBNetwork customer => No
2010-03-16 11:27adrianromeroRelationship addedrelated to 0012601
2010-03-16 11:32adrianromeroStatusnew => scheduled
2010-03-16 11:32adrianromerofix_in_branch => pi
2010-03-17 12:55adrianromeroRelationship addedrelated to 0011933
2010-03-17 13:00rafarodaIssue Monitored: rafaroda
2010-03-17 18:17adrianromeroversion => 2.50MP12
2010-03-17 18:17adrianromerofix_in_branchpi =>
2010-03-22 18:52hgbotCheckin
2010-03-22 18:52hgbotNote Added: 0025654
2010-03-22 18:52hgbotStatusscheduled => resolved
2010-03-22 18:52hgbotResolutionopen => fixed
2010-03-22 18:52hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/419a4aedd233a3e1697c4c610cbc45d61eee39ce [^]
2010-03-22 18:54adrianromeroNote Added: 0025655
2010-03-23 11:50adrianromeroRelationship addeddepends on 0012771
2010-04-15 11:32plujanNote Added: 0026238
2010-04-15 11:32plujanStatusresolved => closed
2010-04-16 00:00anonymoussf_bug_id0 => 2987966
2010-04-19 21:10hudsonbotCheckin
2010-04-19 21:10hudsonbotNote Added: 0026368
2010-05-18 12:12gorka_gilTarget Version2.50MP15 => 2.50MP17

Notes
(0025654)
hgbot   
2010-03-22 18:52   
Repository: erp/devel/pi
Changeset: 419a4aedd233a3e1697c4c610cbc45d61eee39ce
Author: Adrián Romero <adrianromero <at> openbravo.com>
Date: Mon Mar 22 18:59:51 2010 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/419a4aedd233a3e1697c4c610cbc45d61eee39ce [^]

Fixes issue 0012693: Review on delete triggers

---
M src-db/database/model/triggers/C_BPARTNER_TRG.xml
M src-db/database/model/triggers/M_PRODUCT_TRG.xml
A src-db/database/model/triggers/C_BPARTNER_TRG2.xml
A src-db/database/model/triggers/M_PRODUCT_TRG2.xml
---
(0025655)
adrianromero   
2010-03-22 18:54   
The solution implemented has been to move only in the required triggers, the DELETE sentence involved, to a new "before" trigger.

To test these modifications perform different creation and deletion of records in the master table products and business partners.
(0026238)
plujan   
2010-04-15 11:32   
Verified in PI
(0026368)
hudsonbot   
2010-04-19 21:10   
A changeset related to this issue has been promoted to main after passing a series of tests and an OBX has been generated:

Changeset: http://code.openbravo.com/erp/devel/main/rev/419a4aedd233 [^]
Merge Changeset: http://code.openbravo.com/erp/devel/main/rev/91d98bda46c1 [^]
Tests: http://builds.openbravo.com/view/devel-int/ [^]
OBX: http://builds.openbravo.com/erp/core/obx/OpenbravoERP-2.50CI.17088.obx [^]