.Net SqlDataAdapter和SQL Server中的触发器

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.

我在SQL Server中使用一个触发器，它在SQL Server Management Studio的查询窗口中执行查询时按需要工作。触发器的目标是从一个表中获取最新值（其中id对应于插入的id）并将此值添加到插入的行。

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.

我还在C＃中使用DataAdapter与具有触发器的同一数据库进行交互。当我使用MyAdapter.update（MyDataTable）将新值插入到分配了触发器的表中时，触发器不会执行。

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.

我做了很多谷歌搜索，但没有其他人似乎有这个问题，所以我想我错过了一些基本的东西。我也是.Net与数据库交互的新手。数据适配器正常工作（即根据需要插入和更新），但不触发触发器。

Below are some excerpts from my C# code and the trigger.

下面是我的C＃代码和触发器的一些摘录。

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.

问题摘要：触发器在SQL Server中有效，但在使用数据适配器时会触发（也不会抱怨）。

Thanks for your time and help!

感谢您的时间和帮助！

Marvin

马文

2 个解决方案

#1

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

INSERT后触发触发器。使用SQLDataAdapter，您正在执行更新。这是两种截然不同的交易。

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

尝试将触发器设置为ON UPDATE。这应该够了吧。

#2

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:

按照HABO的提示（在原始帖子下面），我修改了我的触发器以适用于多个插入的行。这解决了我的问题。新的触发器代码如下：

 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!

谢谢你的帮助！

#1