Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.5K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 401 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Check sum interactive grid

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.
Best Answer
-
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"
Answers
-
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"
-
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?
-
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.
-
@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.