October 28, 2003 at 1:23 am
We use trigger on many tables to track the last modification, like this:
CREATE TRIGGER [ABC_modified] ON dbo.ABC
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
UPDATE ABC
SET lastModifiedBy = suser_sname(), lastModifiedDate = getdate()
FROM inserted
WHERE ABC.ABC_ID = inserted.ABC_ID
SET NOCOUNT OFF
This works fine with single- and multiple-records updates (e.g. from a Query Analyzer UPDATE statement). However, some triggers have more complex code, like this (only part shown):
DECLARE @work TABLE (
new_status smallint NOT NULL,
phone1 varchar(20) DEFAULT NULL NULL,
phone2 varchar(20) DEFAULT NULL NULL,
phone3 varchar(20) DEFAULT NULL NULL
)
INSERT INTO @work
SELECT (CASE
WHEN pc.verifiedBy = 0 AND pc.accountBalance < pq.invoiceLimit THEN 0
WHEN pc.verifiedBy = 0 AND pc.accountBalance > pq.invoiceLimit THEN 2
WHEN pc.verifiedBy <> 0 AND pc.accountBalance < pq.creditLimit THEN 0
WHEN pc.verifiedBy <> 0 AND pc.accountBalance > pq.creditLimit THEN 2
ELSE 0
END) AS new_status,
pc.Phone1, pc.Phone2, pc.Phone3
FROM inserted pt
LEFT JOIN PostClients pc
LEFT JOIN PostQualities pq ON pc.quality = pq.PostQualityID
ON pt.ofPostClientID = pc.PostClientID
Here, running a multiple-record update will result an error saying that more than one record were returned. I was (wrongly) under the impression that the trigger was fired once for each record being updated, but it seems that we're looking at a bulk operation. Thus, how do a write the equivalent of a FOR/NEXT loop with an ADO .MoveNext equivalent to process all the records?
Hints & tips would be greatly appreciated!
Dave
October 28, 2003 at 4:49 am
First thing (and best) is to try to make the code work on a set instead of a single row. If you can't (or dont want) to do that, then usually the way you handle it is to put a cursor in the trigger, then use the variables loaded in the cursor to do the work inside the trigger or to pass them to a stored proc.
Andy
October 28, 2003 at 5:11 am
With only a part shown it is hard to say why you run into the issue. The part you show would not cause this so I must assume more happens that would be to the condition of single row only. If you would post we can better understand and potentially help you find a proper solution.
October 28, 2003 at 12:12 pm
Okay, here goes - this is the entire trigger:
CREATE TRIGGER [PostTransactions_inserted] ON dbo.PostTransactions
FOR INSERT
AS
SET NOCOUNT ON
IF (SELECT affectsBalance FROM inserted) = -1
BEGIN
DECLARE @thisClient AS int
DECLARE @thisPhone AS varchar(20)
SET @thisClient = (SELECT ofPostClientID FROM inserted)
UPDATE PostClients
SET accountBalance = accountBalance + (SELECT transactionAmount FROM inserted)
WHERE PostClientID = @thisClient
DECLARE @work TABLE (
new_status smallint NOT NULL,
phone1 varchar(20) DEFAULT NULL NULL,
phone2 varchar(20) DEFAULT NULL NULL,
phone3 varchar(20) DEFAULT NULL NULL
)
INSERT INTO @work
SELECT (CASE
WHEN pc.verifiedBy = 0 AND pc.accountBalance < pq.invoiceLimit THEN 0
WHEN pc.verifiedBy = 0 AND pc.accountBalance > pq.invoiceLimit THEN 2
WHEN pc.verifiedBy <> 0 AND pc.accountBalance < pq.creditLimit THEN 0
WHEN pc.verifiedBy <> 0 AND pc.accountBalance > pq.creditLimit THEN 2
ELSE 0
END) AS new_status,
pc.Phone1, pc.Phone2, pc.Phone3
FROM inserted pt
LEFT JOIN PostClients pc
LEFT JOIN PostQualities pq ON pc.quality = pq.PostQualityID
ON pt.ofPostClientID = pc.PostClientID
DELETE FROM PostGrayList
WHERE ofPostClientID = @thisClient
AND grayListReason IN (0, 2)
IF (SELECT new_status FROM @work) = 2
BEGIN
SET @thisPhone = (SELECT phone1 FROM @work)
IF @thisPhone IS NOT NULL
BEGIN
INSERT INTO PostGrayList (CLI, ofPostClientID, grayListReason)
SELECT @thisPhone, @thisClient, 2 FROM @work
WHERE NOT EXISTS (SELECT * FROM PostGrayList WHERE CLI = @thisPhone)
END
SET @thisPhone = (SELECT phone2 FROM @work)
IF @thisPhone IS NOT NULL
BEGIN
INSERT INTO PostGrayList (CLI, ofPostClientID, grayListReason)
SELECT @thisPhone, @thisClient, 2 FROM @work
WHERE NOT EXISTS (SELECT * FROM PostGrayList WHERE CLI = @thisPhone)
END
SET @thisPhone = (SELECT phone3 FROM @work)
IF @thisPhone IS NOT NULL
BEGIN
INSERT INTO PostGrayList (CLI, ofPostClientID, grayListReason)
SELECT @thisPhone, @thisClient, 2 FROM @work
WHERE NOT EXISTS (SELECT * FROM PostGrayList WHERE CLI = @thisPhone)
END
END
END
SET NOCOUNT OFF
October 29, 2003 at 6:56 am
You are probably going to have to cursor through this to do row-by-row processing as you have a lot of subqueries that are likely to return multiple rows. For example,
SET accountBalance = accountBalance + (SELECT transactionAmount FROM inserted
Also, the statement:
IF (SELECT affectsBalance FROM inserted) = -1
I would change to:
IF EXISTS (SELECT 1 FROM inserted WHERE affectsBalance = -1)
This will cover the whole set. Then I would create your cursor on just those inserted records where affectsBalance = -1. Cursors do add extra coding but I see no way around it in this case unless you can join your subqueries in such a fashion so as to limit the results to one inserted record. For example:
UPDATE PostClients
SET accountBalance = pc.accountBalance + i.trasactionAmount
FROM PostClients pc JOIN inserted i ON pc.PostClientID = pc.PostClientID
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply