Как сделать так, чтобы значения в столбце отображались автоматически, когда значение вставляется / обновляется / удаляется в / из другого столбца в той же таблице?
Я создал 2 таблицы: INFORMATION А ТАКЖЕ FEED,
INFORMATION has 2 attributes : ID(Primary Key), TOT_AMOUNT.
FEED has 4 attributes : ID(Foreign key refer INFORMATION(ID)), S_AMOUNT, S_DATE, TOT_REM.
Теперь я должен вставить / обновить / удалить значения в / из TOT_REMна основе вставки / удаления / обновления S_AMOUNT а также TOT_AMOUNT,
Содержание образца:
INFORMATION Table
------------------
ID | TOT_AMOUNT
1 | 100
2 | 20
3 | 50
...
FEED Table
----------------------------------------
ID | S_AMOUNT | S_DATE | TOT_REM
1 | 10 |10.10.2010| 90
1 | 10 |13.10.2010| 80
1 | 30 |17.10.2013| 50
1 | 10 |20.10.2016| 40
...
Нам нужно автоматически вставить значение в TOT_REM атрибут, основанный на операции обновления / вставки / удаления, выполненной S_AMOUNT, с помощью TOT_AMOUNT & S_AMOUNT,
В любое время TOT_REM не может быть меньше 0. И TOT_REM должен быть автоматически вставлен / удален / обновлен так, чтобы
TOT_REM for i(at a specific date) = (TOT_AMOUNT for ID=i) -
SUM(S_AMOUNT of all instances of ID=i,
which is later than the S_DATE for ID=i);
Таким образом, если мы удалим 2-й кортеж (1,10, "13,10.2010",80), то отраженное состояние BR_FEED должно быть:
FEED Table
----------------------------------------
ID | S_AMOUNT | S_DATE | TOT_REM
1 | 10 |10.10.2010| 90
1 | 30 |17.10.2013| 60
1 | 10 |20.10.2016| 50
...
Я написал триггер, который не показывает
ORA-04091: table SSUMAN.FEED is mutating, trigger/function may not see it
Код для триггера:
CREATE OR REPLACE TRIGGER BR_INSERT_TRB
AFTER DELETE OR INSERT OR UPDATE OF S_AMOUNT ON FEED
FOR EACH ROW
BEGIN
IF DELETING THEN
UPDATE FEED bf
SET bf.TOT_REM = bf.S_AMOUNT + :OLD.S_AMOUNT;
END IF;
IF INSERTING THEN
INSERT INTO FEED (TOT_REM) VALUES(
((SELECT TOT_AMOUNT FROM INFORMATION bi WHERE bi.ID=:NEW.ID) -
(SELECT SUM(S_AMOUNT) FROM FEED bf where bf.ID=:NEW.ID) -
:NEW.S_AMOUNT);
END IF;
IF UPDATING THEN
UPDATE FEED bf
SET bf.TOT_REM = (SELECT TOT_AMOUNT FROM BR_INFORMATION bi WHERE bi.ID=bf.ID) -
(SELECT SUM(S_AMOUNT) FROM FEED bf where bf.ID=:NEW.ID) -
:NEW.S_AMOUNT
WHERE :NEW.ID IS NOT NULL;
END IF;
END;
Вопросы:
- Этот подход ошибочен? Не могу ли я достичь того, чего хочу, таким образом?[ДОПОЛНИТЕЛЬНО]
- Есть ли здесь какая-то возможность привлечь внимание? Я не могу думать в этом направлении! Возможно, недостаток опыта...[ДОПОЛНИТЕЛЬНО]
- Есть ли лучший подход, чтобы значения TOT_REM могли автоматически отражаться?[COMPULSORY TO ANSWER]
2 ответа
Я думаю, что лучше создать вид. Посмотри на это
Тестовые данные
create table feed(ID,S_AMOUNT,S_DATE) as (
SELECT 1,10, TO_DATE('10.10.2010','dd.mm.yyyy') FROM dual UNION all
SELECT 1,10,TO_DATE('13.10.2010','dd.mm.yyyy') FROM dual UNION all
SELECT 1,30,TO_DATE('17.10.2013','dd.mm.yyyy') FROM dual UNION all
SELECT 2,10,TO_DATE('20.10.2016','dd.mm.yyyy') FROM dual)
create table INFORMATION (id, TOT_AMOUNT) as (
SELECT 1,100 FROM DUAL UNION ALL
SELECT 2,20 FROM DUAL UNION ALL
SELECT 3,50 FROM DUAL)
запрос
create or replace view result_feed as
SELECT f.*,i.TOT_AMOUNT - NVL(SUM(S_AMOUNT) OVER(PARTITION BY f.ID ORDER BY f.S_DATE),0) AS tot_rem FROM FEED f, INFORMATION i
WHERE f.ID = i.id
ORDER BY f.ID, f.S_DATE;
-- used NVL to prevent side-effect of null values
SELECT * from RESULT_FEED;
Ваш подход с помощью триггера не подходит в этой ситуации. Я думаю, что данные добавляются редко, а запрос нужен только в особых случаях. Конечно, есть несколько подходов к обходной таблице мутаций (переменные пакета, составные триггеры, автономные транзакции), но я думаю, что они только добавляют проблемы производительности в вашу базу данных.
Если бы это была моя бизнес-проблема, и я мог бы начать с нуля (что может быть невозможно в вашем случае), я бы оставил INFORMATION таблица как есть, я бы удалил TOT_REM колонка из FEEDи я бы создал вид, который выглядит как текущий FEED Таблица. Вы можете написать всю необходимую логику в определении представления.
ДОБАВЛЕНО:
Во-первых, вот определение вида; это предполагает базовые таблицы INFORMATION а также FEED как описано в ОП, без TOT_REM колонка в FEED,
create view remaining_balance (id, s_amount, s_date, tot_rem) as
select i.id, f.s_amount, f.s_date,
i.tot_amount - nvl(sum(f.s_amount) over (partition by f.id order by f.s_date), 0)
from information i left outer join feed f
on i.id = f.id
;
Представление использует внешнее соединение, чтобы включить idиз INFORMATION таблица, которая не имеет соответствующих строк в FEED, (Затем, чтобы иметь дело с нулями в вычислении TOT_REMЯ использую nvl() функция для преобразования NULL в 0.)
Вот пример запуска представления:
SQL> select * from information;
ID TOT_AMOUNT
---------- ----------
1 100
2 20
3 50
3 rows selected.
SQL> select * from feed;
ID S_AMOUNT S_DATE
---------- ---------- ----------
1 10 2010-10-10
1 10 2010-10-13
1 30 2010-10-17
1 10 2016-10-20
4 rows selected.
SQL> select * from remaining_balance order by id, s_date;
ID S_AMOUNT S_DATE TOT_REM
---------- ---------- ---------- ----------
1 10 2010-10-10 90
1 10 2010-10-13 80
1 30 2010-10-17 50
1 10 2016-10-20 40
2 20
3 50
6 rows selected.
Теперь хорошо зарекомендовавший себя способ применения сложных ограничений заключается в использовании материализованных представлений. Ограничения прямой проверки работают только на уровне строк и не могут использоваться, когда условия включают более одной таблицы. В текущей задаче проверка на две таблицы, и TOT_REM зависит от других строк в FEED таблица - так что ограничение в FEED стол не будет работать в любом случае.
Подход материализованного представления состоит в том, чтобы определить представление, подобное тому, которое я создал, как материализованное представление, чтобы определить его с помощью refresh fast on commit (чтобы ограничения проверялись сразу после операций DML над базовыми таблицами), а также для создания проверочного ограничения для материализованного представления. В рассматриваемой проблеме это будет проверка на TOT_RM >= 0,
Увы, refresh fast on commit запрещено (по крайней мере, недавно, как Oracle версии 11.2, что у меня есть), когда определение представления использует аналитические функции. Я использовал аналитическую версию sum() функция, так что это не сработает.
Однако представляется целесообразным определить другое материализованное представление, как показано ниже:
create materialized view remaining_balance (id, tot_rem) as
select i.id, i.tot_amount - f.sum_s_amount
from information i inner join (select id, sum(s_amount) as sum_s_amount
from feed
group by id) f
on i.id = f.id
;
SQL> select * from remaining_balance;
ID TOT_REM
---------- ----------
1 40
Я больше не использую внешнее соединение, поскольку предполагается, что он показывает только остатки. Я предполагаю INFORMATION имеет ограничение проверки на TOT_AMOUNT чтобы убедиться, что это не отрицательно, и id в FEED является первичным ключом, указывающим на id в INFORMATION, так что нет никакой дополнительной информации, раскрытой внешним соединением для этой версии представления. (Однако при желании можно включить все id"S).
Здесь вы должны быть в состоянии определить вид с refresh fast on commit и добавить проверочное ограничение для эффекта tot_rem >= 0, Увы, я не могу это проверить; расширенная репликация (требуется для создания материализованных журналов представления, которые, в свою очередь, необходимы для refresh fast) недоступен / не включен в бесплатной экспресс-версии Oracle, которую я имею. Попробуйте поэкспериментировать с этим, хотя это может быть решением, которое вам нужно. Удачи!