November 26, 2012 at 11:54 am
I currently have a table that contains patient information. There is a trigger on this table for every update and insert , that updates the DateOfBirth(DOB) field to Midnight if it is not set to that. Obviously this is a horrible idea. It is causing deadlocks in our system. Ideally I would like to update all the stored procedures that currently update this field and convert them upon insert and remove the trigger. In Sql Server 2005 there is no way to do this at the table level correct? Since there is no dateonly data type? My concern is if there is any embedded code in our application that is inserting into the table that could be missed.
GO
/****** Object: Table [dbo].[Patient] Script Date: 11/26/2012 10:46:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
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,
[Guid] [uniqueidentifier] NOT 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
USE [superbill_7300_dev]
/****** Object: Index [CI_Patient_PracticeID_PatientID] Script Date: 11/26/2012 10:46:54 ******/
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, FILLFACTOR = 100) ON [PRIMARY]
GO
USE [superbill_7300_dev]
/****** Object: Index [IX_Patient_FirstName] Script Date: 11/26/2012 10:46:54 ******/
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
USE [superbill_7300_dev]
/****** Object: Index [IX_Patient_ModifiedDate_PracticeID] Script Date: 11/26/2012 10:46:54 ******/
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
USE [superbill_7300_dev]
/****** Object: Index [IX_Patient_PracticeID_Active_FOR_GetPatients] Script Date: 11/26/2012 10:46:54 ******/
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, FILLFACTOR = 100) ON [PRIMARY]
GO
USE [superbill_7300_dev]
/****** Object: Index [IX_Patient_PracticeID_DefaultServiceLocationID] Script Date: 11/26/2012 10:46:54 ******/
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
USE [superbill_7300_dev]
/****** Object: Index [IX_Patient_ReferringPhysicianID] Script Date: 11/26/2012 10:46:54 ******/
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
USE [superbill_7300_dev]
/****** Object: Index [IX_Patient_SSN] Script Date: 11/26/2012 10:46:54 ******/
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
USE [superbill_7300_dev]
/****** Object: Index [UX_Patient_Guid] Script Date: 11/26/2012 10:46:54 ******/
CREATE UNIQUE NONCLUSTERED INDEX [UX_Patient_Guid] ON [dbo].[Patient]
(
[Guid] 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 [Guid]
GO
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
November 26, 2012 at 12:04 pm
If you can't count on catching all the entries into the table (possible inline code), then a trigger is probably your best option in SQL 2005.
However, you can probably speed up the trigger, and reduce the chance of deadlocks, if you replace this:
SET DOB = dbo.fn_ReplaceTimeInDate(i.DOB)
with:
SELECT DATEADD(day, DATEDIFF(day, 0, i.DOB), 0);
What that does is nest a DateDiff(Day) inside a DateAdd(Day), using "0" (you can use a real date in there if it makes it more readable for you; some people prefer '19000101' instead of 0). By doing so, it finds the number of days from that baseline to the datetime value, then adds it back to the baseline. This eliminates hours, minutes, seconds, and milliseconds, from a DateTime datatype.
It will certainly be faster than a UDF call, even if the UDF is extremely well-written and optimized.
Alternatively to a trigger would be having a scheduled job go through and retrofix the data after it's been inserted/updated. That may or may not be a viable solution for you, depending on how the data is used.
So, till you know better how the data will be used, try modifying the trigger.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 26, 2012 at 12:09 pm
You could also have a computed column that is similar to what G2 suggested.
As a side note, you should try to avoid goto's in your code like in that trigger. There is a try-catch construct in sql now that is far better for this type of thing.
Also, the else condition will never be met in this code:
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
You declared @err_message but nothing ever sets it. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 26, 2012 at 12:18 pm
Thanks for the responses. In terms of a computed column, I would then have to create a new column that would take the current DOB and then convert it correct? Which means all the stored procedures would be need to be updated to refer to the new column correct? There is no way to convert the data upon insert other than the trigger.
November 26, 2012 at 12:21 pm
Computed column won't work if there are applications that directly access the table. They'll pull the existing column in Selects, and modify it in it Update and Inserts, so either they'll get the wrong data in Selects, or get error messages if they try to DML the computed column (if you give it the name of the current column).
The error-handling part of the trigger looks like it's probably cut-and-paste from a coding standard for the company. If so, might not be allowed to edit it. It's probably a legacy of an even older SQL 2000 Server code-base. I've seen that lots of times.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply