Problem with trigger is SQL Server Express

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

Problem with trigger is SQL Server Express

Author

Topic

bxw689
Starting Member

4 Posts

Posted - 2007-12-09 : 00:52:59

I am creating a doctor's office database for a class project and I need to create a trigger to prevent duplicate entries in the patient table. I am using the following code to successfully create the trigger. However, every insert on the patient table is rolled back, and the message prints, even if it is not a duplicate. Please help! Thank you.

CREATE TRIGGER trg_insert_patient
on patients
for insert
as
begin

declare @patient_name varchar(75)

select @patient_name = (select patient_name from inserted)

if exists (select 1 from patients where upper(patient_name) = upper(@patient_name))

begin

rollback transaction
print 'Patient already exists.'

end

end

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-09 : 01:15:45

you should enforce this with a unique constraint, not a trigger.

elsasoft.org

bxw689
Starting Member

4 Posts

Posted - 2007-12-09 : 01:30:33

Thanks jezemine,

That would work, but I originally tried to add a trigger that would concatenate the patient_name and date_of_birth columns (see code below). But that didn't work, so I trimmed to trigger down some to see if it would work and it still didn't. Technically, you can have 2 patients with the same, but it is unlikely that they have the same DOB. The trigger is created successfully, but it rolls back all inserts.

/* THIS TRIGGERS PERFORMS VALIDATION UPON THE INSERT OF A NEW PATIENT*/

CREATE TRIGGER trg_insert_patient
on patients
for insert
as
begin

declare @patient_name varchar(75), @date_of_birth datetime

select @patient_name = (select patient_name from inserted)

select @date_of_birth = (select date_of_birth from inserted)

/* VALIDATES THAT THE INSERTED PATIENT IS NOT A DUPLICATE*/

if exists(select 1 from patients
where upper(patient_name) = upper(@patient_name) and date_of_birth = @date_of_birth)

begin
rollback transaction
print 'Patient already exists'

end

end

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-09 : 02:51:22

in that case, you should have a composite unique constraint on patient_name,date_of_birth.

trigger is the wrong approach here. triggers should not be used to do an constraint's job.

even if it was the right approach, you must write triggers to handle inserts of multiple rows. you are not doing that here. you are writing the trigger as if only one row can be inserted at a time, which is an incorrect assumption.

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-09 : 02:53:46

is this a toy database, or a real one?

I ask because I can certainly imagine it's possible to have two people named the same and born on the same day. just because it's unlikely doesn't mean it doesn't happen.

elsasoft.org

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-09 : 17:37:56

For home work.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-09 : 23:16:53

good.

i'd hate to be left on the sidewalk bleeding to death by the EMTs just because I had the same name and birthday as someone else...

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources