Stuck on what I am sure is very simple problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stuck on what I am sure is very simple problem

Author

Topic

scholin
Yak Posting Veteran

56 Posts

Posted - 2009-09-04 : 10:29:38

I have a simple trigger that creates a net table which works just fine. I want this trigger to bring over two more columns along with the "Patient_ID"; "DOS and "Full_Name".

I am not sure how to code this - I have tried several different ways. I know this should be easy but . . . .

Here is my existing trigger:

ALTER TRIGGER [dbo].[Create_New_IOM_Test_Rec]
ON [dbo].[Patient]
after INSERT
AS
insert dbo.IOM_Tests(Patient_ID)
select DISTINCT Patient_ID from inserted i
where not exists (select * from dbo.IOM_Tests where Patient_ID = i.Patient_ID)

Thank you!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-04 : 10:35:39

Do these columns exist in both Patient and IOM_Tests ?
What do you want to happen if there are multiple rows for the same patient with different DOS values ? If there is only a one-to-one between patient_id, DOS, and Full_name then you probably don't want to repeat those value in different tables anyway. Why not just use the patient_id as a foreign key.

Be One with the Optimizer
TG

scholin
Yak Posting Veteran

56 Posts

Posted - 2009-09-04 : 12:40:34

TG,

Thank you for the response. Yes in this case it will always be a one-to-one relationship. At some point down the road it will eventually end up being a conditional one-to-one but for now just about every patient will have this test.

So how else could I always make sure that the date of service (DOS) and Full_name is inserted and stays updated?

Thanks,

Mike

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-04 : 14:27:36

I guess what I'm saying is the values between these two tables is one-to-one and you've already got the values in Patient then why do you even need the IOM_Tests table? DOS seems like you will likely have multiple dates per patient. But Full_name sounds like it should just be in your patient table. Whenever you need to report the full_name just JOIN to the patient table by patient_id.

But one way to add DOS to your query assuming you can have multiple service dates per patient is:


insert dbo.IOM_Tests(Patient_ID, DOS)
select Patient_ID, DOS
from inserted i
where not exists (select * from dbo.IOM_Tests where Patient_ID = i.Patient_ID and DOS = i.DOS)

Be One with the Optimizer
TG

scholin
Yak Posting Veteran

56 Posts

Posted - 2009-09-04 : 16:38:56

That worked perfectly!

Thank you TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources