Dynamisches SQL in Trigger funktioniert nicht

[sebator23]

Hallo,

ich möchte ein CURSOR mit dynamischem SQL Statement in einem Trigger(After Update) laufen lassen.
Leider habe ich das Gefühl, dass innerhalt des CURSORS nicht mehr auf INSERTED zugegriffen werden kann.
Aus diesem Grund habe ich eine Tabellenvariable angelegt und wollte innerhalb des CURSORS auf diese Variable zugreifen
aber leider funktioniert das auch nicht.

Hier mein Statement:

IF UPDATE(ID_Status)
BEGIN

DECLARE @Data TABLE
(
ID_ADDRESS INT,
ID_ADDRESS_ORG INT,
ID_STATUS INT,
Statusdate DATETIME,
Lastuser VARCHAR(50),
Lastdate DATETIME,
Vorname VARCHAR(50),
Nachname VARCHAR(50),
Kundennummer VARCHAR(50),
Vertrag_Nr VARCHAR(50),
id_tarif INT,
Dialer INT,
DBName_ORG VARCHAR(100),
TransferDate_ORG_DB DATETIME
)



INSERT INTO @Data (ID_ADDRESS,
ID_ADDRESS_ORG,
ID_STATUS,
Statusdate,
Lastuser,
Lastdate,
Vorname,
Nachname,
Kundennummer,
Vertrag_Nr,
id_tarif,
Dialer,
DBName_ORG,
TransferDate_ORG_DB)


SELECT
ID_ADDRESS,
ID_ADDRESS_ORG,
ID_STATUS,
Statusdate,
Lastuser,
Lastdate,
Vorname,
Nachname,
Kundennummer,
Vertrag_Nr,
id_tarif,
Dialer,
DBName_ORG,
TransferDate_ORG_DB
FROM
Inserted


DECLARE @DBName VARCHAR(100)
DECLARE @sSelect VARCHAR(MAX)


DECLARE DataCursor CURSOR FOR

SELECT DISTINCT
DBName_ORG
FROM
@Data


OPEN DataCursor
FETCH NEXT FROM DataCursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN


IF ISNULL(DATABASEPROPERTYEX(@DBName, 'Status'),'offline') = 'online'
BEGIN

SET @sSelect = 'Insert Into ' + @DBName + '.dbo.T_Address (ID_Address_2C, ID_Address_Parent, ID_Status, Statusdate, Lastuser, Lastdate, Vorname, Nachname, Kundennummer, Vertrag_Nr, id_tarif, Zupo)

Select
I.ID_Address,
I.ID_Address_ORG,
I.ID_Status,
I.Statusdate,
I.Lastuser,
I.Lastdate,
I.Vorname,
I.Nachname,
I.Kundennummer,
I.Vertrag_Nr,
I.id_tarif,
1
from
@Data as I
where
I.id_status = 3
and I.DBName_ORG = ''' + @DBName + '''
and I.TransferDate_ORG_DB is null
'

EXECUTE (@sSelect)

END
FETCH NEXT FROM DataCursor INTO @DBName
END
CLOSE DataCursor
DEALLOCATE DataCursor

END

Der Fehler lautet:
Die @Data-Tabellenvariable muss deklariert werden

Habt ihr vielleicht eine Idee?

Vielen Dank für eure Hilfe.

Sebator

 

[ukulele]

Also erstmal korrekt, sobald du im Kontext des Triggers EXEC() aufrufst hast du innerhalb von EXEC() keinen Zugriff mehr auf Variablen, INSERTED, DELETED und natürlich auch nicht auf temporäre Tabellen die nur innerhalb des Triggers existieren. Du hast also das eigentliche Problem nicht wirklich gelöst.

Ich bin mir grade nicht mal sicher warum du überhaupt EXEC() verwendest.
[Edit] Ah jetzt erst gesehen, du schreibst in die DB die in deinem Datensatz genannt wird.[/Edit]

 

[ukulele]

