CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012triggers
lotwan
lotwan

Reputation: 3

Trigger will not fire at all after bulkcopy

I want to update the inserted records after using bulkcopy to insert them into the database. I want have written a trigger to do the update for me after the insert. The trigger works fine if I insert the records one after the other but refuses to work after a bulkcopy insert.

I have added the fire trigger (SqlBulkCopyOptions.FireTriggers & SqlBulkCopyOptions.CheckConstraints) to the connection but it will still not work.

This is the trigger I wrote:

CREATE TRIGGER [dbo].[Trigger_assessment]
ON [dbo].[assessment]
AFTER INSERT 
AS
BEGIN
    SET NoCount ON

    Declare @a as int
    Declare @b as int

    SELECT @b = exam_score, @a = ca 
    FROM inserted

    UPDATE assessment 
    SET final_ca = ca*0.3, final_exam = exam_score * 0.7,
        f_score = ((ca * 0.3) + (exam_score * 0.7)) 
    WHERE exam_score = @b AND ca = @a
END

and also the bulk copy:

     Dim koneksi_excel As New      System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & path1.Text & " '; Extended Properties=""Excel 12.0 xml;HDR=yes""")

    koneksi_excel.Open()
    Dim query_excel As String = "select * from [Sheet1$]"

    Dim cmd As OleDbCommand = New OleDbCommand(query_excel, koneksi_excel)

    Dim rd As OleDbDataReader

    Dim koneksi As New SqlConnection()

    If mysqlconnectionstring.State = ConnectionState.Closed Then
        mysqlconnectionstring.Open()
    End If
    Using BulkCopy As SqlBulkCopy = New SqlBulkCopy(ConfigurationManager.ConnectionStrings("SchoolDBConnectionString").ConnectionString.ToString, SqlBulkCopyOptions.FireTriggers & SqlBulkCopyOptions.CheckConstraints)

        BulkCopy.DestinationTableName = "dbo.assessment"

        Try

            rd = cmd.ExecuteReader
            BulkCopy.WriteToServer(rd)
            rd.Close()
            mysqlconnectionstring.Close()
            MsgBox("Data import succesful", MsgBoxStyle.Information, "imports")
            path.Text = ""
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            mysqlconnectionstring.Close()
        End Try
    End Using

I want assistance Please, thank you

Upvotes: 0

Views: 571

Answers (1)

TT.
TT.

Reputation: 16146

The inserted virtual table is a table, and so can have more than one row. If you do a bulk insert (or update), this table will have more than one row in it. You need to take this into account. You can read more about that here: Use the inserted and deleted Tables.

Your corrected trigger would be:

CREATE TRIGGER [dbo].[Trigger_assessment]
ON [dbo].[assessment]
after insert
AS
BEGIN
    SET NoCount ON

    update 
        assessment 
    set 
        final_ca=a.ca*0.3,
        final_exam=a.exam_score*0.7,
        f_score=((a.ca*0.3)+(a.exam_score*0.7)) 
    from
        assessment AS a
        inner join inserted as i on
            i.ca=a.ca and
            i.exam_score=a.exam_score;
END

Upvotes: 2

Related Questions