Trigger behaving differently on multi-row update

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

General SQL Server Forums

Database Design and Application Architecture

Trigger behaving differently on multi-row update

Author

Topic

nuclearsiloman
Starting Member

9 Posts

Posted - 2007-08-01 : 09:24:02

Let me set the scene:

I have an update trigger on a table. When a specific column is updated, I get the rowid from 'inserted' and then pass it via service broker to another database that will fire off a maintenance routine at a later time. This whole process seems to work fine if I update a single row at a time through Query Analyzer.

During testing (of the service broker part) I found that if in Query Analyzer I run an update that updates all of the records at once, then the trigger seems to fire only once for the entire process, therefore killing the rest of my process.

I would have thought that regardless of how a record was being updated the trigger would fire atomically for each row.

Any guidance on this would be MOST appreciated!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 09:39:15

No, it does not. It is explained in Books Online.
Post your code for trigger here and we might come up with something that does work.

E 12°55'05.25"
N 56°04'39.16"

nuclearsiloman
Starting Member

9 Posts

Posted - 2007-08-01 : 09:47:19

Thank you for the quick reply!! Here is my trigger:
-------------------------------------------------
ALTER TRIGGER [trMoveOrderToBSF]
ON [dbo].[orders]
AFTER UPDATE

AS

SET NOCOUNT ON;

IF ( UPDATE (orderStatus))
BEGIN
declare @xUpdatedOrderStatus int
declare @xUpdatedOrderID int

SELECT @xUpdatedOrderStatus=orderStatus from inserted
if @xUpdatedOrderStatus = 3
BEGIN TRY
BEGIN TRANSACTION
declare @Message XML
SET @Message = (select idOrder, partnerID, orderStatus from inserted FOR XML PATH('Order'), TYPE);
Declare @ConversationHandle uniqueidentifier
Begin Dialog @ConversationHandle
From Service serviceSendProductOrder
To Service 'serviceReceiveProductOrder'
On Contract contractProductOrder
WITH Encryption=off;
SEND
ON CONVERSATION @ConversationHandle
Message Type msgTypeProductOrder (@Message);

COMMIT TRANSACTION
END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
-- Raise an error with the details of the exception
EXEC errorLogging.dbo.spErrorHandling

END CATCH
END

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-01 : 09:52:15

[code]
SELECT @xUpdatedOrderStatus=orderStatus from inserted
if @xUpdatedOrderStatus = 3
[/code]

What if there is more than 1 OrderStatus?

You need to think in set based terms

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 09:56:27

[code]IF UPDATE(orderStatus)
BEGIN
DECLARE @UpdatedOrderStatus INT,
@UpdatedOrderID INT,
@Message XML,
@ConversationHandle UNIQUEIDENTIFIER

IF EXISTS (SELECT * FROM inserted WHERE orderStatus = 3)
BEGIN
TRY
BEGIN TRANSACTION

SET @Message = (SELECT idOrder, partnerID, orderStatus FROM inserted WHERE orderStatus = 3 FOR XML PATH('Order'), TYPE)

BEGIN DIALOG @ConversationHandle
FROM SERVICE serviceSendProductOrder
TO SERVICE 'serviceReceiveProductOrder'
ON CONTRACT contractProductOrder
WITH Encryption = OFF

SEND

ON CONVERSATION @ConversationHandle
MESSAGE TYPE msgTypeProductOrder(@Message)

COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

-- Raise an error with the details of the exception
EXEC errorLogging.dbo.spErrorHandling

END CATCH
END
END[/code]

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-08-01 : 10:16:09

You might want to do:


IF EXISTS
(SELECT * 
FROM inserted AS I 
     JOIN deleted AS D
         ON D.MyPK = I.MyPK
WHERE     I.orderStatus = 3
      AND COALESCE(D.orderStatus, 0) <> 3
)
...
SET	@Message =
(
SELECT I.idOrder, I.partnerID, I.orderStatus 
FROM   inserted 
     JOIN deleted AS D
         ON D.MyPK = I.MyPK
WHERE     I.orderStatus = 3
      AND COALESCE(D.orderStatus, 0) <> 3
FOR XML PATH('Order'), TYPE
)

