Originally posted by Wernfried
View Post
Danke
Urwi
DROP TABLE tmp_Tabelle_A; CREATE TABLE tmp_Tabelle_A ( id NUMBER, name VARCHAR2(4000)); INSERT INTO tmp_Tabelle_A (id, name) VALUES (1, '###'); DROP TABLE tmp_Tabelle_B; CREATE TABLE tmp_Tabelle_B ( id NUMBER, aId NUMBER, name VARCHAR2(4000)); CREATE OR REPLACE TRIGGER tmp_trg_suche_before BEFORE INSERT ON tmp_Tabelle_B FOR EACH ROW DECLARE sText VARCHAR2( 1000 ); BEGIN SELECT listagg(name,' ') WITHIN GROUP (ORDER BY id) INTO sText FROM tmp_Tabelle_B WHERE aid=(:NEW.aid); sText := sText || ' ' || (:NEW.name) ; UPDATE tmp_Tabelle_A SET name = sText WHERE Id = (:NEW.aid) ; END; show errors
SELECT name vor0 FROM tmp_tabelle_A; INSERT INTO tmp_Tabelle_B (id, aid, name) VALUES (1001, 1, 'Eins'); INSERT INTO tmp_Tabelle_B (id, aid, name) VALUES (2001, 1, 'Uno'); INSERT INTO tmp_Tabelle_B (id, aid, name) VALUES (3001, 1, 'One'); SELECT name nach1 FROM tmp_tabelle_A; INSERT INTO tmp_Tabelle_B (id, aid, name) VALUES (4001, 1, 'un'); COMMIT; SELECT name nach2 FROM tmp_tabelle_A; INSERT INTO tmp_Tabelle_B (id, aid, name) VALUES (5001, 1, 'odin'); INSERT INTO tmp_Tabelle_B (id, aid, name) VALUES (6001, 1, 'unos'); SELECT name nach3 FROM tmp_tabelle_A; ROLLBACK; SELECT name nach4 FROM tmp_tabelle_A;
VOR0 -------------------------------------------------------------------------------- ### 1 row selected. 1 row created. 1 row created. 1 row created. NACH1 -------------------------------------------------------------------------------- Eins Uno One 1 row selected. 1 row created. Commit complete. NACH2 -------------------------------------------------------------------------------- Eins Uno One un 1 row selected. 1 row created. 1 row created. NACH3 -------------------------------------------------------------------------------- Eins Uno One un odin unos 1 row selected. Rollback complete. NACH4 -------------------------------------------------------------------------------- Eins Uno One un 1 row selected.
Comment