Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Multiple row update trigger problem

Author  Topic 

suls
Starting Member

5 Posts

Posted - 2006-11-29 : 09:09:35
Hi all

A little stumped on an update trigger.
Asumming only 1 row updated my code logic would look somthing like this

if updated(field1)
if field1 = 1
if field2 = 1
begin
update mytable set field2=0 where mytable.id = (select id from inserted)

exec SP_WriteBillingRecord (select ID from inserted)
end
exec SP_WriteBillingRecord (select ID from inserted)
endif
else
exec SP_WriteBillingRecord (select ID from inserted)
endif

Well thats kind of the basic logic, the problem is how do I apply row by row logic to the trigger when multiple rows are updated.

I know a cursor would be an absoluote nightmare but am struggling to think of another way to get the same result.

Any ideas anyone or is this impossible ?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 09:19:23
This is your logic above
if updated(field1)
if field1 = 1
if field2 = 1
begin
update mytable set field2=0 where mytable.id = (select id from inserted)
exec SP_WriteBillingRecord (select ID from inserted)
end

exec SP_WriteBillingRecord (select ID from inserted)
end
else
exec SP_WriteBillingRecord (select ID from inserted)
end

What you can do, is to do a update a dummycolumn to a certain value and then make the SP to only work with that rows.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

suls
Starting Member

5 Posts

Posted - 2006-11-29 : 11:10:58
Sorry think I have been clear as mud

Dependant on a bit field I need to exec a stored procedure

eg

if deleted.field1 <> inserted.field1
Begin
<<<< DO Stuff >>>>
End

How does this work on a multiple row update ??

Thanks
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-11-29 : 11:51:33
I don't believe that

if updated(field1)

helps you. It only tells you that at least one record in INSERTED has changed with regard to that column, not than any specific record has changed.

You can find out which records have changed in a specific field with:

SELECT ...
FROM inserted AS I
LEFT OUTER JOIN deleted AS D
ON D.MyPK = I.MyPK
WHERE (I.MyCol <> D.MyCol OR (I.MyCol IS NULL AND D.MyCol IS NOT NULL) OR (I.MyCol IS NOT NULL AND D.MyCol IS NULL))

there is an undocumented msforeach (SP?) Sproc that will execute a "templated" SProc for each row in a recordset, or you will have to loop/cursor around the recordset.

Probably better to insert the PKs [and any other relevant data] from the above query into a temporary table, and then loop round each row, rather than using a cursor.

Kristen
Go to Top of Page

suls
Starting Member

5 Posts

Posted - 2006-11-30 : 06:02:27
Thanks Kristen

Thats exactly the heads up I was looking for. I'll do some coding and hopefully get something up and running.

Many many thanks
Go to Top of Page

suls
Starting Member

5 Posts

Posted - 2006-12-04 : 10:45:04
Right thanks for the tips everyone,

pretty much got what I want working now and in case it should help someone in the future it looks like this in its basic form.

Many thanks


alter TRIGGER TrgTest
ON client
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- Create temporary Table of updated positive and negative responders
select
i.client_id,
d.positive_responder as Dpositive_Responder,
d.negative_responder as Dnegative_Responder,
i.positive_responder as Ipositive_Responder,
i.negative_responder as Inegative_Responder
into #tmpResponders
from inserted as I
left outer join deleted as d on d.client_id = i.client_id
where ((i.positive_responder <> d.positive_responder) or
(d.negative_responder <> d.negative_responder))
-- Declare Variables
declare @dPos bit,
@dNeg bit,
@iPos bit,
@iNeg bit,
@NextRowId int,
@CurrentRowId int,
@LoopControl int
-- Initialize Variables
select @LoopControl =1
select @nextRowId = min(client_id) from #TmpResponders
-- Make sure we have some data to work with
if isnull(@nextRowId,0)=0
begin
--select 'No data found to work with'
return
end
-- Get the first row
select @CurrentRowId = Client_id,
@dPos = Dpositive_Responder,
@dNeg = Dnegative_Responder ,
@iPos = Ipositive_Responder,
@iNeg = Inegative_Responder
from #TmpResponders
where client_id = @NextRowId
-- Start the main processing Loop
while @LoopControl = 1
begin
--------------------------------
-- Do Row by row processing here
--------------------------------
print @CurrentRowId
--------------------------------
--------------------------------
--------------------------------
-- Reset Looping variables
select @NextRowId = null
-- Get the next Client_ID
select @NextRowId = min(client_id)
from #TmpResponders
where Client_Id > @CurrentRowID
-- Check we have a next rowID
if isnull(@NextRowId,0) = 0
begin
Break -- Stop processing the loop
end
-- Get the next row
select @CurrentRowId = Client_id,
@dPos = Dpositive_Responder,
@dNeg = Dnegative_Responder ,
@iPos = Ipositive_Responder,
@iNeg = Inegative_Responder
from #TmpResponders
where client_id = @NextRowId
end -- Loop End
END
GO

Go to Top of Page
   

- Advertisement -