.Net SqlDataAdapter and triggers in SQL Server

Question

I am using a trigger in SQL Server that works as required when executing a query in the query window in SQL Server Management Studio. The objective of the trigger is to take the latest value from one table (where an id corresponds to the inserted id) and add this value to the inserted row.

I am also using a DataAdapter in C# to interact with the same database that has the trigger. When I use MyAdapter.update(MyDataTable) to insert new values into the table that the trigger is assigned to, the trigger does not execute.

I have done a lot of googling but nobody else seems to have that problem so I am thinking I am missing something fundamental. I am also new to database interaction with .Net. The data adapter works properly (i.e. inserts and updates as needed) except for not firing the trigger.

Below are some excerpts from my C# code and the 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());
        }
    }

Question summary: the trigger works in SQL Server, but does fire (nor complains) when using a data adapter.

Thanks for your time and help!

Marvin

Answer 1

Following HABO's Tip (below original post) I modified my trigger to work for multiple inserted rows. This has solved my problem. New trigger 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

Thanks for your help!

Answer 2

Your firing the trigger after INSERT. With the SQLDataAdapter you're performing an UPDATE. Those are two very different types of transactions.

Try setting your trigger to ON UPDATE. That should do the trick.