INSERT TRIGGER FIREs though No ROW Inserted to cause this

• Express12

  SSCoach

  Points: 18514

  More actions

  September 15, 2006 at 12:57 pm

  #169689

  I've created an INSERT trigger on my table.  It is firing even when 0 rows are INSERTED w/ the following operation -- the SQL INSERT is inside a Stored Proc invoked by a DTS pkg.  Do I need to specify keyword "AFTER" ?

  My INSERT Trigger Defintion 

  CREATE TRIGGER trgi_MyTable ON MyTable FOR INSERT

  AS

  BEGIN

   exec master..spr_send_cdosysmail

    @from       = 'MyAddress@mycompany.com',

    @to         = 'dbadmins@mycompany.com',

    @subject    ='My Job Failure',

    @body       ='<B>STEP: MyStep </B><BR> ATTRIBUTES: <Font Color=red>Message Here',

    @smtpserver ='mailhost',

    @bodytype   ='HTMLBody'

  END

  My SQL INSERT (inside my PROC)  the particular invocation of the INSERT/SELECT renders 0 rows SELECTed therefore 0 rows INSERTed - but the trigger still fires and transmits the eMail above

  INSERT INTO MyTable (col_1, col_2, col_3)

   SELECT DISTINCT T1.col_1, T1.col_2, T1.col_3

    FROM Table_1 T1 LEFT OUTER JOIN Table_2 T2 on T1.col_1 = T2.col_1

     WHERE T1.col_2 = 'stuff'

      GROUP BY T1.col_1, T1.col_2, T1.col_3

  BT

• Aaron Ingold

  SSCrazy Eights

  Points: 9070

  More actions

  September 15, 2006 at 2:46 pm

  #660829

  I'd always recommend your first line of your trigger is to check @@ROWCOUNT to see if any data was actually affected.

  IF @@ROWCOUNT=0 RETURN;

  I'd recommend that.