4 Replies Latest reply: Mar 24, 2014 4:37 AM by Bhushan53 RSS

    Master-Detail Block : how to save changes only if detail block has a record

    Tabit7

      Hi everyone,

       

      I face an issue in oracle form and need your help.

      I have a master-detail block and i need to show an error message to user if he tries to save the changes and there is no record in the detail block.

      I am new with oracle form and don't know how to do that, do you have any idea?

       

      Thanks.

        • 1. Re: Master-Detail Block : how to save changes only if detail block has a record
          Zlatko Sirotic

          Usually, the implementation is on the client side (Forms, ADF ...).

           

          This is my 12 year old PL/SQL tip (originaly published: otn.oracle.com/oramag/code - tip 24.03.2002)

          which shows the implementation on the database side.

           

          Regards

           

           

          Solving "COMMIT business rules" on the database server

           

          In the last few years, software development is focusing towards three-tier applications, mainly by separating business rules layer from presentation and data layer.

          There are two approaches concerning the physical implementation of the business rules layer, and they are:

          1. On the application server

          2. On the database server

           

          First approach is probably better if the application have to access different databases.

          But if application accesses only one database (like Oracle) it probably would be better to implement business rules on the database.

          Even (very sophisticated) Oracle CDM RuleFrame option of the Headstart Oracle Designer implements business rules layer on the database (at least for now).

           

          If we don't have Oracle RuleFrame, we can try to make our own implementation of the business rules on the database.

          But there are some business rules that were not entirely supported by the database, without the cooperation of the client side.

          Probably the simplest example of that rule is: "Instance of Master must have at least one depending instance of Detail." (e.g. "DEPT has to have at least one EMP.").

          We could call those rules "COMMIT rules", because they can be evaluated only before committing the transaction, and not at the execution of some DML command.

          As we know that Oracle database does not have (and probably never will) some sort of BEFORE COMMIT trigger (which would help solving the "COMMIT rules"),

          we can try to solve them in (at least) those two ways:

           

           

          1. Solving "COMMIT rules" without Deferred Constraint Checking

           

          The possible solution on the version 7 of the Oracle database is to add a field like br_valid (br for Business Rule)

          to the Master table and to insure with the database triggers that the row that has the value of "T" (True) for the br_valid field complies with the "COMMIT rule".

           

          E.g. we add the br_valid field to the DEPT table and fill it with the correct value at the beginning:

           

          ALTER TABLE dept

             ADD (br_valid CHAR (1) DEFAULT 'F' NOT NULL,

                  CONSTRAINT dept_br_valid_ck CHECK (br_valid IN ('T', 'F')))

          /

           

          UPDATE dept

             SET br_valid = 'T'

          WHERE EXISTS (SELECT 1 FROM emp WHERE emp.deptno = dept.deptno)

          /

           

          After that we add triggers to the DEPT and EMP tables:

           

          CREATE OR REPLACE TRIGGER bir_dept

            BEFORE INSERT ON dept

            FOR EACH ROW

          BEGIN

             :NEW.br_valid := 'F';

          END;

          /

           

          CREATE OR REPLACE TRIGGER bur_dept

            BEFORE UPDATE ON dept

            FOR EACH ROW

          BEGIN

             IF :OLD.deptno <> :NEW.deptno THEN

                RAISE_APPLICATION_ERROR (-20001, 'Can''t change deptno in DEPT!');

             END IF;

           

             IF :OLD.br_valid = 'F' AND :NEW.br_valid = 'T' THEN

                DECLARE

                   l_dummy NUMBER (1, 0);

                   CURSOR c_emp IS

                      SELECT 1 FROM emp WHERE deptno = :OLD.deptno;

                BEGIN

                   OPEN c_emp;

                   FETCH c_emp INTO l_dummy;

                   IF c_emp%NOTFOUND THEN

                      CLOSE c_emp;

                      RAISE_APPLICATION_ERROR (-20002, 'DEPT must have EMPs!');

                   END IF;

                   CLOSE c_emp;

                END;

             END IF;

          END;

          /

           

          CREATE OR REPLACE TRIGGER aur_emp

            AFTER UPDATE ON emp

            FOR EACH ROW

          BEGIN

             IF NVL (:OLD.deptno, 0) <>  NVL (:NEW.deptno, 0) THEN

                UPDATE dept

                   SET br_valid = 'F'

                 WHERE deptno = :OLD.deptno

                   AND br_valid = 'T';

             END IF;

          END;

          /

           

          CREATE OR REPLACE TRIGGER adr_emp

            AFTER DELETE ON emp

            FOR EACH ROW

          BEGIN

             UPDATE dept

                SET br_valid = 'F'

              WHERE deptno = :OLD.deptno

                AND br_valid = 'T';

          END;

          /

           

          If we use Forms development tools, then in the Forms trigger POST-FORMS-COMMIT (which fires immediately before database COMMIT command) we can add the following code:

           

          UPDATE dept

             SET br_valid = 'T'

          WHERE deptno := :dept.deptno

             AND br_valid = 'F';

           

          If Forms (or some other client tool) didn't give that command before transaction COMMIT, at the database could have been left DEPT table rows with a value "F" (False).

          So, in this case, client has to cooperate with the database.

           

           

          2. Solving "COMMIT rules" with Deferred Constraint Checking (possible on the version >= 8.0 database)

           

          Method that allows solving "COMMIT rules" completely on the database level consists of the possibility of delaying the checking of the declarative constraints

          (NOT NULL, Primary Key, Unique Key, Foreign Key, Check Constraints) until the commit.

          That possibility was introduced first in the version 8.0.

          We emphasize that the execution of the triggers cannot be delayed.

           

          E.g. we add the field "num_emps" to the DEPT table, which always has the value of the number of the belonging EMP rows and add DEFERRED CK which uses the values from that field:

           

          ALTER TABLE dept ADD num_emps NUMBER DEFAULT 0 NOT NULL

          /

           

          UPDATE dept

             SET num_emps = (SELECT COUNT (*) FROM emp WHERE emp.deptno = dept.deptno)

          /

           

          DELETE dept WHERE num_emps = 0

          /

           

          ALTER TABLE dept ADD CONSTRAINT dept_num_emps_ck CHECK (num_emps > 0) INITIALLY DEFERRED

          /

           

          Triggers that insure the solving of the server side "COMMIT rules" are fairly simple.

          We need a packed variable that is set and reset in the EMP triggers and those value is read in the bur_dept trigger

          (of course, we could have place the variable in the package specification and change/read it directly, thus not needing the package body, but this is a "cleaner" way to do it):

           

          CREATE OR REPLACE PACKAGE pack IS

             PROCEDURE set_flag;

             PROCEDURE reset_flag;

             FUNCTION dml_from_emp RETURN BOOLEAN;

          END;

          /

           

          CREATE OR REPLACE PACKAGE BODY pack IS

             m_dml_from_emp BOOLEAN := FALSE;

           

             PROCEDURE set_flag IS

             BEGIN

                m_dml_from_emp := TRUE;

             END;

           

             PROCEDURE reset_flag IS

             BEGIN

                m_dml_from_emp := FALSE;

             END;

           

             FUNCTION dml_from_emp RETURN BOOLEAN IS

             BEGIN

                RETURN m_dml_from_emp;

             END;

          END;

          /

           

          CREATE OR REPLACE TRIGGER bir_dept

            BEFORE INSERT ON dept

            FOR EACH ROW

          BEGIN

             :NEW.num_emps := 0;

          END;

          /

           

          CREATE OR REPLACE TRIGGER bur_dept

            BEFORE UPDATE ON dept

            FOR EACH ROW

          BEGIN

             IF :OLD.deptno <> :NEW.deptno THEN

                RAISE_APPLICATION_ERROR (-20001, 'Can''t change deptno in DEPT!');

             END IF;

           

             -- only EMP trigger can change "num_emps" column

             IF NOT pack.dml_from_emp THEN

                :NEW.num_emps := :OLD.num_emps;

             END IF;

          END;

          /

           

          CREATE OR REPLACE TRIGGER air_emp

            AFTER INSERT ON emp

            FOR EACH ROW

          BEGIN

             pack.set_flag;

           

             UPDATE dept

                SET num_emps = num_emps + 1

              WHERE deptno = :NEW.deptno;

           

             pack.reset_flag;

          END;

          /

           

          CREATE OR REPLACE TRIGGER aur_emp

            AFTER UPDATE ON emp

            FOR EACH ROW

          BEGIN

             IF NVL (:OLD.deptno, 0) <>  NVL (:NEW.deptno, 0) THEN

                pack.set_flag;

           

                UPDATE dept

                   SET num_emps = num_emps - 1

                 WHERE deptno = :OLD.deptno;

           

                UPDATE dept

                   SET num_emps = num_emps + 1

                 WHERE deptno = :NEW.deptno;

           

                pack.reset_flag;

             END IF;

          END;

          /

           

          CREATE OR REPLACE TRIGGER adr_emp

            AFTER DELETE ON emp

            FOR EACH ROW

          BEGIN

             pack.set_flag;

           

             UPDATE dept

                SET num_emps = num_emps - 1

              WHERE deptno = :OLD.deptno;

           

             pack.reset_flag;

          END;

          /

           

          If we insert a new DEPT without the belonging EMP, or delete all EMPs belonging to a certain DEPT, or move all EMPs of a certain DEPT, when the COMMIT is issued we get the following error:

           

          ORA-02091: transaction rolled back

          ORA-02290: check constraint (SCOTT.DEPT_NUM_EMPS_CK) violated

           

          The point is that in both solutions we had to add "auxiliary" columns in DEPT table ("br_valid" in first case, and "num_emps" in the second).

          In the second case, despite of the first, one "auxiliary" column is (mostly) needed for each "COMMIT rule".

           

          If we'd like to add another "COMMIT rule" to the DEPT table, like:

          "SUM (sal) FROM emp WHERE deptno = p_deptno must be <= p_max_dept_sal"

          in the first solution we could use the same "br_valid" column for both the rules, but in the second case we would have to add another column, like "dept_sal".

          Another Oracle CDM RuleFrame advantage is that is does not force us to add "auxiliary" columns.

           

          We must emphasize that in real code we would not write PL/SQL code directly in the database triggers, but in packages, nor would we directly use RAISE_APPLICATION_ERROR.

          It is written this way in this sample only for the code clarity purpose.

           

          For the conclusion, we could notice that there could be a much simpler way with the delayed check of the declarative constraints.

          In spite of introducing "auxiliary" field "num_emps" and CK to check it, we could delay FK with EMP on DEPT and first add EMP rows,

          and then DEPT row, and in a bir_dept trigger check if there are belonging EMP rows.

          But, downside of that "solution" is that we could violate that rule (e.g. by deleting EMP rows afterwards).

          • 2. Re: Master-Detail Block : how to save changes only if detail block has a record
            Andreas Weiden

            You can do so using a calculated field to do so:

            Create a control-block with property "Single record" set to "True"

            Create a textitem with "datatype" Set to Number in taht block. Set "Calculation mode" to "Summary", "Summarized block" to your detail-block, "Summarized item" to any not-null item in that block and finally "calculation type" to count.

             

            Then, in the PRE-INSERT-trigger at your master-block, check if that newly created item's value is >0.

            • 3. Re: Master-Detail Block : how to save changes only if detail block has a record
              Tabit7

              Hi everyone and thanks for your answers,

               

              I think i would need to use the db solution proposed by Zlatco but i used a form solution with post-form-commit trigger.

              my problem is that when user goes to next record without saving the previous record which has no detail and the current record he has modified has detail, all records are saved including the record with no detail...i don't know if you see my problem with this post-form-commit trigger...i guess you would have an idea or i would try to use a db solution...

               

              Thanks

              • 4. Re: Master-Detail Block : how to save changes only if detail block has a record
                Bhushan53

                That is what the oracle auto save does. Now, if you are saying that while navigating from first record to another one the previous gets saved. It means that the previous record had a status 'INSERT' or 'CHANGED'. The validation as Andreas suggested, can be handled in WHEN-VALIDATE-RECORD of master block.