March 6, 2013 at 3:22 pm
That would be ideal. The problem is there are many places that update or insert into the patient table. And no one can tell me all the places. They may not all be in stored procs. So If I don't have the trigger it could be missed somewhere and cause other issues.
March 6, 2013 at 3:28 pm
Just had this same question on another thread with a trigger and doing a large insert...almost verbatim from the other thread:
One thing I can say is that if you were to alter your trigger to skip the update and you supplied the correct DOB in your INSERT statement you would save yourself a ton of I/O. You would need to do some impact analysis however to ensure a change like this would not compromise your data should other inserts or updates supply invalid values for that column thereby circumventing the usefulness of the trigger. If that were a concern there are other things you could do with CONTEXT_INFO to skip the work in the trigger for only your batch process.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 6, 2013 at 3:34 pm
How do you determine the Context_info? I don't understand how I could use that to avoid the trigger?
March 6, 2013 at 3:55 pm
You would modify your proc to set CONTEXT_INFO before you did the UPDATE.
SET CONTEXT_INFO 0x1256698456
Then in your trigger you would check CONTEXT_INFO and if you found it had the same value you set in your proc you would skip the UPDATE:
SET @var = CONTEXT_INFO();
IF @var != 0x1256698456
BEGIN
-- do update as usual
END
http://msdn.microsoft.com/en-us/library/ms180125(v=sql.90).aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 6, 2013 at 4:09 pm
Thats worth a shot. I'll do some testing. Thanks
March 6, 2013 at 4:56 pm
Why not combine not updating the time in DOB with this?
IF UPDATE(DOB)
BEGIN
UPDATE P
SET DOB = dbo.fn_ReplaceTimeInDate(i.DOB)
FROM dbo.Patient P INNER JOIN
inserted i ON
P.PatientID = i.PatientID
where i.DOB <> cast(convert(varchar,i.DOB,112) as datetime)
SET @error_var = @@ERROR
--Error checking
IF @error_var > 0
GOTO rollback_tran
END
March 6, 2013 at 5:09 pm
Thanks!! That may be the best way to fix it. Or a variation at least.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply