Adding a where clause in a trigger

• ram_kamboz2k7

  SSC-Addicted

  Points: 475

  More actions

  February 23, 2012 at 8:44 am

  #257265

  Hi Guys

  I have this trigger which simply inserts a row of data from one table to another.

  However, there is a dependency, only insert the row of data into the other table if the column 'DIALLER DATE' is not null.

  The trigger is below

  Create TRIGGER out_dialler_calllist ON call_list

  AFTER INSERT, update AS

  Declare

  @AccountNumberint,

  @CustomerNumber int,

  @WorklistIDint,

  @DiallerDatedatetime,

  @AccountSeqNumberint,

  @DateofRequestdatetime,

  @AccountReferencenvarchar,

  @TelephoneNumberint,

  @Balancedecimal,

  @sourcesystemvarchar,

  @ProcessdateDatetime,

  @statuscodenvarchar,

  @statusreasonnvarchar,

  @CustomerNamevarchar

  SET@DateofRequest = GETDATE()

  BEGIN

  SELECT @AccountReference=AccountReference, @TelephoneNumber=TelephoneNumber, @Balance=Balance, @CustomerName=CustomerName

  FROM INSERTED

  INSERT INTO out__calllist(AccountReference, TelephoneNumber, Balance, CustomerName)

  VALUES(@AccountReference, @TelephoneNumber, @Balance, @CustomerName)

  END

  GO

  What i was wondering is, how do i include the where clause and how?

  Hope you guys can help me, Thanks.

• drew.allen

  SSC Guru

  Points: 77013

  More actions

  February 23, 2012 at 9:12 am

  #1450943

  Your trigger is written under the assumption that there will only ever be one row inserted/updated at a time and you can't guarantee that will always be the case. You need to rewrite this using sets.

  Create TRIGGER out_dialler_calllist ON call_list

  AFTER INSERT, update AS

  BEGIN

  INSERT INTO out__calllist(AccountReference, TelephoneNumber, Balance, CustomerName)

  SELECT AccountReference, TelephoneNumber, Balance, CustomerName

  FROM INSERTED

  WHERE DiallerDate IS NOT NULL

  END

  GO

  You've also written a single trigger for inserts and updates. You may want to consider writing separate triggers, because you may want to update the secondary table when the primary table is updated instead of inserting a new record in the secondary table.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA