April 4, 2005 at 8:20 am
Hi.
I've noticed through several other posts that anybody mentioning using nested scripts is strung up by their vital organs.
As such i'm looking for suggestions to a problem i'm trying to solve that doesn't involve cursors.
I want to write a trigger that checks for changes on a table. The SP that makes updates to the table is a very simple affair that will updates table if a simple compare shows that any data has changed.
ie.
compare table(a) to table (b)
if table(a) has different data update table(b)
So the trigger on table(b) needs to run through each record in the Inserted recordset, check whether the field in inserted is different to field in table(b) and then crosscheck the changed field against a predefined hashtable (columnname, bit flag) to check whether we need to notify that the particular field has changed.
If you can follow what i'm trying to achieve do you have any suggestions? I think i've been sat at my screen too long to see what is probably a really simple solution.
April 4, 2005 at 9:47 am
I think I followed it. As a baseline, check out BOL. Go to the Index tab, look up "triggers" and then the "multirow" subcategory below that. Given that, I think you want to use a LEFT JOIN from the "inserted" resultset to table(b) using whatever criteria you use. This all makes more since as an update trigger instead of an insert trigger to me, but I don't know what data you are managing and you were talking insert. Assuming you correctly joined looking for matching fields you can then use a where clause to filter out the non NULL joined rows since they were not changed. For the crosscheck, you can LEFT JOIN that as well and WHERE clause out the non-notify rows. What you do with that result depends on what "notify" means.
This is all very vague and a lot of guess work. Perhaps someone else understands your situation better than I, but at least for me to do any better I'd have to see some DDL to know what table B looks like. It would also help if you clarified what constituted a "change". In principle, a conditional "change" can only occur during an UPDATE since INSERT and DELETE by definition modify (create or destroy) the table row. However, if this is tracking history as in an audit table or something like it, I could see how your "change" might be the current value being inserted compared to the previous most current version based on sequence number or timestamp added to the key.
April 4, 2005 at 4:15 pm
>>we need to notify that the particular field has changed.
Kinda vague. What form does 'notification' take ?
Assuming each record has a primary key column:
Insert Into Notifications (PKeyColumn, ColumnName)
Select i.PKeyColumn, 'Column1'
From inserted As i
Inner Join TableB As b
On (i.PKeyColumn = b.PKeyColumn)
Where i.Column1 <> b.Column1
And Exists (
Select *
From Hastable As h
Where h.ColumnName = 'Column1'
And h.NotifyChanges = 1
)
-- Repeat for other columns
April 5, 2005 at 1:50 am
Perhaps this is oversimplistic - but I like simple - and it avoids triggers which I don't like for performance and because, as a developer, it makes debugging harder / less obvious what's going on :
CREATE TABLE #Changes (KeyField , TargetField )
BEGIN TRANSACTION
INSERT INTO #Changes
SELECT a.KeyField, b.TargetField
FROM TableA a
JOIN TableB b
ON b.KeyField = a.KeyField
WHERE a.MatchField b.MatchField
UPDATE TableA
SET TargetField = c.TargetField
FROM #Changes c
JOIN TableA a
ON a.KeyField = c.KeyField
IF @@ERROR 0
BEGIN
ROLLBACK TRANSACTION
GOTO ExceptionHandler
END
IF @@ERROR 0
BEGIN
ROLLBACK TRANSACTION
GOTO ExceptionHandler
END
COMMIT TRANSACTION
DROP TABLE #Changes
April 5, 2005 at 2:29 am
Hi
Just to explain myself a bit further all the tables take this form
CREATE TABLE [dbo].[MED_Progress] (
[ID_rec] [int],
[Entityref] [varchar] (15),
[Matterno] [int],
[Medical_ref] [varchar] (25),
[Progress_notes] [varchar] (512),
[Status] [int],
[Time_stamp] [datetime],
[sr_Confirm] [int],
[InstructionID] [int],
[Agency_Confirm] [bit]
)
CREATE TABLE [dbo].[incPRE_Med_Progress] (
[ID_rec] [int],
[Entityref] [varchar] (15),
[Matterno] [int],
[Medical_ref] [varchar] (25),
[Progress_notes] [varchar] (512),
[Satus] [int],
[Time_stamp] [datetime],
[sr_Confirm] [int],
[InstructionID] [int] ,
[Agency_Confirm] [bit],
[ChangeCount] [int],
[RecordDate] [datetime],
[ArchiveDate] [datetime]
)
CREATE TABLE [dbo].[incPRE_Med_Progress_Hash] (
[ColumnName] [varchar] (50) NULL ,
[CheckChange] [bit] NULL
)
med_progress is a simple container to capture data from a view in a client system.
Incpre_med_progress is our internal table used to store the latest data about any particular record.
Whenever there is a difference between med_progress and incepre_med_progress, incpre_med_progress is updated with the latest data (old data archived to a table not shown), and if specified fields (defined within incpre_med_progress_hash) are changed then the fieldnames require capturing. When you have a complete list of changed fields for a record, the list is passed as a string to a 3rd db system via a stored procedure.
April 5, 2005 at 9:03 am
I am going to assume that ID_rec is the key for the MED_Progress table and that "ID_rec, RecordDate" is the key for the incPRE_Med_Progress table. If the link between the tables is different (such as ID_rec maintained independantly) then you will just need to adjust the query to account for it. This code could be run as a part of an UPDATE trigger on MED_Progress, or done explicitely from the SPs updating MED_Progress. If done as an update trigger, you would need a cursor to loop through each MED_Progress row being updated if you wish to support multi-row updates with this process.
declare @NumDifferent smallint
declare @FieldsDifferent varchar(1000)
set @NumDifferent = 0
set @FieldsDifferent = ''
SELECT @NumDifferent = @NumDifferent + case when Core.Entityref = inc.Entityref then 0 else 1 end
, @NumDifferent = @NumDifferent + case when Core.Matterno = inc.Matterno then 0 else 1 end
, @NumDifferent = @NumDifferent + case when Core.Medical_ref = inc.Medical_ref then 0 else 1 end
, @NumDifferent = @NumDifferent + case when Core.Progress_notes = inc.Progress_notes then 0 else 1 end
, @NumDifferent = @NumDifferent + case when Core.Status = inc.Status then 0 else 1 end
, @NumDifferent = @NumDifferent + case when Core.Time_stamp = inc.Time_stamp then 0 else 1 end
, @NumDifferent = @NumDifferent + case when Core.sr_Confirm = inc.sr_Confirm then 0 else 1 end
, @NumDifferent = @NumDifferent + case when Core.InstructionID = inc.InstructionID then 0 else 1 end
, @NumDifferent = @NumDifferent + case when Core.Agency_Confirm = inc.Agency_Confirm then 0 else 1 end
, @FieldsDifferent = @FieldsDifferent + case when Core.Entityref = inc.Entityref then ''
when ISNULL( ( SELECT CheckChange FROM incPRE_Med_Progress_Hash WHERE ColumnName = 'Entityref' ), 0 ) = 1 then ',Entityref'
else '' end
, @FieldsDifferent = @FieldsDifferent + case when Core.Matterno = inc.Matterno then ''
when ISNULL( ( SELECT CheckChange FROM incPRE_Med_Progress_Hash WHERE ColumnName = 'Matterno' ), 0 ) = 1 then ',Matterno'
else '' end
, @FieldsDifferent = @FieldsDifferent + case when Core.Medical_ref = inc.Medical_ref then ''
when ISNULL( ( SELECT CheckChange FROM incPRE_Med_Progress_Hash WHERE ColumnName = 'Medical_ref' ), 0 ) = 1 then ',Medical_ref'
else '' end
, @FieldsDifferent = @FieldsDifferent + case when Core.Progress_notes = inc.Progress_notes then ''
when ISNULL( ( SELECT CheckChange FROM incPRE_Med_Progress_Hash WHERE ColumnName = 'Progress_notes' ), 0 ) = 1 then ',Progress_notes'
else '' end
, @FieldsDifferent = @FieldsDifferent + case when Core.Status = inc.Status then ''
when ISNULL( ( SELECT CheckChange FROM incPRE_Med_Progress_Hash WHERE ColumnName = 'Status' ), 0 ) = 1 then ',Status'
else '' end
, @FieldsDifferent = @FieldsDifferent + case when Core.Time_stamp = inc.Time_stamp then ''
when ISNULL( ( SELECT CheckChange FROM incPRE_Med_Progress_Hash WHERE ColumnName = 'Time_stamp' ), 0 ) = 1 then ',Time_stamp'
else '' end
, @FieldsDifferent = @FieldsDifferent + case when Core.sr_Confirm = inc.sr_Confirm then ''
when ISNULL( ( SELECT CheckChange FROM incPRE_Med_Progress_Hash WHERE ColumnName = 'sr_Confirm' ), 0 ) = 1 then ',sr_Confirm'
else '' end
, @FieldsDifferent = @FieldsDifferent + case when Core.InstructionID = inc.InstructionID then ''
when ISNULL( ( SELECT CheckChange FROM incPRE_Med_Progress_Hash WHERE ColumnName = 'InstructionID' ), 0 ) = 1 then ',InstructionID'
else '' end
, @FieldsDifferent = @FieldsDifferent + case when Core.Agency_Confirm = inc.Agency_Confirm then ''
when ISNULL( ( SELECT CheckChange FROM incPRE_Med_Progress_Hash WHERE ColumnName = 'Agency_Confirm' ), 0 ) = 1 then ',Agency_Confirm'
else '' end
FROM MED_Progress Core
LEFT JOIN incPRE_Med_Progress inc
ON inc.ID_rec = Core.ID_rec
AND inc.RecordDate = ( SELECT MAX( MAXinc.RecordDate ) FROM incPRE_Med_Progress MAXinc WHERE MAXinc.ID_rec = inc.ID_rec )
WHERE Core.ID_rec = @TheRecInQuestion
if @NumDifferent > 0
begin
INSERT INTO incPRE_Med_Progress
( the stuff )
exec ReportChanges @REC_id = @TheRecInQuestion, @FieldList = @FieldsDifferent
end
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply