.Net SqlDataAdapter and Triggers in SQL Server

Question

.Net SqlDataAdapter and Triggers in SQL Server

I am using a trigger in SQL Server that works when needed when executing a query in a query window in SQL Server Management Studio. The purpose of a trigger is to take the last value from one table (where id corresponds to the inserted id) and append that value to the inserted row.

I am also using DataAdapter

in C # to interact with the same database that has a trigger. When I use MyAdapter.update(MyDataTable)

to insert new values into the table that the trigger is assigned to, the trigger fails.

I've done a lot of searches, but someone else doesn't have this problem, so I think I'm missing something fundamental. I am also new to interacting with a database with .Net. The data adapter works correctly (i.e., inserts and updates as needed), except that it does not fire the trigger.

Following are some snippets of C # code and trigger.

CREATE TRIGGER getLatestCap 
ON  TestIDTable 
AFTER insert
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @BID INT;
    DECLARE @Date Date;

    SET @BID = (SELECT BattID FROM inserted);
    SET @Date = (SELECT Test_Date FROM inserted);

    SELECT M_Cap, Cap_Date
    INTO #tempTable
    FROM CapDataTable
    WHERE BattID = @BID;

    -- Set the Test_Cap entry in TestIDTable to that capacity. 
    UPDATE TestIDTable 
    SET Test_Cap = (SELECT M_Cap
                    FROM #tempTable
                    WHERE Cap_Date = (SELECT max(Cap_Date)
                                      FROM #tempTable))
    WHERE BattID = @BID AND Test_Date = @Date;
END
GO

private void Setup()
{
        try
        {
            string BattSelect = "SELECT * FROM " + tbl;
            dt = new DataTable();
            Adpt = new SqlDataAdapter(BattSelect, ConnectionStr);
            builder = new SqlCommandBuilder(Adpt);
            Adpt.Fill(dt);
        }
        catch (Exception e)
        {
            MessageBox.Show("While Connecting to "+tbl+": " + e.ToString());
        }
    }

    private void UpdateDB()
    {
        try
        {
            Adpt.InsertCommand = builder.GetInsertCommand();
            Adpt.UpdateCommand = builder.GetUpdateCommand();
            Adpt.Update(dt);
        }
        catch (Exception e)
        {
            MessageBox.Show("While Updating " + tbl + ": " + e.ToString());
        }
    }

Summary of the question: The trigger works in SQL Server, but fires (and doesn't complain) when using the data adapter.

Thanks for your time and help!

Marvin

+3

c # sql sql-server triggers sqldataadapter

user2502832 25 nov. '14 at 2:25

source to share

2 answers

You fire the trigger after the INSERT. With SQLDataAdapter you are doing an UPDATE. These are two very different types of transactions.

Try setting the trigger to ON UPDATE. This should do the trick.

+2

Robin mattheussen 25 nov. '14 at 3:33

source to share

user2502832 · Accepted Answer · 2014-11-25T23:24:00+0000

Following the HABO Tip (below the original post), I modified my trigger to work for multiple inserted rows. This solved my problem. New startup code below:

 CREATE TRIGGER getLatestCap
    ON TestIDTable
    AFTER insert
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE TestIDTable 
    set Test_Cap = M_Cap
    FROM 
        (SELECT C.BattID, Test_Date, M_Cap
        FROM 
            (SELECT t.BattID, t.M_Cap, t.Cap_Date 
            FROM CapDataTable t
            INNER JOIN(
                SELECT BattID, max(Cap_Date) as Latest
                FROM CapDataTable
                GROUP BY BattID
            ) tm on t.BattID = tm.BattID and t.Cap_Date = tm.Latest)

         C INNER JOIN inserted I
            on C.BattID = I.BattID) as t1
        INNER JOIN TestIDTable as t2
        on  t1.BattID = t2.BattID AND t1.Test_Date = t2.Test_Date
END
GO

Thank you for your help!

.Net SqlDataAdapter and Triggers in SQL Server

More articles: