none
C# trigger vs. SQL 2008 Managment Studio RRS feed

  • Question

  • Hello, 

    I'm running into an issue getting SQL Scripts to translate into a C# trigger using VS2008 and SQL Server 2008 Management Studio.  I have developed a dummy data set to test my code and I can get the desired result when using the SQL script, but I can't seem to get it to work properly in C#. 

    This SQL Code fires properly:

    INSERT INTO Invoices (VendorID, InvoiceNumber, InvoiceDate,
    	InvoiceTotal, PaymentTotal, TermsID, InvoiceDueDate, PaymentDate)
    VALUES ('124', '99999999', '11-11-2010', 190, 190, 3, '11-11-2011', '11-11-2010'),
    	('124', '99999998', '11-11-2010', 100, 90, 3, '11-11-2011', '11-10-2010'),
    	('124', '99999997', '11-11-2010', 2000, 0, 3, '11-11-2011', NULL)
    
    INSERT INTO ShippingLabels
    	(VendorName, VendorAddress1, VendorAddress2,
    	VendorCity, VendorState, VendorZipCode)
    SELECT VendorName, VendorAddress1, VendorAddress2,
    	VendorCity, VendorState, VendorZipCode
    FROM Vendors
    WHERE VendorID = 124
    

    The correct result from the SQL code:

    Trigger FIRED
    
    (3 row(s) affected)
    
    (1 row(s) affected)
    

    This VS2008 test script fires properly:

    INSERT INTO Invoices (VendorID, InvoiceNumber, InvoiceDate,
    	InvoiceTotal, PaymentTotal, TermsID, InvoiceDueDate, PaymentDate)
    VALUES ('124', '99999999', '11-11-2010', 190, 190, 3, '11-11-2011', '11-11-2010'),
    	('124', '99999998', '11-11-2010', 100, 90, 3, '11-11-2011', '11-10-2010'),
    	('124', '99999997', '11-11-2010', 2000, 0, 3, '11-11-2011', NULL)
    

    The test script then fires this trigger:

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using Microsoft.SqlServer.Server;
    
    
    public partial class Triggers
    {
      [Microsoft.SqlServer.Server.SqlTrigger(
          Name = "tr_Invoices_Update_Shipping",
          Target = "Invoices",
          Event = "AFTER INSERT, UPDATE")]
     
      public static void tr_Invoices_Update_Trigger()
      {
        SqlConnection connection = new SqlConnection("Context connection=true");
        connection.Open();
    
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
    
        SqlTriggerContext tc = SqlContext.TriggerContext;
        if (tc.TriggerAction == TriggerAction.Insert)
        {
          //p465
          command.CommandText =
            "INSERT INTO ShippingLabels " +
              "(VendorName, VendorAddress1, VendorAddress2, " +
              "VendorCity, VendorState, VendorZipCode) " +
            "SELECT VendorName, VendorAddress1, VendorAddress2, " +
              "VendorCity, VendorState, VendorZipCode " +
            "FROM Vendors " +
            "WHERE VendorID = 124";
              //"(SELECT VendorID " +
              //"FROM Inserted " +
              //"WHERE (PaymentTotal = InvoiceTotal))";
     
        }
        else if (tc.TriggerAction == TriggerAction.Update)
        {
          //if (InvoiceTotal - PaymentTotal - CreditTotal == 0)
          //{
    
          //}
        }
    
        SqlContext.Pipe.Send("Trigger FIRED");
        connection.Close();
      }
    }
    
    

    Which yields the incorrect result:

    Trigger FIRED
    (3 row(s) affected)
    (0 row(s) returned)

    After stepping through the trigger code the watch on the command.CommandText shows that the text is getting properly loaded to the command.  Ultimately I want to have the WHERE statement read directly from the Inserted table, which I thought was my initial problem.  After simplifying my code I realized that there is something else causing the problem, though, and I'm just not seeing it.  So my questions are:

    1.  What could I be overlooking that would create the difference in results between the two codes?

    2.  Is the following code the proper use of the Inserted table in a trigger, or should I be looking for another way to pull a result set from long insert statements?

    command.CommandText =
            "INSERT INTO ShippingLabels " +
              "(VendorName, VendorAddress1, VendorAddress2, " +
              "VendorCity, VendorState, VendorZipCode) " +
            "SELECT VendorName, VendorAddress1, VendorAddress2, " +
              "VendorCity, VendorState, VendorZipCode " +
            "FROM Vendors " +
            "WHERE VendorID = " +
              "(SELECT VendorID " +
              "FROM Inserted " +
              "WHERE (PaymentTotal = InvoiceTotal))";
    

    Thanks in advance for any guidance.  :)

    Stash

    Sunday, May 1, 2011 4:06 AM

Answers

  • I seem to have figured out my issue with the trigger code.  Apparently I wasn't sending the command through the SQLContext.Pipe as I should have been.  I also modified the Insert statement subquery to use the DISTINCT modifier so only one line gets inserted into the target table if more than one invoice has the 0 balance.  This only works if all the VendorIDs are the same in the INSERT statment test script, but for right now this does what I need it to do.  The Inserted table works as I intended as well so both questions from my OP have been answered. 

    It's amazing what a few hours of sleep can do for your thought processes.  :D

          string sql =
            "INSERT INTO ShippingLabels " +
              "(VendorName, VendorAddress1, VendorAddress2, " +
              "VendorCity, VendorState, VendorZipCode) " +
            "SELECT VendorName, VendorAddress1, VendorAddress2, " +
              "VendorCity, VendorState, VendorZipCode " +
            "FROM Vendors " +
            "WHERE VendorID = " +
              "(SELECT DISTINCT VendorID " +
              "FROM Inserted " +
              "WHERE (PaymentTotal = InvoiceTotal))";
          
          SqlCommand selectCommand =
          new SqlCommand(sql, connection);
          SqlContext.Pipe.ExecuteAndSend(selectCommand);
    

    • Marked as answer by Pvt. Stash Sunday, May 1, 2011 7:54 PM
    Sunday, May 1, 2011 7:54 PM