Update Trigger Using Inserted Table

• mpd777

  Mr or Mrs. 500

  Points: 573

  More actions

  August 13, 2010 at 10:31 am

  #236660

  I'm writing an update trigger and using the Inserted table to update another table.

  Code is below. It executes on an update of the table, but when I try to use the "INSERTED"

  table to insert into another table "AppointmentStatusTransactions", the data from the INSERTED table is not correct. It works the first time I fire the trigger with an upgrade, but subsequent upgrades (and firing of the trigger) provides the data from the first time the trigger is fired. I'm thinking that the SELECT statement on the INSERTED table is seeing multiple rows and the last row is the row that is being used in inserting into the table "AppointmentStatusTransactions".

  Is my assumption correct and is there away around this?

  Thanks

  Code starts:

  USE [PRM_2009_P2]

  GO

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  -- =========================================

  ALTER TRIGGER [dbo].[ApptTranTrig5]

  ON [dbo].[Appointment]

  AFTER UPDATE

  AS

  IF UPDATE(AppointmentStatusUID)

  BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from

  -- interfering with SELECT statements.

  SET NOCOUNT ON;

  INSERT INTO dbo.AppointmentStatusTransactions (AppointmentUID,AppointmentStatusUID,PatientUID,ServiceSiteUID,StatusStartTime,StatusModifiedBy)

  SELECT i.AppointmentUID,i.AppointmentStatusUID,i.PatientUid,i.ServiceSiteUID,i.LastModifiedDate,i.LastModifiedByUID

  FROM INSERTED AS I;

  END

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  August 23, 2010 at 4:27 am

  #1210339

  Correct "ON clause" columns as per requirement

  ALTER TRIGGER [dbo].[ApptTranTrig5]

  ON [dbo].[Appointment]

  AFTER UPDATE

  AS

  IF UPDATE(AppointmentStatusUID)

  BEGIN

  SET NOCOUNT ON;

  INSERT INTO dbo.AppointmentStatusTransactions

  (

  AppointmentUID,

  AppointmentStatusUID,

  PatientUID,

  ServiceSiteUID,

  StatusStartTime,

  StatusModifiedBy

  )

  SELECT

  i.AppointmentUID,

  i.AppointmentStatusUID,

  i.PatientUid,

  i.ServiceSiteUID,

  i.LastModifiedDate,

  i.LastModifiedByUID

  FROM INSERTED i INNER JOIN

  [dbo].[Appointment] A ON

  A.AppointmentUID = I.AppointmentUID

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)