.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

Tip: `inserted` and `deleted` are tables so that they can represent the results of set operations. Designing a trigger on the assumption that they will always contain exactly one row is generally a bad plan. If you are absolutely certain there will never be more than one row then _please_ add a check for the number of rows and use `RaIsError` to explicitly inform those who come along later that they have attempted to execute an unacceptable statement. (`if ( select Count(*) from inserted ) > 1 RaIsError( 'FooTable_Insert: No more than one row may be processed.', 25, 42 ) with log`) — HABO, Nov 25 '14 at 03:10
Thanks for your answer. After some research into the one vs. many inserts problem for triggers I am very certain re writing my trigger to handle multiple inserts will solve my problem. I will post again as soon as I've tired it out. — user2502832, Nov 25 '14 at 07:14

score 2 · Accepted Answer · answered Nov 25 '14 at 23:24

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!

score 1 · Answer 2 · answered Nov 25 '14 at 03:33

1

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.

answered Nov 25 '14 at 03:33

romatthe

1,447
3
17
33

Thanks for your answer. 1. To understanding, adapter.update() could do any of INSERT, DELETE or UPDATE depending on the changes that were made in the data table. Is that not the case? 2. I did try using ON UPDATE instead of ON INSERT, this did not solve the problem. – user2502832 Nov 25 '14 at 07:04
Yes, that's correct, I did not see that you added an insert command as well. – romatthe Nov 25 '14 at 07:14

.Net SqlDataAdapter and triggers in SQL Server

2 Answers2