Triggers and multiple row updates
-
I've got an update trigger, but it only seems to be 'triggered' once for an update statement that updates several rows of a given tab.
Anyone know a trigger based solution to this?
-
This is normal behaviour for a trigger.
Inside the trigger, you have a 'inserted' and 'deleted' table. These tables contain the complete set that was changed during the update ('deleted' contains the old values and 'inserted' the new values).
The solution for this is to make sure all actions you do in the trigger are done on all of the records in the inserted/deleted table.
If you post your code, I'm sure we can help you find a solution.
-
It's along the lines of
if update(refno)
begin
select @before=convert(varchar(255),refno) from deleted
select @after=convert(varchar(255),refno) from inserted
exec WriteToCal @User,refno,@Before,@After,@Refno,@Surname, @Dob
end
This code is then repeated for several different fields. I obviously need to loop around the deleted table. What's the best way?
Cheers
Simon
-
Your best bet would be to use a cursor. I don't like using cursors, but I don't know of any way to check the Update() values without scrolling through all of the records, one at a time. I'm assuming you DON'T want to run your script if those fields weren't updated.
Pseudo code.
declare
fetch next tCursor
While @@FETCH_STATUS = 0
BEGIN
IF UPDATE(refno)
BEGIN
begin
select @before=convert(varchar(255),refno) from deleted
select @after=convert(varchar(255),refno) from inserted
exec WriteToCal @User,refno,@Before,@After,@Refno,@Surname, @Dob
END
END
-
I think in production environment you'll kill your database.
Secondly with multiple tables and a rollback in one all previous captures need to be undone. -
Bobster,
UPDATE() doesn't need to be tested for each row, because it is true for the entire set. ie. the single- or multi-row update is fired by something like:
UPDATE x SET y = 'blah' .... WHERE...so UPDATE(y) is true for the entire set.
Also, I realise it's only a bit of guidance trigger code, but don't forget to stick another fetch within the loop.
Cheers,
- Mark
Cheers,
- Mark -
hi!
if you're using triggers and cursors, make sure you implement proper error handling (deallocate your cursor on every possible failure) or declare your cursor "local". otherwise an error that exits the trigger without deallocating your cursor will lead to "cursor already exists" error on next call.
in general it's always better to make your modifications by using the "inserted/deleted" pseudo tables for joins (or at least to provide keys) and thus avoiding cursors, eg.
update your_table set modified_date = getutcdate() where id in (select id from inserted)
or something like that.
best regards,
chris.
-
Hi Mark,
I don't mean to start an argument, but the UPDATE() function is field specific, so you WOULD have to test for each row because you might have updated multiple and different fields which would fire the trigger. If I'm mistaken about this, please correct me. Also, the previous posts about deallocating cursors is one of the reasons I hate triggers. If you forget to deallocate, which I seem to ALWAYS do, you leave a cursor hung in memory. Does anyone know how to easily get rid of a stuck cursor? I just write another script that deallocates the cursor once it's become stuck. Also, what kind of error handling can you do to close / deallocate cursors?
-
How about not using cursors at all? There are many cheaper ways to loop in SQL. Below is one example; it creates a csv string out of data in a column (test data script at bottom).
declare @ID int,
@Data varchar(800)
set@ID = 0
While 1=1
begin
--Select next table to be created
Select top 1 @ID = [ID],
@Data = isnull(@Data + ',', '') + Data
From#Delete
Where[ID] > @ID
order by [ID]
If @@Rowcount = 0 Break
END
Select @Data
/*-----------------------------------------
Create Test Data
*/----------------------------------------
If object_ID('tempdb..#Delete') is not null drop table #Delete
Create Table #Delete ([ID] int identity, Data varchar(100))
Insert #Delete Values ('Data1')
Insert #Delete Values ('Data2')
Insert #Delete Values ('Data3')
Insert #Delete Values ('Data4')
Insert #Delete Values ('Data5')
snootchie bootchies
Signature is NULL
-
You can select the updated rows without a cursor:
SELECT PrimaryKey, refno FROM inserted WHERE UPDATE(refno)
or
SELECT PrimaryKey, d.refno as Before, i.refno as After
FROM inserted i
INNER JOIN deleted d on i.PrimaryKey = d.PrimaryKey
WHERE UPDATE(refno)
This will only select rows where the refno field was updated, although it may have been updated to the same value it had originally.
Can you use a simple INSERT/SELECT in the trigger to write the log entries instead of calling WriteToCal?
-
Are you trying to log the beofore and after changes? Maybe eomehting like this?
Insert
LogTable
(
refnoBefore
, refnoAfter
)
Select
d.refno
, i.refno
From
insertedi
inner join
deletedd
on d.id = i.id
-
How can someone hate triggers? They are a very useful means of maintaining low level data integrity. I have noticed most of the coding examples one finds in books to involve cursors to account for the multiple rows, but in practice (since I do hate cursors--an object more properly deserving of that) I have never found a need to do that. You just have to take the time to write a good query. Here's a trigger that seems similar to what you want. If some changes the date and appt was scheduled, we want to know about it. Here it is:
CREATE TRIGGER trg_u_MonitorScheduledDateChanges
ON dbo.tblWorkOrderMain
FOR UPDATE
AS
IF UPDATE(dtmScheduledDate) BEGIN
INSERT mtblChangedScheduledDate(strWorkOrder, dtmOriginalScheduledDate,
dtmUpdatedScheduledDate, strChangedBy, strRescheduleType)
SELECT wom.strWorkOrderNo, d.dtmScheduledDate, wom.dtmScheduledDate, wom.strLastEditedBy,
wom.strRescheduleType
FROM deleted d
JOIN tblWorkOrderMain wom
ON d.strWorkOrderNo=wom.strWorkOrderNo
WHERE d.dtmScheduledDate<>wom.dtmScheduledDate
ENDI could have used the inserted table, but in this case the updated table has the information I need.
-
Don't forget to handle NULLS:
WHERE ISNULL(d.dtmScheduledDate,'1/1/1980') <> ISNULL(wom.dtmScheduledDate,'1/1/1980')
-
Good point. I handle nulls in the trigger, but I didn't want to overclutter the answer. Still, your method of handling possible nulls is more elegant than mine, and I am going to use it in my future queries.
Secondly with multiple tables and a rollback in one all previous captures need to be undone.Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply