Trigger for multi-row insert RRS feed

  • Question

  • Hi!

         I have a table, table1, that may be populated using a simple insert, or using  "insert into table1 select c1, c2 from table2". 

        I need a trigger to calculate a column of table1, that for a simple insert would look more or less like this:

    SELECT @c1 = c1, @c2 = c2, @c3 =c3, @c4 = c4


    SET @c5 = some calculation using @c1, @c2, @c3, @c4

    UPDATE table1 SET c5 = @c5

    WHERE c1 = @c1 AND c2 = @c2 AND c3 = @c3

    But it doesn't work when I use "insert into table1 select c1, c2 from table2". How could I do a trigger that would work for both cases?

    Thank you!


    Wednesday, December 27, 2006 1:11 PM


  • Ana:

    This might work for you:

    UPDATE table1
       SET c5 = <some calculation using a.c1, a.c2, a.c3, a.c4>
     INNER JOIN table1 a
    WHERE I.c1 = a.c1
      AND I.c2 = a.c2
      AND I.c3 = a.c3

    -- ---------------------------------------------------------
    --  H O W E V E R !
    --  This looks like an exceedingly dangerous query!
    --  The reason this looks so dangerous is that I have
    --  grave doubt that this query is properly indexed and
    --  without proper indexing this table will be processed
    --  for update with a table scan; under these circumstances
    --  this trigger is likely to be a source of (1) blockage
    --  and (2) deadlocks.  This is not something you want
    --  to be caused by a trigger!
    --  Be sure that this query has an index that accommodes
    --  an INDEX SEEK for your WHERE clause.
    -- ---------------------------------------------------------


    Wednesday, December 27, 2006 1:31 PM