none
how to disable SQL Table trigger to insert a row RRS feed

  • Question

  • I am new to vb.net and am trying to insert a row to a table and am running into a trigger throwing a "specified cast is not valid vb.net" error and I am assuming it's the trigger stopping the insert command from completing.  My data is correct, I have validated it manually and believe if I stop the trigger I can force the entry.  of course I am in a test DB to do this.  I just do not know nor can I find help online to complete the code.  Please forgive the amateur coding.

    HT16DataSet.EnforceConstraints = False

    HT16DataSet.INVENTORY_TRANSDataTable ----  Here's where I think I need to disable the trigger.

    Me.INVENTORY_TRANSTableAdapter.FillByMaxID(HT16DataSet.INVENTORY_TRANS)

    nextID = HT16DataSet.INVENTORY_TRANS.Rows(0)("TRANSACTION_ID") + 1

    Me.HT16DataSet.INVENTORY_TRANS.Clear()

            Try
                'Me.INVENTORY_TRANSTableAdapter.InsertQuery(nextID, PO, PO_Line, PartNo, Type, ClassR, CDec(Shipped), 0, datetouse, Whse, Loc, NLstr, NLstr, Post, (CDec(Shipped) * CDec(matl_cost)), (CDec(Shipped) * CDec(labor_cost)), (CDec(Shipped) * CDec(bur_cost)), (CDec(Shipped) * CDec(ser_cost)), datetouse, CC, SiteID)

    Me.INVENTORY_TRANSTableAdapter.Insert(nextID, NLstr, NLstr, NLstr, NLstr, NLstr, NLint, NLint, NLstr, NLint, PO, PO_Line, PartNo, Type, ClassR, CDec(Shipped), 0, datetouse, Whse, Loc, NLstr, uname, Nfld, NLstr, (CDec(Shipped) * CDec(matl_cost)), (CDec(Shipped) * CDec(labor_cost)), (CDec(Shipped) * CDec(bur_cost)), (CDec(Shipped) * CDec(ser_cost)), datetouse, NLint, NLint, NLint, NLint, NLint, NLstr, NLint, NLstr, NLstr, NLint, NLint, NLstr, NLstr, NLstr, NLstr, NLstr, NLint, NLstr, NLstr, NLstr, NLstr, NLstr, NLstr, NLstr, NLstr, NLstr, datetouse, 1, SiteID, NLstr, NLstr, NLstr, NLstr)

    Catch ex As Exception
                System.Windows.Forms.MessageBox.Show(ex.Message & " Inv Trans")
            End Try

    Thursday, August 15, 2019 2:56 PM

Answers

  • When using TableAdapters my guess it may not be possible, instead create a class, create an new instance of the class and call the methods e.g.

    Imports System.Data.SqlClient
    
    Public Class TriggerWork
        Private ConnectionString As String = "Data Source=YOURSERVERNAME;" &
                                             "Initial Catalog=YOURTABLENAME;" &
                                             "Integrated Security=True"
        Public Sub DisableTrigger()
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "DISABLE TRIGGER YourTableName ON DATABASE"
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
        Public Sub EnableTrigger()
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "ENABLE TRIGGER YourTableName ON DATABASE"
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by MarkSLaRosa Thursday, August 15, 2019 6:09 PM
    Thursday, August 15, 2019 5:55 PM
    Moderator

All replies

  • Hello,

    Using a valid connection setup a command and configure a query for disabling the trigger in a try/catch/finally. in the finally part of the try write another query and execute.

    The first query uses DISABLE TRIGGER while the second uses ENABLE TRIGGER for SQL-Server.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, August 15, 2019 4:45 PM
    Moderator
  • That sounds like what I need to do.  My command I am assuming would look like this:

    DISABLE TRIGGER safety ON DATABASE; GO

    My problem is, is the command a query that's created by right clicking on the dataset.xsd tab? This is all new to me. If it's not too much of a bother do you have any sample code you can show me?

    thanks, Mark

    Thursday, August 15, 2019 5:36 PM
  • When using TableAdapters my guess it may not be possible, instead create a class, create an new instance of the class and call the methods e.g.

    Imports System.Data.SqlClient
    
    Public Class TriggerWork
        Private ConnectionString As String = "Data Source=YOURSERVERNAME;" &
                                             "Initial Catalog=YOURTABLENAME;" &
                                             "Integrated Security=True"
        Public Sub DisableTrigger()
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "DISABLE TRIGGER YourTableName ON DATABASE"
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
        Public Sub EnableTrigger()
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "ENABLE TRIGGER YourTableName ON DATABASE"
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by MarkSLaRosa Thursday, August 15, 2019 6:09 PM
    Thursday, August 15, 2019 5:55 PM
    Moderator
  • Thank you very much for your help.  This should get me where I need to be!

    Mark

    Thursday, August 15, 2019 6:10 PM