December 24, 2008 at 7:20 am
I know I have the inserted table, but how could I reference the old value of a column?
The problem we are having is that we are deleting some records in one table and recreating them with the updated data and we need to be able to correctly find the old records using the value in a date column called StartDtTm.
CREATE TRIGGER [t_ptSchedule_TO_ptAppointments]
ON [dbo].[ptSchedule]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- Declare all the variables
DECLARE @ScheduleId int
DECLARE @ApptTypeID int
DECLARE @ResourceId int
DECLARE @ResourceName varchar(255)
DECLARE @status int
DECLARE @ModifiedByUserID int
DECLARE @WaitList int
DECLARE @ApptBlockID int
DECLARE @EncounterID int
DECLARE @StartDtTm datetime
DECLARE @EndDtTm datetime
DECLARE @CheckedInDtTm datetime
DECLARE @CheckedOutDtTm datetime
DECLARE @ModifiedDtTm datetime
DECLARE @MRN nvarchar(50)
DECLARE @Description nvarchar(2000)
DECLARE @Notes nvarchar(200)
DECLARE @Flow nvarchar(100)
DECLARE @Site nvarchar(500)
DECLARE @ApptType nvarchar(500)
DECLARE @ApptStatus nvarchar(500)
-- Now set the variables
SELECT @ScheduleId = i.ScheduleId FROM INSERTED i
SELECT @ApptTypeID = i.ApptTypeID FROM INSERTED i
SELECT @ResourceId = i.ResourceId FROM INSERTED i
SELECT @ResourceName = DisplayName FROM coApptResources WHERE ResourceId = @ResourceId
SELECT @status = i.Status FROM INSERTED i
SELECT @ModifiedByUserID = i.ModifiedByUserID FROM INSERTED i
SELECT @WaitList = i.WaitList FROM INSERTED i
SELECT @ApptBlockID = i.ApptBlockID FROM INSERTED i
SELECT @EncounterID = i.EncounterID FROM INSERTED i
SELECT @StartDtTm = i.StartDtTm FROM INSERTED i
SELECT @EndDtTm = i.EndDtTm FROM INSERTED i
SELECT @CheckedInDtTm = i.CheckedInDtTm FROM INSERTED i
SELECT @CheckedOutDtTm = i.CheckedOutDtTm FROM INSERTED i
SELECT @ModifiedDtTm = i.ModifiedDtTm FROM INSERTED i
SELECT @MRN = i.MRN FROM INSERTED i
SELECT @Description = i.Description FROM INSERTED i
SELECT @Notes = i.Notes FROM INSERTED i
SELECT @Flow = i.Flow FROM INSERTED i
SELECT @Site = 'North'
SELECT @ApptType = t.DisplayName FROM coApptTypes t WHERE t.ApptTypeID = @ApptTypeID
SELECT @ApptStatus = s.Status FROM coApptStatus s WHERE s.StatusID = @status
-- Only do this if the MRN on the inserted record is not null.
IF @MRN IS NOT NULL
BEGIN
DECLARE @Cnt int
-- Here, we are going to check to see if there is an existing ptAppointments record that
-- matched the newly inserted record.
-- The below statement won't work because the @ScheduleID is the new one that was inserted
-- SELECT @Cnt = COUNT(*) FROM ptAppointments WHERE ID = @ScheduleID
-- The below statement is the one we are using now but cannot determine if it is really going to work
-- correctly in all cases.
SELECT @Cnt = COUNT(*)
FROM ptAppointments
WHERE PID=@MRN
AND [Date]=@StartDtTm
AND Resource=@ResourceName
AND Site=@Site
-- if the record was found, delete it
IF @Cnt > 0
BEGIN
-- Here's where it gets tricky, we know that the statement below doesn't work because
-- the @ScheduleID is the new one.
-- DELETE FROM ptAppointments
-- WHERE ID = @ScheduleID
-- However, if the @ScheduleID is the new one, wouldn't the @StartDtTm, @ResourceName and @Site
-- ALSO be from the newly inserted record?
DELETE FROM ptAppointments
WHERE PID=@MRN
AND [Date]=@StartDtTm
AND Resource=@ResourceName
AND Site=@Site
END
-- Then we insert the new information into ptAppointments
INSERT INTO ptAppointments VALUES (@MRN, @ResourceName, @StartDtTm, @StartDtTm, @ResourceName, @ApptType, @Site, @ApptStatus,
Left(@Description,250), @ModifiedDtTm, @ModifiedByUserID, DATEDIFF(MINUTE,@StartDtTm, @EndDtTm),
CAST(@ScheduleID AS VARCHAR(20)), @StartDtTm, @EndDtTm)
END
END
December 24, 2008 at 7:28 am
I suggest you read this article[/url] as there are some issues with your trigger as is. The biggest being that it will not handle a set-based insert or update.
When using triggers in SQL Server there are 2 virtual tables available, inserted which has the inserted or new updated rows, and deleted which has the old updated or deleted rows. SO if you need to access the deleted or previous rows you use the deleted table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 24, 2008 at 7:28 am
Variables in a trigger are something that should be avoided as much as possible. Your trigger will only work for 1 row. Triggers are fired once per operation, not once per row. If you update multiple rows at once, your trigger breaks. I would suggest re-writing the trigger completely to use set based logic with the inserted and deleted (this is the table you asked about) tables. Perform your inserts or updates as joins to the inserted and deleted tables.
December 24, 2008 at 7:29 am
December 24, 2008 at 7:39 am
Must be based on seniority!:D
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 24, 2008 at 7:42 am
December 24, 2008 at 7:44 am
So, Seth, Jack, how would you rewrite this trigger? I am a rather newbie at them and this one was actually written by someone else that didn't understand them, either. I am only
trying to make it work and would appreciate your expertise in this matter.
December 24, 2008 at 7:58 am
Looking into it.
First few questions:
Is the ScheduleID *changing* on an update to ptSchedule?
What is the primary key on ptSchedule?
December 24, 2008 at 8:20 am
In an insert, the scheduleid is changing, in an update,it is not changing
ScheduleID is the primary key for ptSchedule.
ptAppointments primary key: PID + Date + Resource + Site
Here is ptSchedule->ptAppointments column mapping:
ptAppoinments.PID -> ptSchedule.MRN
ptAppoinments.Location -> (DisplayName FROM coApptResources WHERE ResourceId = ptSchedule.ResourceId)
ptAppoinments.Date -> ptSchedule.StartDtTm
ptAppoinments.Time -> ptSchedule.EndDtTm
ptAppoinments.Resource -> (DisplayName FROM coApptResources WHERE ResourceId = ptSchedule.ResourceId)
ptAppoinments.Type -> ptSchedule.ApptTypeID
ptAppoinments.Site -> 'North'
ptAppoinments.Status -> (s.Status FROM coApptStatus s WHERE s.StatusID = ptSchedule.Status)
ptAppoinments.Notes -> Left(ptSchedule.Description,250)
ptAppoinments.ModifiedDate -> ptSchedule.ModifiedDtTm
ptAppoinments.UserID -> ptSchedule.ModifiedByUserID
ptAppoinments.Duration -> DATEDIFF(MINUTE,ptSchedule.StartDtTm, ptSchedule.EndDtTm)
ptAppoinments.ID -> CAST(ptSchedule.ScheduleID AS VARCHAR(20))
ptAppoinments.StartTime -> ptSchedule.StartDtTm
ptAppoinments.EndTime -> ptSchedule.EndDtTm
December 24, 2008 at 8:42 am
Thanks for the mapping, and well done on anticipating it =)
This is more or less what your trigger should look like. Keep in mind that triggers like this should be tested extensively in a test database before attempting to put them in a Live system. If I screwed up a join anywhere, this could update many more rows than intended.
[font="Courier New"]CREATE TRIGGER [t_ptSchedule_TO_ptAppointments]
ON [dbo].[ptSchedule]
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE ptAppointments -- Won't update anything if the rows are inserted (Join won't match)
SET PID = i.MRN,
Location = R.DisplayName,
[Date] = i.StartDtTm,
[Time] = i.EndDtTm,
Resource = R.DisplayName,
[Type] = i.ApptTypeID,
Site = 'North',
Status = S.Status,
Notes = LEFT(i.[Description],250),
ModifiedDate = i.ModifiedDtTm,
UserID = i.ModifiedByUserID,
Duration = DATEDIFF(mi,i.StartDtTm, i.EndDtTm),
StartTime = i.StartDtTm,
EndTime = i.EndDtTm
FROM ptAppointments A
INNER JOIN inserted i ON A.[ID] = CAST(i.ScheduleID AS VARCHAR(20))
LEFT JOIN coApptResources R ON i.resourceID = R.ResourceID
LEFT JOIN coApptStatus S ON i.Status = S.StatusID
LEFT JOIN coApptTypes T ON i.apptTypeID = T.apptTypeID
-- Inserts new records
INSERT INTO ptAppointments(PID, Location, [Date],[Time], Resource, [Type], Site, Status, Notes,
ModifiedDate, UserID, Duration, StartTime, EndTime)
SELECT i.MRN, R.DisplayName, i.StartDtTm, i.EndDtTm, R.DisplayName, i.ApptTypeID, 'North', S.Status, LEFT(i.[Description],250),
i.ModifiedDtTm, i.ModifiedByUserID, DATEDIFF(mi,i.StartDtTm, i.EndDtTm), i.StartDtTm, i.EndDtTm
FROM inserted i
LEFT JOIN ptAppointments A ON CAST(i.ScheduleID AS VARCHAR(20)) = A.[ID]
LEFT JOIN coApptResources R ON i.resourceID = R.ResourceID
LEFT JOIN coApptStatus S ON i.Status = S.StatusID
LEFT JOIN coApptTypes T ON i.apptTypeID = T.apptTypeID
WHERE A.[ID] IS NULL
END[/font]
December 24, 2008 at 9:12 am
I didn't see where you deleted the old ptAppoinment record(s)
December 24, 2008 at 9:23 am
MrBaseball34 (12/24/2008)
I didn't see where you deleted the old ptAppoinment record(s)
You don't need to delete. I was getting ready to write a similar trigger to Seth's. The first section of the trigger is updating any matching existing ptAppointment rows and then the second section is inserting the new ptAppointment rows.
The only thing I would potentially change in Seth's trigger are the Left Joins/order of the joins. I don't think I'd use any outer joins in the Update as you should always have a valid Resource, Status, and Appointment Type. Then in the Insert section I'd reorder like this to get rid of all but one outer join:
FROM
inserted i JOIN
coApptResources R ON
i.resourceID = R.ResourceID JOIN
coApptStatus S ON
i.Status = S.StatusID JOIN
coApptTypes T ON
i.apptTypeID = T.apptTypeID] LEFT JOIN
ptAppointments A ON
CAST(i.ScheduleID AS VARCHAR(20)) = A.ID
WHERE
A.ID IS NULL
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 24, 2008 at 9:35 am
Yeah, I probably overuse left joins in situations like that. But he does allow NULL's (I still have a copy of that tabe definition from a past problem) in all those fields, and I didn't want the appointment to not be updated if they didn't supply one of them.
December 24, 2008 at 9:40 am
MrBaseball34 (12/24/2008)
I didn't see where you deleted the old ptAppoinment record(s)
To expand upon this (Jack is right that you don't need to delete).
What you were doing in your last trigger is deleting a row, then inserting a row.
Here, you're just updating the row that's already there. The delete was never necessary.
The logic the trigger fires is:
If you insert new rows into the table, new matching rows will be inserted into the Appt table.
If you update rows in the schedule table, the matching rows (which will always be there due to this same trigger) will be updated.
Note that this does not cover rows deleted out of the schedule table... but it never did.
December 24, 2008 at 10:00 am
Thanks guys..
Note that this does not cover rows deleted out of the schedule table... but it never did.
I have another trigger for deleting the ptAppointment records when a ptSchedule record is deleted.
Original
CREATE TRIGGER [t_ptSchedule_TO_ptAppointmentsDELETE]
ON [dbo].[ptSchedule]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ScheduleId int
SELECT @ScheduleId = i.ScheduleId FROM DELETED i
DELETE FROM ptAppointments WHERE ID = CAST(@ScheduleID AS VARCHAR(20))
END
I just modified it to use join with the deleted table, is this the way it should be done?
CREATE TRIGGER [t_ptSchedule_TO_ptAppointmentsDELETE]
ON [dbo].[ptSchedule]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM ptAppointments a
JOIN DELETED d ON a.ID = d.ScheduleID
END
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply