Check sum interactive grid

Luffy

Hi everyone, I used the apex of 2020. I have one case as when create new interactive grid where column A is selected sum that must equal = 100. I used two cases but it was not unsuccessful.

- The first case, I tried to create validation for column but it was suitable for editing ( because recorded rows), with creating new, database has been not recorded,  so that validation has been not checked.

- The second case, I create a trigger for a table (new interactive grid) when after insert

CREATE OR REPLACE TRIGGER TRIGGER2

after INSERT OR UPDATE OF COLUMN A  ON TABLE

  declare

v_weight number;

BEGIN

       select nvl(sum(A),0)

    into v_weight

from TABLEgc

where gc.grading_id = v('GRADING_ID');

where v('GRADING_ID) - I create this item on applicaiton and assign to page item

     if v_weight <> 100 then

        raise_application_error(-20111,'This total must equal =  100%');

     end if;

END;

Thank you in advance. I hope everybody give me new idea or new skills.

Keyser

your trigger solution above would fire for each row, so if you were inserting/updating/deleting multiple rows it could fail mid transaction when in reality once all the DML had taken place they might correctly sum to 100 (also, it's not clear from your trigger as I don't think it will compile but I think you would have a mutating problem due to selecting from the same table you are updating)

it's an old discussion but a good one on Ask Tom

"total participation within one agreement must sum to 100 percent"

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4233459000346171405

I'm not keen on the MV solution but the initially deferred constraint on the parent table solution might be of interest to you but you would need to handle the multiple users scenario

also, SQL Assertions would be nice but they don't exist yet...

SQL Assertions / Declarative multi-row constraints

(one of the Ask Tom links in that thread is the one I have included above, "Toon" created the Database SQL Assertion thread and comments in the Ask Tom thread so make sure you read his replies)

please update your username to something more recognisable than "4162060"

Keyser

your trigger solution above would fire for each row, so if you were inserting/updating/deleting multiple rows it could fail mid transaction when in reality once all the DML had taken place they might correctly sum to 100 (also, it's not clear from your trigger as I don't think it will compile but I think you would have a mutating problem due to selecting from the same table you are updating)

it's an old discussion but a good one on Ask Tom

"total participation within one agreement must sum to 100 percent"

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4233459000346171405

I'm not keen on the MV solution but the initially deferred constraint on the parent table solution might be of interest to you but you would need to handle the multiple users scenario

also, SQL Assertions would be nice but they don't exist yet...

SQL Assertions / Declarative multi-row constraints

(one of the Ask Tom links in that thread is the one I have included above, "Toon" created the Database SQL Assertion thread and comments in the Ask Tom thread so make sure you read his replies)

please update your username to something more recognisable than "4162060"

Luffy

Thank you, Keyser. I have read your link that helps me many thing to do. I have created view log on table with statment

Create materialized view log on table A logging

with rowid, column B

INCLUDING NEW VALUES;

A But a that period of creating materialized view refresh fast on commit,

it shows me cannot use filter colums from materialized view log on table A. I tried to recreate log by adding with rowid, sequence(column B, B_id) but it shows me view already exits on table A.

Can you give some good idea?

Keyser

it shows me cannot use filter colums from materialized view log on table A. I tried to recreate log by adding with rowid, sequence(column B, B_id) but it shows me view already exits on table A.

DROP MATERIALIZED VIEW LOG ON <table_name>;

then try to recreate it

you may have the same issue with the MV...

DROP MATERIALIZED VIEW <mv_name>;

this is an old but good article on MV use : https://oracle-base.com/articles/misc/materialized-views

also consult the documentation for whatever version of the Oracle database you are using

you should get into the habit of using more descriptive table/column names, A, B, B_ID will get very tiresome very quickly, especially for anyone who has to maintain your code later

you should also use the advanced editor on here to highlight your SQL code as i have done.  People want to help you but don't make it hard work for them, make it as easy as possible, they'll be more likely to assist you.  Create the smallest example possible to recreate the problem you are having and include any Oracle errors you receive.

Luffy

@Keyser I am appreciate you for helping me to solve m y problem and every comment of you to me. I will do it for the next time.

The query statement of drop MATERIALIZED VIEW is very clear and easy as well as my case. I use that quote to deal with that problem.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4233459000346171405

You gave me new skills in oracle and improve my presentation skills on forum.