November 23, 2013 at 8:50 pm
I know I should do all the create table statements etc, but I'll chop it down to the bare essentials:
CREATE TABLE Patient (
PatientID INT IDENTITY(1000,1) PRIMARY KEY,
Birthdate DATE,
DeceaseDate DATE);
CREATE TABLE Protocol (
ProtocolNo INT IDENTITY(1,1) PRIMARY KEY
);
CREATE TABLE Enrollment (
enrollmentID INT IDENTITY(10000,1) NOT NULL,
ePatientID INT,
eProtocolNo INT,
enrollDate DATE NOT NULL,
leaveDate DATE,
UNIQUE (ePatientID, eProtocolNo),
PRIMARY KEY enrollmentID,
FOREIGN KEY ePatientID REFERENCES Patient(patientID),
eProtocolNo REFERENCES Protocol(ProtocolNo);
CREATE TABLE Symptom (
sEnrollmentID INT,
sToxicityID INT,
Cycle TINYINT,
Grade TINYINT CHECK IN (1,2,3,4,5),
The basic rule that I'm enforcing is that if a Patient has a Symptom of Grade 5, he is (1) removed from the Protocol (leaveDate is updated), and (2) His DeceaseDate is updated.
I got the "one hop" (to enrollment) working, but I can't figure out how to get the Patient record to update too.
ALTER TRIGGER trgFatalToxicity ON Symptom
AFTER INSERT
AS
DECLARE @enrollmentID int
DECLARE @patientID int
SET NOCOUNT ON;
IF EXISTS (SELECT *
FROM inserted
WHERE inserted.Grade=5)
BEGIN
SELECT @enrollmentID = MAX(inserted.sEnrollmentID)
FROM inserted;
PRINT 'Patient died!, Grade 5 inserted!'
DECLARE @dod DATE;
SET @dod = GETDATE();
-- this works
UPDATE enrollment
SET leaveDate = @dod
WHERE enrollmentID = @enrollmentID;
PRINT 'enrollment table updated';
-- this fails, I guess
-- error:Msg 4104, Level 16, State 1, Procedure trgFatalToxicity, Line 29
--The multi-part identifier "enrollment.patientID" could not be bound.
UPDATE patient
SET dod = @dod
WHERE patient.patientID = enrollment.patientID
and enrollment.enrollmentID = @enrollmentID;
PRINT 'patient table updated.'
END
Error:
Msg 4104, Level 16, State 1, Procedure trgFatalToxicity, Line 29
The multi-part identifier "enrollment.patientID" could not be bound.
Two things: what does that error mean in non-geek English? and how do I fix it? I suspect it is caused by the fact that the inserted virtual table can contain more than one record. The enrollmentID will always be the same for all the records in the insertedtable, so that might be what's doing it...
The weird thing is that I can update LeaveDate without a problem...
Is it that I'm trying to use a scalar value instead of joining to a table that's causing the error? (It's the only thing I can think of right now!) -- like I'm trying to do a join to a constant @SomeValue instead of WHERE tblA.column1=@SomeValue.
Thanks!
Pieter
November 24, 2013 at 3:12 am
ALTER TRIGGER trgFatalToxicity ON Symptom
AFTER INSERT
SET NOCOUNT ON;
IF EXISTS (SELECT *
FROM inserted
WHERE inserted.Grade=5)
BEGIN
PRINT 'Patient died!, Grade 5 inserted!'
UPDATE e
SET leaveDate = GETDATE()
FROM INSERTED JOIN enrollment AS e
ON inserted.sEnrollmentID = e.EnromlmentID
WHERE INSERTED.Grade = 5;
PRINT 'enrollment table updated';
UPDATE p
SET DeceaseDate = GETDATE()
FROM patient AS p
JOIN enrollment ON p.patientID = enrollment.ePatientID
JOIN INSERTED
ON inserted.sEnrollmentID = e.EnrollmrntID
WHERE INSERTED.Grade = 5;
PRINT 'patient table updated.'
END
I modified this to set based and added missing table join to patient table, which was the reason for error.
Corrected column names
November 24, 2013 at 4:58 am
The problem with the original join, according to the table definitions given, is that the field in the enrollment table is called epatientid, not patientid. So naturally the name enrollment.patientid couldn't be resolved.
One of the sillier conventions sometimes adopted is to change a column name according to the table it's in; this allows omitting to write table or alias names to qualify column names in select lsts and on and where clauses, but it also means that if the same attribute is in more than one table (as in this case, the attribute concerned being the identification of a the patient) that attribute has multiple different names in the system, which can cause all sorts of problems, just as it caused a problem in this case.
There was a problem with the logic of the trigger too; it was written assuming that any update of the symptoms table updates symptoms for only one enrollment. That may be true today, but there's probably a better than even chance that someone will devise a way of pulling in multiple updates from a staging table created elsewhere during an unplanned period of unavaliability of the main database, and that could result in living patients being marked dead and dead patients apparently remaining alive. So it would be better to eliminate the use of local variables and do the job in DML instead, so that it will work correctly with mutiple patients and that problem can never arise. One way of doing that has already been suggested by Ville-Pekka Vahteala, but it won't work because (a) it falls into the same trap with a different column and prefix - the column in enrollment id is not called sEnrollmentid, the enrollmentid is only called that when it's in the symptom table - and (b) it doesn't remove the original error anyway (or if it does, it removes all reference to the target table in the vonditions for updating Patient, so it fails to specify what rows to update - it's not clear whether the intention was to replace the original join or keep it and add another one.
It could also be changed to standard SQL from T-SQL using a non-standard feature introduced by Microsoft (or maybe by Sybase, I can't remember), but that would only be necessary if there's a chance of its being ported to a different RDBMS like Ingres or DB2 or..., so probably not worth thinking about except that neither the original code nor the correction offered will so far will actually work. So here goes:
[code"sql"]ALTER TRIGGER trgFatalToxicity ON Symptom
AFTER INSERT AS
SET NOCOUNT ON;
UPDATE Enrollment
SET leaveDate = GETDATE()
WHERE EXISTS (
SELECT 1 FROM inserted i
WHERE Enrollment.enrollmentID = i.sEnrollmentID
AND Grade = 5 );
If @@rowcount > 0
BEGIN
PRINT 'Patient died!, Grade 5 inserted!';
PRINT 'Enrollment table updated';
UPDATE Patient
SET DeceaseDate = GETDATE()
WHERE EXISTS (
SELECT 1 FROM enrollment e
WHERE Patient.patientID = e.ePatientID
AND e.enrollmentID = i.sEnrollmentID
AND i.Grade = 5);
PRINT 'patient table updated.';
END
[/code]
It might be sensible to use MERGE statements instead of UPDATE statements, but as the original used update I thought it better to continue to use it and not introduce something that might be unfamiliar to the original poster.
I've changed the column name dod to what appears in the table definition, and inserted the AS marker to separate the declaration header for the trigger from the body of the trigger.
Tom
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply