The trigger is very simple. ( I hate that we have this trigger but unfortunately not on the books to fix everywhere).
The only table involved is the Patient table. I was wondering if there was a conflict between the Primary Key Nonclusterindex and the clustered index.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--===========================================================================
-- TR -- IU -- PATIENT -- CHANGE TIME
--===========================================================================
CREATE TRIGGER [dbo].[tr_IU_Patient_ChangeTime] ON [dbo].[Patient]
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @error_var int
SET @error_var = 0
DECLARE @proc_name sysname
SET @proc_name = (SELECT name FROM sysobjects WHERE id = @@PROCID)
DECLARE @CRLF char(2)
SET @CRLF = CHAR(13) + CHAR(10)
DECLARE @err_message nvarchar(255)
IF UPDATE(DOB)
BEGIN
UPDATE P
SET DOB = dbo.fn_ReplaceTimeInDate(i.DOB)
FROM dbo.Patient P INNER JOIN
inserted i ON
P.PatientID = i.PatientID
SET @error_var = @@ERROR
--Error checking
IF @error_var > 0
GOTO rollback_tran
END
RETURN
rollback_tran:
IF @err_message IS NULL
SET @err_message = 'Rolling back transaction - ' + @proc_name + ' - ' + CONVERT(varchar(30), GETDATE(), 121)
ELSE
SET @err_message = 'Rolling back transaction - ' + @proc_name + ' - ' + CONVERT(varchar(30), GETDATE(), 121) + @CRLF + @CRLF + @err_message
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
RAISERROR(@err_message, 16,1)
RETURN
END
GO
CREATE TABLE [dbo].[Patient](
[PatientID] [int] IDENTITY(1,1) NOT NULL,
[PracticeID] [int] NOT NULL,
[ReferringPhysicianID] [int] NULL,
[Prefix] [varchar](16) NOT NULL,
[FirstName] [varchar](64) NOT NULL,
[MiddleName] [varchar](64) NOT NULL,
[LastName] [varchar](64) NOT NULL,
[Suffix] [varchar](16) NOT NULL,
[AddressLine1] [varchar](256) NULL,
[AddressLine2] [varchar](256) NULL,
[City] [varchar](128) NULL,
[State] [varchar](2) NULL,
[Country] [varchar](32) NULL,
[ZipCode] [varchar](9) NULL,
[Gender] [varchar](1) NULL,
[MaritalStatus] [varchar](1) NULL,
[HomePhone] [varchar](10) NULL,
[HomePhoneExt] [varchar](10) NULL,
[WorkPhone] [varchar](10) NULL,
[WorkPhoneExt] [varchar](10) NULL,
[DOB] [datetime] NULL,
[SSN] [char](9) NULL,
[EmailAddress] [varchar](256) NULL,
[ResponsibleDifferentThanPatient] [bit] NULL,
[ResponsiblePrefix] [varchar](16) NULL,
[ResponsibleFirstName] [varchar](64) NULL,
[ResponsibleMiddleName] [varchar](64) NULL,
[ResponsibleLastName] [varchar](64) NULL,
[ResponsibleSuffix] [varchar](16) NULL,
[ResponsibleRelationshipToPatient] [varchar](1) NULL,
[ResponsibleAddressLine1] [varchar](256) NULL,
[ResponsibleAddressLine2] [varchar](256) NULL,
[ResponsibleCity] [varchar](128) NULL,
[ResponsibleState] [varchar](2) NULL,
[ResponsibleCountry] [varchar](32) NULL,
[ResponsibleZipCode] [varchar](9) NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedUserID] [int] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[ModifiedUserID] [int] NOT NULL,
[RecordTimeStamp] [timestamp] NOT NULL,
[EmploymentStatus] [char](1) NULL,
[InsuranceProgramCode] [char](2) NULL,
[PatientReferralSourceID] [int] NULL,
[PrimaryProviderID] [int] NULL,
[DefaultServiceLocationID] [int] NULL,
[EmployerID] [int] NULL,
[MedicalRecordNumber] [varchar](128) NULL,
[MobilePhone] [varchar](10) NULL,
[MobilePhoneExt] [varchar](10) NULL,
[PrimaryCarePhysicianID] [int] NULL,
[VendorID] [varchar](50) NULL,
[VendorImportID] [int] NULL,
[CollectionCategoryID] [int] NULL,
[Active] [bit] NOT NULL,
[SendEmailCorrespondence] [bit] NULL,
[PhonecallRemindersEnabled] [bit] NOT NULL,
[EmergencyName] [varchar](128) NULL,
[EmergencyPhone] [varchar](10) NULL,
[EmergencyPhoneExt] [varchar](10) NULL,
[PatientGuid] [uniqueidentifier] NOT NULL,
[Ethnicity] [varchar](64) NULL,
[Race] [varchar](64) NULL,
[LicenseNumber] [varchar](64) NULL,
[LicenseState] [varchar](2) NULL,
[Language1] [varchar](64) NULL,
[Language2] [varchar](64) NULL,
CONSTRAINT [PK_Patient] PRIMARY KEY NONCLUSTERED
(
[PatientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE UNIQUE CLUSTERED INDEX [CI_Patient_PracticeID_PatientID] ON [dbo].[Patient]
(
[PracticeID] ASC,
[PatientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Patient_FirstName] ON [dbo].[Patient]
(
[FirstName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Patient_ModifiedDate_PracticeID] ON [dbo].[Patient]
(
[ModifiedDate] DESC,
[PracticeID] ASC
)
INCLUDE ( [PatientID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Patient_PracticeID_Active_FOR_GetPatients] ON [dbo].[Patient]
(
[PracticeID] ASC,
[Active] ASC,
[LastName] ASC,
[FirstName] ASC,
[MiddleName] ASC
)
INCLUDE ( [PatientID],
[AddressLine1],
[AddressLine2],
[City],
[State],
[ZipCode],
[HomePhone],
[SSN],
[ResponsibleFirstName],
[ResponsibleLastName],
[MedicalRecordNumber],
[DOB]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Patient_PracticeID_DefaultServiceLocationID] ON [dbo].[Patient]
(
[PracticeID] ASC,
[DefaultServiceLocationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_Patient_ReferringPhysicianID] Script Date: 03/06/2013 11:55:36 ******/
CREATE NONCLUSTERED INDEX [IX_Patient_ReferringPhysicianID] ON [dbo].[Patient]
(
[ReferringPhysicianID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_Patient_SSN] Script Date: 03/06/2013 11:55:36 ******/
CREATE NONCLUSTERED INDEX [IX_Patient_SSN] ON [dbo].[Patient]
(
[SSN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [UX_Patient_Guid] Script Date: 03/06/2013 11:55:36 ******/
CREATE UNIQUE NONCLUSTERED INDEX [UX_Patient_Guid] ON [dbo].[Patient]
(
[PatientGuid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_CollectionCategory] FOREIGN KEY([CollectionCategoryID])
REFERENCES [dbo].[CollectionCategory] ([CollectionCategoryID])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_CollectionCategory]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_Doctor] FOREIGN KEY([PrimaryProviderID])
REFERENCES [dbo].[Doctor] ([DoctorID])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_Doctor]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_Employers] FOREIGN KEY([EmployerID])
REFERENCES [dbo].[Employers] ([EmployerID])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_Employers]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_InsuranceProgram] FOREIGN KEY([InsuranceProgramCode])
REFERENCES [dbo].[InsuranceProgram] ([InsuranceProgramCode])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_InsuranceProgram]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_PatientReferralSource] FOREIGN KEY([PatientReferralSourceID])
REFERENCES [dbo].[PatientReferralSource] ([PatientReferralSourceID])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_PatientReferralSource]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_Practice] FOREIGN KEY([PracticeID])
REFERENCES [dbo].[Practice] ([PracticeID])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_Practice]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_PrimaryCarePhysicianID] FOREIGN KEY([PrimaryCarePhysicianID])
REFERENCES [dbo].[Doctor] ([DoctorID])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_PrimaryCarePhysicianID]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_ReferringPhysician] FOREIGN KEY([ReferringPhysicianID])
REFERENCES [dbo].[Doctor] ([DoctorID])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_ReferringPhysician]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_ServiceLocationID] FOREIGN KEY([DefaultServiceLocationID])
REFERENCES [dbo].[ServiceLocation] ([ServiceLocationID])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_ServiceLocationID]
GO
ALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate]
GO
ALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_CreatedUserID] DEFAULT (0) FOR [CreatedUserID]
GO
ALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]
GO
ALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_ModifiedUserID] DEFAULT (0) FOR [ModifiedUserID]
GO
ALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[Patient] ADD DEFAULT ((1)) FOR [SendEmailCorrespondence]
GO
ALTER TABLE [dbo].[Patient] ADD DEFAULT ((0)) FOR [PhonecallRemindersEnabled]
GO
ALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_Guid] DEFAULT (newid()) FOR [PatientGuid]
GO