Problem with trigger is SQL Server Express

Question

Problem with trigger is SQL Server Express

dJeePe

Say Hey Kid

Points: 712
More actions
December 8, 2007 at 10:22 pm

#184741

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

The problem with such a trigger is that you've created an "After" trigger... these types of triggers fire "After" the data has already been inserted. In other words, the data has already been inserted into the table and will always cause a dupe according to your trigger. If you and your instructor insist on making the error of trying to do dupe checks using triggers instead of a properly designed table having a primary key to reject such duplicates, then you will need to create an "Instead of Trigger", instead (no pun intended).

The best thing to do would be to test for duplication from the app before you do an insert and also have the correct primary keys on the table to reject an insert that would cause a "duplicate".

I trully hope your instructor isn't teaching "Insert by exception"... Rollbacks are one of the most expensive things that can be done in any RDBMS.

Also, since you're a "student of SQL", here's a couple of other things...

There is no need for the UPPER to compare data... unless someone made the horrible mistake of making the database case sensitive.

A table name should indicate what is on a single row of the table... since only one patient will live on a row, the table should be named "Patient"... not "Patients".

You will also learn to hate underscores... they greatly interfere with speed typing... although you would think the contrary, I use "mixed case" on table names, column names, and variable names. For example, instead of using patient_name, I use PatientName... putting the left pinky on the shift key is a hell of a lot easier than "breaking stide" to hit shift/underscore... at least for me. And, the readability is just fine. But your choice.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

More info...

You CAN do a dupe check using an after trigger... Again, ITS THE WRONG THING TO DO!!!! But it will help you "pass" if your instructor insists on having you write incorrect code...

CREATE TRIGGER trg_Insert_Patients

ON Patients

FOR INSERT

AS

DECLARE @PatientName VARCHAR(75)

SELECT @PatientName = (SELECT Patient_Name FROM INSERTED)

IF (SELECT COUNT(*) FROM Patients WHERE Patient_Name = @PatientName) > 1

BEGIN

ROLLBACK TRANSACTION

PRINT 'Patient ' + @PatientName + ' already exists.'

END

GO

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)