December 8, 2007 at 10:22 pm
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
December 9, 2007 at 9:24 am
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
December 9, 2007 at 9:28 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply