August 16, 2010 at 9:22 am
I'm writing an update trigger on table "Appointment" and using the Inserted table to update another table, "AppointmentStatusTransactions".
The first time the trigger table, Appointment, is updated the trigger fires and a row is inserted into the AppointmentStatusTransaction table with the correct data taken from the INSERTED table.
Subsequent UPDATES to the Appointment table result in the AppointmentTransactionStatus table being inserted with the same data as the first INSERT even though the data in the Appointment table was modified. I've verified that the data in the Appointment table is being modifed but the modified data is not being INSERTED into the AppontmentStatusTransactions table.
Any idea what I'm doing wrong?
Code is below.
Thanks for any assistance.
Code starts:
USE [PRM_2009_P2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =========================================
ALTER TRIGGER [dbo].[ApptTranTrig5]
ON [dbo].[Appointment]
AFTER UPDATE
AS
IF UPDATE(AppointmentStatusUID)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO dbo.AppointmentStatusTransactions (AppointmentUID,AppointmentStatusUID,PatientUID,ServiceSiteUID,StatusStartTime,StatusModifiedBy)
SELECT i.AppointmentUID,i.AppointmentStatusUID,i.PatientUid,i.ServiceSiteUID,i.LastModifiedDate,i.LastModifiedByUID
FROM INSERTED AS I;
END
August 16, 2010 at 9:27 am
the UPDATE() function is so misleading.
the UPDATE() tests to see if the column name was specifically included/named in the update or not...it does not test if a value changed.
what you want to do is join the INSERTED and DELETED tables together in the update trigger, and test the field specifically:
ALTER TRIGGER [dbo].[ApptTranTrig5]
ON [dbo].[Appointment]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO dbo.AppointmentStatusTransactions (AppointmentUID,AppointmentStatusUID,PatientUID,ServiceSiteUID,StatusStartTime,StatusModifiedBy)
--inserting ONLY the items that had the AppointmentStatusUID CHANGE
SELECT i.AppointmentUID,i.AppointmentStatusUID,i.PatientUid,i.ServiceSiteUID,i.LastModifiedDate,i.LastModifiedByUID
FROM INSERTED AS I
INNER JOIN DELETED AS D ON I.AppointmentUID = D.AppointmentUID
WHERE I.AppointmentStatusUID <> D.AppointmentStatusUID
Lowell
August 16, 2010 at 9:34 am
Lowell:
Thanks for the quick reply.
The data is changing, I've looked at the Appointment table and see that the changes are made, but not reflected in either the INSERTED or DELETED tables.
Do the INSERTED and DELETED tables contain multiple rows of data? Is it possible that I'm inserting the last row of data from these tables and I want the first row? If so, how do I grab the row I need?
Thanks for any help you can provide.
Mike
August 16, 2010 at 9:50 am
well, the way i read your trigger, it should be working just fine.
if you run this commnad, do you see changes in the status?
SELECT i.AppointmentUID,i.AppointmentStatusUID,StatusStartTime
FROM AppointmentStatusTransactions
GROUP BY i.AppointmentUID,i.AppointmentStatusUID,StatusStartTime
ORDER BY i.AppointmentUID,i.StatusStartTime
you might need to post the CREATE TABLE definitions of both tables,and maybe some sample data to see if we can duplicate the error of data not being inserted...that is the whole trigger, right? you didn't leave anything out? an error in the trigger could cause no data to be inserted.
Lowell
August 16, 2010 at 11:01 am
Lowell:
Tried your suggestion of GROUP & ORDER, still no changes.
Here are the tables (I used SS Mgmt Studio to create:
CREATE TABLE dbo.AppointmentStatusTransactions (
AppoiintmentUID UNIQUEIDENTIFIER PRIMARY KEY,
AppointmentStatusUID UNIQUEIDENTIFIER,
PatientID UNIQUEIDENTIFER,
ServiceSiteUID UNIQUEIDENTIFIER,
StatusStartTime DATETIME,
StatusModifiedBy UNIQUEIDENTIFIER)
CREATE TABLE dbo.Appointment (
AppoiintmentUID UNIQUEIDENTIFIER PRIMARY KEY,
AppointmentStatusUID UNIQUEIDENTIFIER,
PatientID UNIQUEIDENTIFER,
ServiceSiteUID UNIQUEIDENTIFIER,
LastModifiedTime DATETIME,
LastModifiedBy UNIQUEIDENTIFIER)
]
The trigger looks for an UPDATE to Appointment and then INSERTS row into AppointmentStatusTransaction. As you can see the columns have the same name except for appointment.LastmodifiedTime = AppointmentStatusTransactions.StatusStartTime and Appointment.LastModifiedBy = AppointmentStatusTransactions.StatusModifiedBy.
Trigger Code:
USE [PRM_2009_P2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =========================================
ALTER TRIGGER [dbo].[ApptTranTrig5]
ON [dbo].[Appointment]
AFTER UPDATE
AS
IF UPDATE(AppointmentStatusUID)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO dbo.AppointmentStatusTransactions (AppointmentUID,AppointmentStatusUID,PatientUID,ServiceSiteUID,StatusStartTime,StatusModifiedBy)
SELECT i.AppointmentUID,i.AppointmentStatusUID,i.PatientUid,i.ServiceSiteUID,i.LastModifiedDate,i.LastModifiedByUID
FROM INSERTED AS I;
END
August 16, 2010 at 11:30 am
ok i'm pretty sure you are pasting untested code, because the tables you pasted do not have the same column names as the trigger;
i'm not surprised nothing seems to work...the code doesn't pass a syntax check.
i have no idea why you are using uniqueidentifiers for data types; they will make your application slower and you cannot index them. they should be integers or big integers.
here's a simple proof of concept that shows that if your code is cleaned up, it works. the trigger inserts to the audit table IF an update occurs.
that means the problem is probably related to the uniqueidentifiers...does your application KNOW which AppoiintmentUID as the UNIQUEIDENTIFIER it is looking at so it can update it? maybe it's just inserting new ones over and over instead of updating?
USE tempdb
/*
--results
0CB683BB-DBF6-466C-BCA4-3A7EA2246F44C1E47E75-2630-4A9D-8F3A-9F001CDA4C530CB683BB-DBF6-466C-BCA4-3A7EA2246F440CB683BB-DBF6-466C-BCA4-3A7EA2246F442010-08-16 13:26:53.2700CB683BB-DBF6-466C-BCA4-3A7EA2246F44
*/
CREATE TABLE dbo.AppointmentStatusTransactions (
AppoiintmentUID UNIQUEIDENTIFIER PRIMARY KEY,
AppointmentStatusUID UNIQUEIDENTIFIER,
PatientID UNIQUEIDENTIFIER,
ServiceSiteUID UNIQUEIDENTIFIER,
StatusStartTime DATETIME,
StatusModifiedBy UNIQUEIDENTIFIER)
CREATE TABLE dbo.Appointment (
AppoiintmentUID UNIQUEIDENTIFIER PRIMARY KEY,
AppointmentStatusUID UNIQUEIDENTIFIER,
PatientID UNIQUEIDENTIFIER,
ServiceSiteUID UNIQUEIDENTIFIER,
LastModifiedTime DATETIME,
LastModifiedBy UNIQUEIDENTIFIER)
GO
-- =========================================
CREATE TRIGGER [dbo].[ApptTranTrig5]
ON [dbo].[Appointment]
AFTER UPDATE
AS
IF UPDATE(AppointmentStatusUID)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO dbo.AppointmentStatusTransactions (AppoiintmentUID,AppointmentStatusUID,PatientID,ServiceSiteUID,StatusStartTime,StatusModifiedBy)
SELECT i.AppoiintmentUID,i.AppointmentStatusUID,i.PatientID,i.ServiceSiteUID,i.LastModifiedTime,i.LastModifiedBy
FROM INSERTED AS I;
END
GO
declare @myNewID uniqueidentifier,
@apptID uniqueidentifier
SELECT @myNewID=NEWID()
select @apptID=NEWID()
--create one record
INSERT INTO Appointment(AppoiintmentUID,AppointmentStatusUID,PatientID,ServiceSiteUID,LastModifiedTime,LastModifiedBy)
SELECT @myNewID,@myNewID,@myNewID,@myNewID,GETDATE(),@myNewID
--try to update it
UPDATE Appointment
SET AppointmentStatusUID = @apptID
WHERE AppoiintmentUID = @myNewID
--confirm a record exists in the audit table
select * from dbo.AppointmentStatusTransactions
Lowell
August 16, 2010 at 12:20 pm
Lowell:
Once again Thanks.
Couple of clarifications:
1) I'm writing some adjunct code to an application that exists. That's the reason for UNIQUEIDENTIFIERS (I'm stuck with them).
2) The Appointment table is created and maintained by the application. I don't touch this table. The trigger is the only interaction I have with the table.
3) I created the AppointmentStatusTransactions table (through SS Mgmt Studio) and matched up the columns attributes to the Appointment table The trigger is the only object that intereracts with the AppointmentTransactionStatus table.
4) The first time the trigger fires for an Appointment.AppointmentUID UPDATE it INSERTS the information perfectly into the AppointmentStatusTransactions table. Subsequent UPDATES for the AppointmentUID results in a row being INSERTED into AppointmentStatusTransactions but with the same data as the first INSERT.
I'm flummoxed.
Thanks
Mike
August 16, 2010 at 12:44 pm
Lowell:
You're probably ready to shoot me, but one last thing.
I deleted all the rows from the AppointmentTransactionTable and used the application to update the Appointment table.
The trigger worked perfectly.
Could my SELECT statement be selecting off the AppointmentTransactionTable instead of the INSERTED table?
INSERT dbo.AppointmentStatusTransactions (AppointmentUID,AppointmentStatusUID,PatientUID,ServiceSiteUID,StatusStartTime,StatusModifiedBy)
SELECT i.AppointmentUID,i.AppointmentStatusUID,i.PatientUid,i.ServiceSiteUID,i.LastModifiedDate,i.LastModifiedByUID
FROM INSERTED AS I
I don't see how, but that's the only thing I can think of.
Mike
August 16, 2010 at 12:55 pm
Lowell:
I changed the names of the colums in the AppointmentStatusTransactions table so they aren't identical to the column names in the Appointment table and voila, it worked.
Not sure if SQL was at fault or MS Access that I was using to view the table data.
Either way, I owe you many thanks as your last post mentioned that maybe the application didn't know which AppointmentUID..... It wasn't the application, but that gave my the idea to change the column names.
Thanks again.
Mike
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply