Werte in gleichnamigen Spalten in verschiedenen Tabellen löschen

[MyCodeBucket]

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.

 

[akretschmer]

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.

 

[MyCodeBucket]

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)

 

[akretschmer]

mal als Demo:

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.

 

[MyCodeBucket]

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

 

[MyCodeBucket]

...und wenn ichs nicht hinbekomm tausch ich einfach den MS SQL Server gegen nen Postgres Server um
Kleiner Scherz

 

[akretschmer]

...und wenn ichs nicht hinbekomm tausch ich einfach den MS SQL Server gegen nen Postgres Server um

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

 

[ukulele]

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:

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 aktualisiert, 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.

 

[MyCodeBucket]

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 (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
ON Tab_01.pk = i.pk | Ich meine zwar zu verstehen, was hier passiert, kanns aber nicht ausdrücken
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

und ich das endlich mal weiterbringe und verstehe.

 

[ukulele]

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

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]

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

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

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

Ich halt euch alle auf dem laufenden

 

[MyCodeBucket]

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?

 

[ukulele]

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.