d88b7323   
   From: esquel@sommarskog.se   
      
   Jed Fletcher (jedfletcher1@gmail.com) writes:   
   > I have a update statement   
   > UPDATE Stock SET Qty = " + Qty + " WHERE Code = '" + dr.Cells   
   > ["codeDataGridViewTextBoxColumn"].Value.ToString() + "'   
      
   No, no.   
      
      cmd.CommantText = "UPDATE dbo.Stock Set Qty = @qty WHERE Code = @code";   
      cmd.Parameters.Add("@qty", SqlDbType.Int);   
      cmd.Parameters.Value["@qty] = Qty;   
      cmd.Parameters.Add("@code", SqlDbType.VarChar, 4000);   
      cmd.Parameters.Value["@code"] =   
         dr.Cells["codeDataGridViewTextBoxColumn"].Value;   
      
   You should always use parameterised statments. You avoid risk for SQL   
   injection, and you utilise the SQL Server cache better. And the SQL   
   code is easier to read.   
      
   > I want this trigger to retrieve the parameters from this update   
   > statement and put them into another table.In short I want to update   
   > the stock table and if the updated Qty is not equal to the original   
   > Qty i want to insert the Qty into the variance table.   
   >   
   > CREATE TRIGGER VarianceReport   
   > ON Stock   
   > INSTEAD OF UPDATE   
   >   
   > AS   
   > IF (Stock.Qty <> u.Qty)   
   >  BEGIN   
   >     SET NOCOUNT ON   
   >     INSERT INTO Variance(Qty)   
   >     SELECT u.Qty   
   >     FROM updated u   
   >  END   
      
   If I understand this correctly, you want   
      
      
   CREATE TRIGGER VarianceReport ON Stock AFTER UPDATE   
      INSERT Variance(Qty)   
         SELECT d.Qty   
         FROM   inserted i   
         JOIN   deleted d ON i.keycol = d.keycol   
         WHERE  i.qty <> d.qty   
      
      
   I changed the trigger to an AFTER trigger, because I could not see any   
   point with having it as an AFTER trigger.   
      
   In a trigger you have access to the virtual tables inserted and deleted   
   which holds the data after and before the update respectively. You also   
   have to keep in mind that a trigger fires once per statment, and these   
   table thus can include many rows.   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)