so that you only process items that have an orderStatus = 3 now, ~~but~~ and previously had some different value.

Kristen

nuclearsiloman
Starting Member

9 Posts

Posted - 2007-08-01 : 10:17:41

Thanks for the comments but I'm a bit confused...

I check to see if the orderStatus column is the one being updated, ie, IF ( UPDATE (orderStatus)) because I don't care about other column updates. Then I check to see if it's been updated to a 3 (other values aren't important).

the INSERTED table should have the row values being updated. If I add a WHERE clause based on idOrder (which is unique in the table), then the trigger only passes one record, but the last. I can't use orderStatus in the where because many records have 3 as their value. I only want to grab the record just updated.

nuclearsiloman
Starting Member

9 Posts

Posted - 2007-08-01 : 10:20:50

Thanks Kristen, but won't this return all records that have an orderStatus of 3? I need to create one Service Broker message per update on the table.

quote:

Originally posted by Kristen

You might want to do:


IF EXISTS
(SELECT * 
FROM inserted AS I 
     JOIN deleted AS D
         ON D.MyPK = I.MyPK
WHERE     I.orderStatus = 3
      AND COALESCE(D.orderStatus, 0) <> 3
)
...
SET	@Message =
(
SELECT I.idOrder, I.partnerID, I.orderStatus 
FROM   inserted 
     JOIN deleted AS D
         ON D.MyPK = I.MyPK
WHERE     I.orderStatus = 3
      AND COALESCE(D.orderStatus, 0) <> 3
FOR XML PATH('Order'), TYPE
)

so that you only process items that have an orderStatus = 3 now, ~~but~~ and previously had some different value.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 10:21:51

Please calm down and read the answer I gave you a while ago.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 10:23:29

quote:
Originally posted by nuclearsiloman

I need to create one Service Broker message per update on the table.

Then save the values you need in a temporary table and iterate it.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-08-01 : 10:27:05

"the INSERTED table should have the row values being updated"

No, the [inserted] table has the rowS being updated, so you must handle the fact that it contains multiple rows.

"Then save the values you need in a temporary table and iterate it"

Nah, just iterate [inserted] ... assuming some unique / PK column

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 10:29:15

[code]IF UPDATE(orderStatus) AND EXISTS (SELECT * FROM inserted WHERE orderStatus = 3)
BEGIN
TRY
DECLARE @UpdatedOrderStatus INT,
@UpdatedOrderID INT,
@Message XML,
@ConversationHandle UNIQUEIDENTIFIER,
@ID INT

DECLARE @Records TABLE (ID INT IDENTITY, idOrder INT, partnerID INT)

INSERT @Records (idOrder, partnerID)
SELECT idOrder,
partnerID
FROM inserted
WHERE orderStatus = 3

SELECT @ID = MAX(ID)
FROM @Records

BEGIN TRANSACTION

WHILE @ID >= 1
BEGIN
SET @Message = (SELECT idOrder, partnerID, 3 AS orderStatus FROM @Records WHERE ID = @ID FOR XML PATH('Order'), TYPE)

BEGIN DIALOG @ConversationHandle
FROM SERVICE serviceSendProductOrder
TO SERVICE 'serviceReceiveProductOrder'
ON CONTRACT contractProductOrder
WITH Encryption = OFF

SEND

ON CONVERSATION @ConversationHandle
MESSAGE TYPE msgTypeProductOrder(@Message)

SET @ID = @ID - 1
END

COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

-- Raise an error with the details of the exception
EXEC errorLogging.dbo.spErrorHandling

END CATCH
END[/code]

E 12°55'05.25"
N 56°04'39.16"

nuclearsiloman
Starting Member

9 Posts

Posted - 2007-08-01 : 10:29:37

quote:
Originally posted by Peso

Please calm down and read the answer I gave you a while ago.

E 12°55'05.25"
N 56°04'39.16"

Peso, thank you for you help. I am calm, completely. This isn't the first or last time I've stumbled upon something like this. No worries.

The root of the issue is that I have always believed that when a row is updated it fires the update trigger attached, the trigger completes, and then the next row is updated, the trigger fires, then completes....

I suppose that when an application is updating a single row at at time this holds true, but when doing a mass update via Query Analyser, this isn't the case.

I think the suggestion of the iterating through a temp table might be a sound one. Because this will handle single updates from the application and batch ones from Query Analyser.

Kristen
Test

22859 Posts

Posted - 2007-08-01 : 10:37:15

"I suppose that when an application is updating a single row at at time this holds true, but when doing a mass update via Query Analyser, this isn't the case."

Well that's true, but anything could update multiple rows - e.g. a future application change

The iteration thingie will be fine, but if you really only want to allow single row update you could:


IF (SELECT COUNT(*) FROM inserted) > 1
BEGIN
   RAISERROR ...
   ROLLBACK
END

Will the service broker not handle XML covering multiple records? That would be better still!

Kristen

nuclearsiloman
Starting Member

9 Posts

Posted - 2007-08-01 : 10:46:04

Peso - thanks, you're the best! Your solution worked perfectly for me. It's one of the solutions that seem obvious once you see it. I guess I was hung up on my erroneous base belief about a trigger.

Kristen - thank you for your suggestions as well. Service Broker could pass multiple rows in its message and then I could parse the message on the recieve side, but I wanted to keep a single order per message to minimize backlog if the receiver side goes offline. It also helps me to keep things straight in my aging brain!

This is my first time using the forums here - it's been a very positive experience. Thank you all!

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-01 : 10:47:24

quote:
Originally posted by nuclearsiloman
The root of the issue is that I have always believed that when a row is updated it fires the update trigger attached, the trigger completes, and then the next row is updated, the trigger fires, then completes....

Nope....is your background Oracle or DB2?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

nuclearsiloman
Starting Member

9 Posts

Posted - 2007-08-01 : 10:51:51

No... just one of those things that seeps into one's head and after awhile seems like fact. I'm straight now though and won't have a related problem again. I'm not smart, but learn fast.

quote:
Originally posted by X002548

quote:
Originally posted by nuclearsiloman
The root of the issue is that I have always believed that when a row is updated it fires the update trigger attached, the trigger completes, and then the next row is updated, the trigger fires, then completes....

Nope....is your background Oracle or DB2?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-08-01 : 11:13:08

"I wanted to keep a single order per message to minimize backlog if the receiver side goes offline"

I don't reckon that makes any difference, but I'm not familiar with your scenario of course ...

If you send multiple-record XML to Service Broker you have optimum set-based efficiency in SQL Server, and a single "transaction" to Service Broker. (Whereas row-by-row requires looping in SQL Server and "more chatty" conversations with Service Broker).

If your connection to Service Broker fails all bets are off, presumably? So your update is going to be rolled back in SQL Server? (and if not then Service Broker has process SOME, but not all, of the transactions - and will miss the rest ...)

(Please also note my earlier suggestion [regarding handling multiple-row updates] about only passing changes to Service Broker if the Status has changed from some value <> 3 [to become "3"])

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 11:13:34

quote:
Originally posted by nuclearsiloman

...but I wanted to keep a single order per message to minimize backlog if the receiver side goes offline.

Will there be a difference of

1) 1 transaction with 10 messages with 1 record each
2) 1 transaction with 1 message with 10 records

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-08-01 : 11:17:23

"Will there be a difference"

Yeah, (2) is more efficient

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 11:43:54

The rhetorical part got missing...

E 12°55'05.25"
N 56°04'39.16"