|
[x]
Posted via EE Mobile
|
||
Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again. |
||
| Question |
|
[x]
Attachment Details
|
||
|
[x]
The Solution Rating System
|
||
With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.
Your Input Matters If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support. Thank you! |
||
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 63: 64: 65: 66: 67: 68: 69: 70: 71: 72: 73: 74: 75: 76: 77: 78: 79: 80: 81: 82: 83: 84: 85: 86: |
create or replace
TRIGGER
"THW_TEST_TRG_UPD" FOR UPDATE ON APP_ADMIN.THW_TEST
COMPOUND TRIGGER
-- Datentyp für einen Datensatz der Tabelle
TYPE t_history_tab IS TABLE OF THW_TEST%ROWTYPE;
-- Deklaration der Variablen
l_history_tab t_history_tab := t_history_tab();
l_timestamp_von timestamp(9);
l_timestamp_bis timestamp(9);
l_index SIMPLE_INTEGER := 0;
-- ===============================================================
-- BEFORE EACH ROW
-- ===============================================================
BEFORE EACH ROW IS
BEGIN
-- Aktuelle Zeitstempel bestimmen
l_timestamp_von := systimestamp;
l_timestamp_bis := l_timestamp_von - numtodsinterval(0.000001,'SECOND');
-- Wird der Hauptdatensatz verändert, dann Daten historisieren
IF (:OLD.ORIG_ID IS NOT NULL) AND (:OLD.ID IS NOT NULL) AND (:OLD.ORIG_ID = :OLD.ID) THEN
-- Zähler inkrementieren und Collection erweitern
l_history_tab.extend();
l_index := l_history_tab.LAST;
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- This I would like to do... (generic solution)
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- l_history_tab(l_index) := :OLD;
-- l_history_tab(l_index).ID := THW_TEST_SEQ_ID.nextval;
-- l_history_tab(l_index).gueltig_bis := l_timestamp_bis;
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- instead of... (field by field)
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
l_history_tab(l_index).ID := THW_TEST_SEQ_ID.nextval;
l_history_tab(l_index).orig_id := :OLD.orig_id;
l_history_tab(l_index).hapo_id := :OLD.hapo_id;
l_history_tab(l_index).benu_id := :OLD.benu_id;
l_history_tab(l_index).gueltig_von := :OLD.gueltig_von;
l_history_tab(l_index).gueltig_bis := l_timestamp_bis;
l_history_tab(l_index).zuletzt_mutiert := :OLD.zuletzt_mutiert;
l_history_tab(l_index).data := :OLD.data;
END IF;
-- Zuletzt mutiert aktualisieren
:NEW.gueltig_von := l_timestamp_von;
:NEW.zuletzt_mutiert := l_timestamp_von;
END BEFORE EACH ROW;
-- ===============================================================
-- AFTER STATEMENT
-- ===============================================================
AFTER STATEMENT IS
BEGIN
-- Wenn es was zu historisieren gibt, dann hier erledigen
IF (l_history_tab.COUNT() > 0) THEN
-- Alle gespeicherten Records verarbeiten
FOR i IN l_index..l_history_tab.COUNT()
LOOP
-- Alten Datensatz einschieben
INSERT INTO THW_TEST VALUES l_history_tab(l_index);
END LOOP;
l_history_tab.delete();
END IF;
END AFTER STATEMENT;
END THW_TEST_TRG_UPD;
|
Advertisement
| Hall of Fame |