Da du einen After-Update-Trigger hast stehen die Daten ja schon in deiner Tabelle. Du kannst also grundsätzlich die Daten auch aus der eigentlichen Tabelle bekommen und mit EXEC() zusammengefasst nach Datenbank abarbeiten, das wäre eleganter. Ist ein bisschen knifflig wenn man nicht direkt davor sitzt aber ich meine das müsste gehen.

 

[sebator23]

Ich habe bisher nur mit EXEC() gearbeitet und wüsste nicht, wie ich das anders lösen soll.
Ich benötige ein dynamisches Statement, da sich das Insert auf unterschiedliche Datenbanken bezieht.

Hintergrund ist folgender:

Ich habe eine Datenbank, die Datensätze aus insgesamt 5 anderen Datenbanken enthält.
Nun möchte ich, dass ein erfolgreich abgespeicherter Datensatz über den Trigger einen neuen Datensatz in der
Ursprungskampagne erstellt.

Ich hoffe, ich habe mich verständlich ausgedrückt

 

[sebator23]

Da du einen After-Update-Trigger hast stehen die Daten ja schon in deiner Tabelle. Du kannst also grundsätzlich die Daten auch aus der eigentlichen Tabelle bekommen und mit EXEC() zusammengefasst nach Datenbank abarbeiten, das wäre eleganter. Ist ein bisschen knifflig wenn man nicht direkt davor sitzt aber ich meine das müsste gehen.

Das habe ich bereits so gelöst aber ich dachte, es gibt noch eine elegantere Lösung


IF UPDATE(ID_Status)
BEGIN

DECLARE @DBName varchar(100)
DECLARE @sSelect varchar(MAX)


Declare DataCursor cursor for

SELECT DISTINCT
I.DBName_ORG
from
Inserted AS I


OPEN DataCursor
FETCH NEXT FROM DataCursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN


IF ISNULL(DATABASEPROPERTYEX(@DBName, 'Status'),'offline') = 'online'
BEGIN

SET @sSelect = 'Insert Into ' + @DBName + '.dbo.T_Address (ID_Address_2C, ID_Address_Parent, ID_Status, Statusdate, Lastuser, Lastdate, Vorname, Nachname, Kundennummer, Vertrag_Nr, id_tarif, Zupo)

Select
I.ID_Address,
I.ID_Address_ORG,
I.ID_Status,
I.Statusdate,
I.Lastuser,
I.Lastdate,
I.Vorname,
I.Nachname,
I.Kundennummer,
I.Vertrag_Nr,
I.id_tarif,
1
from
T_Address as I
where
I.id_status = 3
and I.DBName_ORG = ''' + @DBName + '''
and I.TransferDate_ORG_DB is null
'

EXECUTE (@sSelect)

END
FETCH NEXT FROM DataCursor INTO @DBName
End
close DataCursor
deallocate DataCursor

END

 

[dabadepdu]

Habt ihr vielleicht eine Idee?

Ich bin kein MS SQL Spezialist, aber aus meiner Sicht zur Begriffsdefinition:
dynamisches SQL ist ungleich Textkonkatenierte Statements, sondern ein eigenständiges Verfahren, mit dem Du tatsächlich Objektzugriff hast.
Das ist allerdings (m)eine Perspektive, die ich so noch nicht eingesetzt habe bei MS SQL.

Wenn Du bei String Operationen bleibst, muss natürlich alles gehen, was mit Stringkonkatenation und Wertextraktion möglich ist.
Aber ein "Exec(SQLText)" löst keine Objekte auf.

 

[sebator23]

[Edit] Ah jetzt erst gesehen, du schreibst in die DB die in deinem Datensatz genannt wird.[/Edit]

Ja genau.
Ich kenne leider keinen anderen Weg als EXEC()

 

[sebator23]

Ich bin kein MS SQL Spezialist, aber aus meiner Sicht zur Begriffsdefinition:
dynamisches SQL ist ungleich Textkonkatenierte Statements, sondern ein eigenständiges Verfahren, mit dem Du tatsächlich Objektzugriff hast.
Das ist allerdings (m)eine Perspektive, die ich so noch nicht eingesetzt habe bei MS SQL.

