1. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

Werte in gleichnamigen Spalten in verschiedenen Tabellen löschen

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von MyCodeBucket, 30 Juli 2019.

  1. MyCodeBucket

    MyCodeBucket Benutzer

    Hallo zusammen.

    Ich stehe vor folgendem Problem:

    Auf einem MS SQL 2014 soll der Inhalt von zwei Spalten (attrib1,attrib3) in verschiedenen Tabellen gelöscht/überschrieben werden. die Tabellen identifiziere ich wie folgt:

    SELECT TABLE_NAME, COLUMN_NAME
    FROM database.INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'attr1'
    ORDER BY TABLE_NAME
    SELECT TABLE_NAME, COLUMN_NAME
    FROM database.INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'attr3'
    ORDER BY TABLE_NAME


    Diese Tabellen starten stets mit folgenden Zeichen: zTM...

    Um die Einträge in den Spalten 'attrib1' und 'attrib3' zu "anonymisieren" (hierbei handelt es sich um Benutzerdaten ...created by und last changed by...) verwende ich manuell für alle Tabellen folgende Statements:

    UPDATE [database].[dbo].[zTM...] SET attr1 ='ANON'
    UPDATE [database].[dbo].[zTM...] SET attr3 ='ANON'


    So weit zu meinem "überragenden" rudimentären Wissen zu SQL, anspruchsvoller kann ich nicht ;)

    Ich würde das Ganze gerne in einem SQL Statement ausführen und als StoredProcedure vorhalten.

    Das Problem ist, dass zu den Tabellen stets neue hinzukommen können und ich somit die "Identifikation" aller Tabellen mit den betreffenden Spalten durchführen muss.

    Bin ich da von meinem Ansatz her schräg oder gibt es eine einface Lösung?

    Ich möchte in den Tabellen die Werte einfach gelöscht oder mit einem Standardwert befüllt haben.

    Kann man evt. einen Trigger basteln, der die Ändeung in den Spalten erkennt und dann den Wert automatisch löscht oder umschreibt?

    Da eine Java Applikation in die Datenbank schreibt traue ich mich nicht, die Spalten einfach auf 'read only' zu setzen, da ich nicht weiss wie die Applikation beim schreiben reagiert.

    Hat jemand eine Idee, wie ich das umsetzen kann? Für eure Hilfe wäre ich dankbar, ich hab keine Ahnung von SQL, würde auch die Finger davon lassen, oder es eben lernen, aber dazu fehlt mir aktuell die Zeit.
     
  2. akretschmer

    akretschmer Datenbank-Guru

    das klingt alles extrem abenteuerlich. Warum speichert ihr erst die Daten, wenn ihr sie nicht benötigt?

    Zum Kern Deiner Frage: aus den Katalogen kannst Du die Namen der Tabellen holen, ja. Daraus mußt Du dann aber dynamisch die SQL-Befehle generieren, und diese generierten Befehle ausführen. Für PostgreSQL ginge das so: PostgreSQL: Documentation: 11: 43.5. Basic Statements

    Ein Trigger, der beim Insert/Update die Spalten vor der Speicherung noch einmal überschreibt, ist auch denkbar - und aus Datenschutzsicht sogar besser. Ich bin aber kein MSSQL-Experte.
     
  3. MyCodeBucket

    MyCodeBucket Benutzer

    Tja, wenn ich die Daten erst gar nicht speichern müsste, wäre ich froh, aber die kommen eben aus einer Applikation als Dokumenteigenschaften mit und in der Applikation kann ich aus Gewähleistungsgründen nichts ändern, geschweige denn dass ich nicht im Source-Code rumbasteln würde.
    Danke für den PostgeS link :)
    Die Trigger-Lösung finde ich auch charmanter.
    Wie gesagt, weiter wie die Basic-Basic-Basics habe ich bei SQL nie gebraucht. (sprich SELECT und UPDATE) :p
     
  4. akretschmer

    akretschmer Datenbank-Guru

    mal als Demo:

    Code:
    test=*# create table foo(id int primary key, val1 text, val2 text, val3 text);
    CREATE TABLE
    test=*# create or replace function trg_foo() returns trigger as $$begin new.val2:='anon'; new.val3:='anon';return new; end;$$language plpgsql;
    CREATE FUNCTION
    test=*# create trigger trg1 before insert or update on foo for each row execute procedure trg_foo();
    CREATE TRIGGER
    test=*# select * from foo;
     id | val1 | val2 | val3
    ----+------+------+------
    (0 rows)
    
    test=*# insert into foo values (1, 'name1', 'geheim spalte val2', 'geheim spalte val3');
    INSERT 0 1
    test=*# select * from foo;
     id | val1  | val2 | val3
    ----+-------+------+------
      1 | name1 | anon | anon
    (1 row)
    
    test=*# update foo set val2 = 'geheim2', val3='geheim3' where id = 1;
    UPDATE 1
    test=*# select * from foo;
     id | val1  | val2 | val3
    ----+-------+------+------
      1 | name1 | anon | anon
    (1 row)
    
    test=*#
    
    PostgreSQL, in M$SQL kann und wird die Syntax anders sein.
     
  5. MyCodeBucket

    MyCodeBucket Benutzer

    SUPER! Vielen Dank.
    Genauso schauts perfekt aus.
    Jetzt muss ich das "NUR" noch passend umsetzen und einbauen :confused::D XD
    Ich halt dich auf dem laufenden (und beschäftige mich mit Triggern und StoredPrcedures)
    Danke für den Ansatz :)
     
    akretschmer gefällt das.
  6. MyCodeBucket

    MyCodeBucket Benutzer

    ...und wenn ichs nicht hinbekomm tausch ich einfach den MS SQL Server gegen nen Postgres Server um :p
    Kleiner Scherz
     
    akretschmer gefällt das.
  7. akretschmer

    akretschmer Datenbank-Guru

    Damit wärest Du weder der Erste noch der Letzte...
     
  8. ukulele

    ukulele Datenbank-Guru

    Unter MSSQL würde sich da ein INSTEAD OF-Trigger anbieten, leider unterscheiden sich die SQL-Varianten grade bei Triggern sehr stark. Hier mal ein Beispiel:
    Code:
    CREATE TRIGGER   [dbo].[unt_insert]
       ON           [dbo].[unt]
       INSTEAD OF INSERT, UPDATE
    AS
    BEGIN
       SET NOCOUNT ON;
    
       INSERT INTO unt(pk,id)
       SELECT   i.pk,
               NULL
       FROM   INSERTED i
       LEFT JOIN DELETED d
       ON       i.pk = d.pk
       WHERE   d.pk IS NULL
    
       UPDATE   unt
       SET       unt.pk = i.pk,
               unt.id = NULL
       FROM   unt
       INNER JOIN INSERTED i
       ON       unt.pk = i.pk
       INNER JOIN DELETED d
       ON       unt.pk = d.pk
    END
    GO
    Das ist eine Tabelle von mir namens "unt". Mit diesem Trigger würde die Spalte "pk" ganz regulär eingefügt oder aktuallisiert, alle Spalten müssten im Prinzip im INSERT und UPDATE-Statement so auftauchen. Nur die Spalte deren Daten du verwerfen willst (in diesem Falle wäre das "id") setzt du in beiden Vorgängen auf NULL. Jetzt kann jeder in die DB inserten was er will, diese Spalte wird nie befüllt werden.

    Großer Nachteil von dem Trigger ist natürlich, dass wenn sich die Tabelle ändert, auch der Trigger angepasst werden muss. Vorteil ist aber das, selbst wenn andere Trigger auf der Tabelle arbeiten, die Daten zu keinem Zeitpunkt in die DB geschrieben werden, also auch nicht von einem anderen Trigger, der erst nach dem eigentlichen Insert feuert, irgendwie ausgelesen werden.
     
  9. MyCodeBucket

    MyCodeBucket Benutzer

    Sooo, sorry für das späte Danke (auch an ukulele)...Urlaub etc ;)
    Ich hab versucht das Gnze zu verstehen, scheitere doch mit meinem Verständnis bei der einen oder anderen Sache.
    Das Trigger Beispiel macht mir noch zu schaffen (mir fehlen halt viele Grundlagen) aber man wächst ja an den Herausforderungen.

    Wenn mein DB Server nun wie folgt aussieht (Mehrere Datenbanken, Tabellen in einer DB, Colums mit Null sollen gelöscht werden :
    DB_SRV
    >Databases
    >DB_01
    >DB_02
    >DB_TEST
    >Tab_01
    PK Col_01 Col_02 Col_03 Col_04 Col_05 Col_nn
    ## NULL ## NULL ## ## ##
    >Tab_02
    PK Col_01 Col_02 Col_03 Col_04 Col_05 Col_nn
    ## ## ## ## ## ## ##
    >Tab_03
    PK Col_01 Col_02 Col_03 Col_04 Col_05 Col_nn
    ## NULL ## NULL ## ## ##
    >Tab_nn
    PK Col_01 Col_02 Col_03 Col_04 Col_05 Col_nn
    >DB_nn


    In Bezug auf das Beispiel bedeutet das, ich muss für jede Tabelle einen Trigger generieren - so in etwa:

    CREATE TRIGGER [dbo].[Tab_01_insert]
    ON [DB_TEST].[dbo].[Tab_01]
    INSTEAD OF INSERT, UPDATE
    AS
    BEGIN
    ...
    END
    GO

    CREATE TRIGGER [dbo].[Tab_03_insert]
    ON [DB_TEST].[dbo].[Tab_03]
    INSTEAD OF INSERT, UPDATE
    AS
    BEGIN
    ...
    END
    GO
    .
    .

    Wenn ich das nun richtig verstanden habe (vielleicht ist da auch viel Müll von mir mit kommentiert, aber so habe ich mir das Verständnis zurchtgerückt) muss zwischen BEGIN und END folgendes:

    BEGIN
    SET NOCOUNT ON;

    INSERT INTO [DB_TEST].[dbo].[Tab_01](PK,Col_01,Col_02,Col_03,Col_04,Col_05,Col_nn) |sprich alle Spalten
    SELECT i.PK, | i.pk, NULL erschliesst sich mir nicht so ganz... erstes Element mit NULL im Primärkey? sprich:letzte Zeile?
    NULL
    FROM INSERTED i | inserted/deleted Tabelle - bin ich mich grad am einlesen
    :confused: (i/d=Name für die Tabelle?)
    LEFT JOIN DELETED d | und ab hier hörts grad definitiv auf mit meinem Verständnis, vielleicht denk ich auch zu kompliziert
    ON i.pk = d.pk
    WHERE d.pk IS NULL


    UPDATE Tab_01 | hier wird die Tabelle Tab_01 mit der inserted Tabelle upgedatet?
    SET Tab_01.pk = i.pk, | müssen hier alle Spalten (Col_01 bis Col_nn) hin?
    Tab_01.Col_01 = NULL | und hier alle Spalten (Col_01,Col_03) die leer bleiben sollen?
    FROM Tab_01
    INNER JOIN INSERTED i | und alles hier verknüpft, bringt meine Synapsen endgültig zum glühen :mad:
    ON Tab_01.pk = i.pk | Ich meine zwar zu verstehen, was hier passiert, kanns aber nicht ausdrücken :confused:
    INNER JOIN DELETED d
    ON Tab_01.pk = d.pk
    END



    CREATE TRIGGER [dbo].[Tab_01_insert]
    ON [DB_TEST].[dbo].[Tab_01]
    INSTEAD OF INSERT, UPDATE
    AS
    BEGIN
    SET NOCOUNT ON;

    INSERT INTO [DB_TEST].[dbo].[Tab_01](PK,NULL,Col_02,NULL,Col_04,Col_05,Col_nn)
    SELECT i.PK,
    NULL
    FROM INSERTED i
    LEFT JOIN DELETED d
    ON i.pk = d.pk
    WHERE d.pk IS NULL

    UPDATE Tab_01
    SET Tab_01.pk = i.pk,
    Tab_01.Col_01 = NULL
    FROM Tab_01
    INNER JOIN INSERTED i
    ON Tab_01.pk = i.pk
    INNER JOIN DELETED d
    ON Tab_01.pk = d.pk
    END
    GO


    mit diesem Statement bekomme ich
    Msg 120, Level 15, State 1, Procedure zTM1001_TRANSLATIONS_insert, Line 8
    The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.


    Ich wär echt froh, wenn mir jemand einen Denkanstoss gibt, damit ich meine Kaffee und Mittagspausen nicht wieder mit Pause vergeude :D

    und ich das endlich mal weiterbringe und verstehe.


     
  10. ukulele

    ukulele Datenbank-Guru

    Also grundsätzlich erstmal ja, jede Tabelle, in der eine Spalte dauerhaft auf NULL gehalten werden soll, braucht einen Trigger. Der muss natürlich auch die konkrete Tabellen- und Spaltenbezeichnungen enthalten, soweit klar.

    INSTEAD OF INSERT, UPDATE feuert dann den Code des Triggers bei jedem INSERT oder UPDATE anstelle der eigentlichen INSERT oder UPDATE-Anweisung. In dem Moment, wo der Trigger feuert (ausgelöst wird), stehen die kompletten Datensätze, die INSERT oder UPDATE liefern in der (System-)Tabelle INSERTED (die heißt so, bei MySQL ist das NEW). INSERTED beinhaltet also in dem Moment das, was eigentlich in die Tabelle soll.

    Alles was durch UPDATE überschrieben wird oder was durch ein DELETE gelöscht wird, steht in der Tabelle DELETED. Anstatt jetzt zuzulassen das die Daten aus INSERTED in die Tabelle gelangen und die Daten in DELETED aus der Tabelle entfernt werden findet jetzt also das statt, was im Trigger steht. Wichtig ist auch: Der Trigger wird mit einem Update-Befehl einmal ausgelöst, nicht für jede Zeile. Betrifft das Update 1000 Zeilen stehen auch 1000 Zeilen in INSERTED und der Trigger wird einmal abgefeuert. Er muss also logischerweise alles aus INSERTED berücksichtigen.

    Ich mache jetzt nichts anderes als alle Datensätze aus INSERTED in die Zieltabelle zu schreiben aber nur solche, die ich mit keinem Datensatz in DELETED joinen kann (natürlich über den Primärschlüssel). Das heißt es sind zwingend nur komplett neue Datensätze betroffen, keine Aktualisierungen denn dann gäbe es einen Eintrag in DELETED dazu.

    Der Insert reicht die Daten 1:1 weiter bis auf die Spalte, die du nullen willst. Diese Information steht zwar in INSERTED, wird aber nie in die Zieltabelle geschrieben.

    Im Anschluss joine ich Alles, was auch einen Eintrag in DELETED hat (INNER JOIN macht das zwingend notwendig), wo es also schon einen Datensatz mit dem Primärschlüssel gibt. Die Logik ist die selbe, diese Datensätze entstammen alle einem Update, ich führe jetzt also nicht den Insert sondern das Update auf die Zieltabelle aus selbst aus. Auch hier müssen alle Spalten rein und nur die, die genullt werden soll, wird nicht befüllt. Man könnte sie auch aus dem Update-Statement weg lassen, die steht da nur der Vollständigkeit halber.

    In deinem Code gibt es direkt einen Fehler
    Code:
    INSERT INTO [DB_TEST].[dbo].[Tab_01](PK,NULL,Col_02,NULL,Col_04,Col_05,Col_nn)
    In der Werteliste, die alle Spalten der Zieltabelle enthällt die befüllt werden sollen, fehlt die Spalte die mit NULL befüllt wird. In der Werteliste muss die Spalte genannt werden, in den Datensätzen muss dann NULL als Wert stehen. Auch hier kann man die Spalte auch komplett weg lassen, also sowohl in der Spaltenliste als auch im Select auf INSERTED.
     
    MyCodeBucket und Walter gefällt das.
  11. MyCodeBucket

    MyCodeBucket Benutzer

    Hallo Ukulele, herzlichen Dank für die tolle Erklärung.

    Wenn ich das richtig verstehe und ich versuche das auf einen Punkt gebracht zu erklären, hat man 3 Tabellen, die Originale, die Inserted und die Deleted.
    Die "neuen Daten" werden nicht in die Originale, sondern in die Inserted "geschrieben".
    In der Deleted sthen meine "ge-NULLten" Spalten und die Daten aus der Inserted Tabelle.
    Dann wird verglichen und was gleich ist wird ignoriert, der Rest geschrieben.
    Das wäre dann beim ersten Mal quasi alle Datensätze aus der Original, sofern ich die Inhalte der beiden Spalten Col_01 und Col_03 nicht vorher lösche und bei jedem weitern mal nur der neue Datensatz, der in den Spalten Col_01 und Col_03 etwas stehen hat.

    Wenn das was ich da geschrieben habe vom Prinzip her stimmt, ist das dank deiner Beschreibung genauso einfach zu kapieren, wie ein Am7-Chord auf der Ukulele.;)

    Vielen Dank!

    Dann begeb ich mich mal an die Umsetzung meiner 14 Originaltabellen mit - ich weiss nicht wieviel - Spalten (80-90) und kollabiere an der Syntax und Umsetzung :eek:

    Zum Glück sind die Tabellen identisch (bis auf den Namen und die Inhalte) :D

    .....und NEIN, das ist nicht mein Tabellendesign, das kommt aus der Applikation :rolleyes:

    Ich halt euch alle auf dem laufenden :)
     
  12. MyCodeBucket

    MyCodeBucket Benutzer

    Update:
    Ich hab jetzt meine erste Originaltabelle umgesetzt, das Script sieht analog dazu so aus:

    CREATE TRIGGER [dbo].[Tab_01_insert]
    ON [DB_TEST].[dbo].[Tab_01]
    INSTEAD OF INSERT, UPDATE
    AS
    BEGIN
    SET NOCOUNT ON;

    INSERT INTO [DB_TEST].[dbo].[Tab_01](PK,NULL,Col_02,NULL,Col_04,Col_05,Col_nn)
    SELECT i.PK,NULL,i.Col_02,NULL,i.Col_04,i.Col_05,i.Col_nn

    FROM INSERTED i
    LEFT JOIN DELETED d
    ON i.pk = d.pk
    WHERE d.pk IS NULL

    ON i.Col_01 = d.Col_01
    WHERE d.Col_01 IS NULL

    ON i.Col_02 = d.Col_02
    WHERE d.Col_02 IS NULL

    ON i.Col_03 = d.Col_03
    WHERE d.Col_03 IS NULL

    ON i.Col_04 = d.Col_04
    WHERE d.Col_04 IS NULL

    ON i.Col_05 = d.Col_05
    WHERE d.Col_05 IS NULL

    ON i.Col_nn = d.Col_nn
    WHERE d.Col_nn IS NULL

    UPDATE [DB_TEST].[dbo].[Tab_01]
    SET [DB_TEST].[dbo].[Tab_01].pk = i.pk,
    [DB_TEST].[dbo].[Tab_01].Col_01 = NULL,
    [DB_TEST].[dbo].[Tab_01].Col_02 = i.Col_02,
    [DB_TEST].[dbo].[Tab_01].Col_01 = NULL,
    [DB_TEST].[dbo].[Tab_01].Col_04 = i.Col_04,
    [DB_TEST].[dbo].[Tab_01].Col_05 = i.Col_05,
    [DB_TEST].[dbo].[Tab_01].Col_nn = i.Col_nn,

    FROM [DB_TEST].[dbo].[Tab_01]
    INNER JOIN INSERTED i
    ON [DB_TEST].[dbo].[Tab_01].pk = i.pk,
    ON [DB_TEST].[dbo].[Tab_01].Col_01 = NULL,
    ON [DB_TEST].[dbo].[Tab_01].Col_02 = i.Col_02,
    ON [DB_TEST].[dbo].[Tab_01].Col_01 = NULL,
    ON [DB_TEST].[dbo].[Tab_01].Col_04 = i.Col_04,
    ON [DB_TEST].[dbo].[Tab_01].Col_05 = i.Col_05,
    ON [DB_TEST].[dbo].[Tab_01].Col_nn = i.Col_nn,

    FROM [DB_TEST].[dbo].[Tab_01]
    INNER JOIN DELETED d
    ON [DB_TEST].[dbo].[Tab_01].pk = i.pk,
    ON [DB_TEST].[dbo].[Tab_01].Col_01 = NULL,
    ON [DB_TEST].[dbo].[Tab_01].Col_02 = i.Col_02,
    ON [DB_TEST].[dbo].[Tab_01].Col_01 = NULL,
    ON [DB_TEST].[dbo].[Tab_01].Col_04 = i.Col_04,
    ON [DB_TEST].[dbo].[Tab_01].Col_05 = i.Col_05,
    ON [DB_TEST].[dbo].[Tab_01].Col_nn = i.Col_nn,
    END
    GO


    Ist das so vom Aufbau her richtig oder doch irgendwas falsch verstanden/völlig schräg?:confused:
     
  13. ukulele

    ukulele Datenbank-Guru

    Du hast in dem Update-Statement 2x FROM stehen, das ist zuviel.

    Deine Erklärung ist auch nicht ganz korrekt, genullte Werte stehen in keiner der Tabellen. Es gibt drei identische Tabellen, zwei davon (INSERTED und DELETED) sind nur temporär und entstehen durch Insert oder Update oder Delete auf die Haupttabelle. In dem Zeitpunkt wo der Trigger ausgeführt wird, existieren sie.

    INSERTED (die Tabelle) enthält genau die Datensätze, die in die Tabelle eingefügt werden sollen. Das können bei UPDATE auch die Werte sein, die schon in der Tabelle stehen. DELETED enthält die Datensätze, wie sie ursprünglich in der Tabelle gestanden haben. Beispiel:
    Tabelle SpalteA SpalteB
    Datensatz1 WertA WertB

    UPDATE Tabelle SET SpalteA='WertC'

    In diesem Fall enthält INSERTED folgende Werte:
    Datensatz1 WertC WertB
    Und DELETED:
    Datensatz1 WertA WertB

    INSERT INTO Tabelle(SpalteA,SpalteB)
    VALUES('WertD','WertF')

    Dann entält INSERTED:
    Datensatz2 WertD WertF
    DELETED ist leer, enthält keine Datensätze.

    Erst wenn du jetzt verhinderst das der Inhalt von INSERTED und/oder DELETED an die Tabelle "angehängt" bzw. aus der Tabelle "entnommen"wird, sorgst du mit deinem Insert- oder Update-Befehl dafür das eine Spalte auf NULL gesetzt wird.
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden