By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE warehouse_products (
shaft NUMBER,
axle NUMBER,
diferantial NUMBER
);
CREATE VIEW total_stocks_v (shafts_stock, axle_stock, diferantial_stock) AS
SELECT SUM(Shaft),
SUM(AXLE),
SUM(diferantial)
FROM warehouse_products;
CREATE TABLE total_stocks (
shafts_stock NUMBER,
axle_stock NUMBER,
diferantial_stock NUMBER
);
CREATE OR REPLACE TRIGGER TRIGGER1
AFTER DELETE OR INSERT OR UPDATE OF AXLE, DIFERANTIAL, SHAFT
ON WAREHOUSE_PRODUCTS
BEGIN
MERGE INTO total_stocks dst
USING (
SELECT SUM(Shaft) AS shafts_stock,
SUM(AXLE) AS axle_stock,
SUM(diferantial) AS differential_stock
FROM warehouse_products
) src
ON (1 = 1)
WHEN MATCHED THEN
UPDATE
SET shafts_stock = src.shafts_stock,
axle_stock = src.axle_stock,
diferantial_stock = src.differential_stock
WHEN NOT MATCHED THEN
INSERT (shafts_stock, axle_stock, diferantial_stock)
VALUES (src.shafts_stock, src.axle_stock, src.differential_stock);
END;
/
SELECT * FROM USER_ERRORS;
INSERT INTO warehouse_products VALUES (1,2,3);
1 rows affected
INSERT INTO warehouse_products VALUES (2,3,4);
1 rows affected
SELECT * FROM total_stocks_v;
| SHAFTS_STOCK | AXLE_STOCK | DIFERANTIAL_STOCK |
|---|---|---|
| 3 | 5 | 7 |
SELECT * FROM total_stocks;
| SHAFTS_STOCK | AXLE_STOCK | DIFERANTIAL_STOCK |
|---|---|---|
| 3 | 5 | 7 |
INSERT INTO warehouse_products
SELECT 9, 8, 7 FROM DUAL UNION ALL
SELECT 13, 42, 97 FROM DUAL;
2 rows affected
SELECT * FROM total_stocks_v;
| SHAFTS_STOCK | AXLE_STOCK | DIFERANTIAL_STOCK |
|---|---|---|
| 25 | 55 | 111 |
SELECT * FROM total_stocks;
| SHAFTS_STOCK | AXLE_STOCK | DIFERANTIAL_STOCK |
|---|---|---|
| 25 | 55 | 111 |