Trigger defined for Insert, Update

• bob pearson-184607

  SSC Rookie

  Points: 39

  More actions

  December 19, 2007 at 1:35 pm

  #201874

  Is there an "easy/quick" way to determine inside the trigger itself whether it is being fired because of an Insert or because of an Update when a trigger has been defined for Insert, Update?

  Without inquiring whether a row exists or not, is there a system variable that can determine this?

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  December 19, 2007 at 3:04 pm

  #761604

  If your trigger is For Update, Insert

  there is not any system information identifying what action caused the trigger to fire.

  You could duplicate your code, and create 1 trigger for insert, and 1 trigger for update, or test for Existing rows as you mentioned.

• robert.gerald.taylor

  SSChampion

  Points: 13133

  More actions

  September 12, 2012 at 9:54 pm

  #1537006

  You could do something like:

  DECLARE @operation as Varchar(10);

  DECLARE @Count as int;

  SET @operation = 'Inserted';

  SELECT @Count = COUNT(*) FROM DELETED;

  if @Count > 0

  BEGIN

  SET @operation = 'Deleted';

  SELECT @Count = COUNT(*) FROM INSERTED;

  IF @Count > 0

  SET @operation = 'Updated'

  END

  HTH,

  Rob