Wenn Du bei String Operationen bleibst, muss natürlich alles gehen, was mit Stringkonkatenation und Wertextraktion möglich ist.
Aber ein "Exec(SQLText)" löst keine Objekte auf.

Ich kenne leider keinen anderen Weg als EXEC()
Gibt es einen anderen Lösungsansatz?

 

[dabadepdu]

Wie gesagt, SQL Statements als Zeichenketten zusammenzubauen ist eine Möglichkeit, die muss man dann konsequent einsetzen und nicht mit Objekten (Cursor, Table Variables, ..) vermischen. Du verletztes es glaube ich bei "set @Select ='... from @Data..", was Du aber im nächsten Post geändert hast.
Und Zeichen zu SQL Strings zusammenzusetzen ist nicht "Dynamisches SQL". Das ist nach meiner Kenntnis etwas anderes, ein eigenständiger Begriff / Verfahren, den ich aber explizit nur von Oracle und Postgres kenne, das ist ein eigenes Thema. Meine Anmerkung betrifft zunächst einmal den Hinweis auf die (einheitliche) Vorgehensweise und ihre Benennung.

Ob und wie "Dynamisches SQL" auch bei MS SQL möglich ist, weiß ich nicht, müsste ich selbst recherchieren, werde ich aber nicht tun.

 

[sebator23]

Wie gesagt, SQL Statements als Zeichenketten zusammenzubauen ist eine Möglichkeit, die muss man dann konsequent einsetzen und nicht mit Objekten (Cursor, Table Variables, ..) vermischen. Du verletztes es glaube ich bei "set @Select ='... from @Data..", was Du aber im nächsten Post geändert hast.
Und Zeichen zu SQL Strings zusammenzusetzen ist nicht "Dynamisches SQL". Das ist nach meiner Kenntnis etwas anderes, ein eigenständiger Begriff / Verfahren, den ich aber explizit nur von Oracle und Postgres kenne, das ist ein eigenes Thema. Meine Anmerkung betrifft zunächst einmal den Hinweis auf die (einheitliche) Vorgehensweise und ihre Benennung.

Ob und wie "Dynamisches SQL" auch bei MS SQL möglich ist, weiß ich nicht, müsste ich selbst recherchieren, werde ich aber nicht tun.

Danke für den Hinweis.
Ich werde das mal recherchieren.

 

[ukulele]

Also ich kenne dynamisches SQL tatsächlich nur so das ausführbarer Code als String zusammen gebaut wird. Das das mit Problemen einher geht ist naheliegend, aber eine andere "Verwendung" ist mir nicht geläufig.

Hier steht auch was zu deinem Fall (select * from @dbname + '..tbl'):

Dynamisches SQL : Fluch und Segen

Original von Erland Sommarskog; deutsche Übersetzung von Frank Kalis Wenn Sie die verschiedenen Newsgroups über Microsoft SQL Server verfolgen, wird Ihnen auffallen, dass häufig Fragen gestellt werden, warum die Statements: SELECT * FROM @tablename…

www.insidesql.org

 

[sebator23]

Also ich kenne dynamisches SQL tatsächlich nur so das ausführbarer Code als String zusammen gebaut wird. Das das mit Problemen einher geht ist naheliegend, aber eine andere "Verwendung" ist mir nicht geläufig.

Hier steht auch was zu deinem Fall (select * from @dbname + '..tbl'):

Dynamisches SQL : Fluch und Segen

Original von Erland Sommarskog; deutsche Übersetzung von Frank Kalis Wenn Sie die verschiedenen Newsgroups über Microsoft SQL Server verfolgen, wird Ihnen auffallen, dass häufig Fragen gestellt werden, warum die Statements: SELECT * FROM @tablename…

www.insidesql.org

Ich kenne dynamisches SQL auch nur als zusammengebauten String.
Danke für den Link. Ich schaue mir das mal an.

 

[ukulele]

Ich würde aber sagen dein 2ter Code ist schon der ideale Weg der mir einfällt.

 

[sebator23]

Ich würde aber sagen dein 2ter Code ist schon der ideale Weg der mir einfällt.

Dann werde ich das auch so anwenden.
Danke für die Unterstützung