Enable / Disable Trigger in SQL Datenbank

  • VB.NET

Es gibt 1 Antwort in diesem Thema. Der letzte Beitrag () ist von GOKTerek.

    Enable / Disable Trigger in SQL Datenbank

    Hallo,

    aktuell bin ich dabei einen Manuellen Prozess des Datenbanktausches zu automatisieren. Hierzu werden CAD-Daten kopiert und es müssen SQL-DB ausgetauscht werden.

    Es funktioniert alles Einwandfrei, bis auf das ich in der Destination DB die Trigger nicht deaktivieren kann.

    Hier bekomme ich die Meldung, das die Trigger nicht gefunden werden bzw. ich die Rechte nicht habe, diese zu deaktivieren. Manuell kann ich sie im SQL Manager deaktivieren.

    Als Codevorlage habe ich diese Seite gehabt(mein Schlaues SQL Buch behandelt das Thema Trigger gar nicht):

    social.msdn.microsoft.com/Foru…ert-a-row?forum=vbgeneral

    Anbei mein Code die Trigger einschalten ausschalten sind in Zeile 40 - Zeile 60:

    VB.NET-Quellcode

    1. Public Sub CreateBackupSourcePDM(ByVal Source_SelectedRow As DataRow, ByVal Destination_SelectedRow As DataRow, ByVal Workingtable As DataTable)
    2. Try
    3. CreateDB_Backup(CStr(Source_SelectedRow("PDM_System")), "SourceDB.bak")
    4. CreateDB_Backup(CStr(Destination_SelectedRow("PDM_System")), "DestinationDB" & System.DateTime.Now.ToString("yyyyMMdd_HHmm") & ".bak")
    5. Dim Destination_SQL_Connection As SqlClient.SqlConnection = New SqlClient.SqlConnection
    6. Destination_SQL_Connection.ConnectionString = "Data Source =" & ServerName & "; User ID=" & UserName & " ;Password=" & PW
    7. Destination_SQL_Connection.Open()
    8. Dim CopyCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("ALTER DATABASE " & CStr(Destination_SelectedRow("PDM_System")) & " SET OFFLINE WITH ROLLBACK IMMEDIATE", Destination_SQL_Connection)
    9. CopyCommand.ExecuteNonQuery()
    10. CopyCommand = New SqlClient.SqlCommand("DROP DATABASE " & CStr(Destination_SelectedRow("PDM_System")), Destination_SQL_Connection)
    11. CopyCommand.ExecuteNonQuery()
    12. 'RESTORE DATABASE [GOK_TEST_PDM] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\SourceDB.bak' WITH FILE = 1, MOVE N'GOK_SW_PDM' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\GOK_TEST_PDM.mdf', MOVE N'GOK_SW_PDM_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\GOK_TEST_PDM_log.ldf', NOUNLOAD, REPLACE, STATS = 5
    13. 'RESTORE DATABASE [GOK_TEST_PDM] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\SourceDB.bak' WITH FILE = 2
    14. Dim RestoreQuery As String = "RESTORE DATABASE " & CStr(Destination_SelectedRow("PDM_System")) & " FROM DISK = N'SourceDB.bak' WITH FILE = 1"
    15. ', MOVE N'GOK_SW_PDM' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\GOK_TEST_PDM.mdf'
    16. RestoreQuery = RestoreQuery & ", MOVE N'" & CStr(Source_SelectedRow("PDM_System")) & "' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\" & CStr(Destination_SelectedRow("PDM_System")) & ".mdf'"
    17. ', MOVE N'GOK_SW_PDM_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\GOK_TEST_PDM_log.ldf'
    18. RestoreQuery = RestoreQuery & ", MOVE N'" & CStr(Source_SelectedRow("PDM_System")) & "_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\" & CStr(Destination_SelectedRow("PDM_System")) & "_log.ldf'"
    19. ', NOUNLOAD, REPLACE, STATS = 5
    20. RestoreQuery = RestoreQuery & ", NOUNLOAD, REPLACE, STATS = 5"
    21. Debug.Print(RestoreQuery)
    22. CopyCommand = New SqlClient.SqlCommand(RestoreQuery, Destination_SQL_Connection)
    23. CopyCommand.ExecuteNonQuery()
    24. Destination_SQL_Connection.Close()
    25. Dim Destination_SQL_Connection_NewDB As SqlClient.SqlConnection = New SqlClient.SqlConnection
    26. Destination_SQL_Connection_NewDB.ConnectionString = "Data Source =" & ServerName & ";Initial Catalog=" & CStr(Destination_SelectedRow("PDM_System")) & "; User ID=" & UserName & " ;Password=" & PW
    27. Destination_SQL_Connection_NewDB.Open()
    28. CopyCommand = New SqlClient.SqlCommand("DISABLE TRIGGER ArchiveServers ON DATABASE", Destination_SQL_Connection_NewDB)
    29. CopyCommand.ExecuteNonQuery()
    30. CopyCommand = New SqlClient.SqlCommand("DISABLE TRIGGER Projects ON DATABASE", Destination_SQL_Connection_NewDB)
    31. CopyCommand.ExecuteNonQuery()
    32. Destination_SQL_Connection_NewDB.Close()
    33. ChangeDestinationValues(Workingtable)
    34. Destination_SQL_Connection_NewDB.Open()
    35. CopyCommand = New SqlClient.SqlCommand("ENABLE TRIGGER ArchiveServers ON DATABASE", Destination_SQL_Connection_NewDB)
    36. CopyCommand.ExecuteNonQuery()
    37. CopyCommand = New SqlClient.SqlCommand("ENABLE TRIGGER Projects ON DATABASE", Destination_SQL_Connection_NewDB)
    38. CopyCommand.ExecuteNonQuery()
    39. Destination_SQL_Connection_NewDB.Close()
    40. Catch ex As Exception
    41. MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace)
    42. End Try
    43. End Sub
    44. Public Sub ChangeDestinationValues(ByVal WorkingTables As DataTable)
    45. Dim FoundRow() As DataRow = WorkingTables.Select("PDM_Type = 'Destination'")
    46. For Each SelectedRow As DataRow In FoundRow
    47. If CStr(SelectedRow("Table_Name")) <> "NoSQLReading" Then
    48. Dim Destination_SQL_Connection As SqlClient.SqlConnection = New SqlClient.SqlConnection
    49. Destination_SQL_Connection.ConnectionString = "Data Source =" & ServerName & "Initial Catalog=" & CStr(SelectedRow("PDM_System")) & "; User ID=" & UserName & " ;Password=" & PW
    50. Destination_SQL_Connection.Open()
    51. Dim Querystring As String = "Select TOP (1) * From " & CStr(SelectedRow("Table_Name"))
    52. Dim Temp_SQLDataAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(Querystring, Destination_SQL_Connection)
    53. Dim SQL_Table As DataTable = New DataTable
    54. Temp_SQLDataAdapter.FillSchema(SQL_Table, SchemaType.Source)
    55. Temp_SQLDataAdapter.Fill(SQL_Table)
    56. For Each Temp_SelectedRow As DataRow In SQL_Table.Rows
    57. If CStr(SelectedRow("Table_Name")) <> "Projects" Then
    58. Temp_SelectedRow(CStr(SelectedRow("Table_CLM"))) = SelectedRow("Desc_Value")
    59. ElseIf CStr(SelectedRow("Table_Name")) = "Projects" Then
    60. If CInt(Temp_SelectedRow("ProjectID")) = 1 Then
    61. Temp_SelectedRow(CStr(SelectedRow("Table_CLM"))) = SelectedRow("Desc_Value")
    62. Exit For
    63. End If
    64. End If
    65. Next
    66. Temp_SQLDataAdapter.Update(SQL_Table)
    67. Destination_SQL_Connection.Close()
    68. End If
    69. Next
    70. End Sub


    Danke im Voraus :-)!
    Hallo,

    habe den Fehler gefunden. Der command zum Disablen/Enablen von Trigger ist wie folgt.

    VB.NET-Quellcode

    1. CopyCommand = New SqlClient.SqlCommand("DISABLE TRIGGER *TriggerName* ON *TabellenName*", Destination_SQL_Connection_NewDB)


    Das war etwas verwirrend in dem Link beschrieben, da mich 'ON Database' etwas irittiert hat. Die sternchen bitte wegdenken.