update statement not firing a trigger

Hi,
I found a bug in one of my trigger that I have corrected. This trigger updates a field (status) in another table.

Now I've got a bunch of records with the wrong status that need to be updated. Instead of running an update statement on the status table to update the status field, I would like to update the first table, to fire the trigger and update the status field on the second table. This way I am sure, that the problem is completely fixed and there is no other cause.

However when I do it, it updates the first record but not the others. I've tried a fetch thinkink that it would update one record at a time, but same thing it only updates the first record.
My database is SQL 2000 db.
Thanks in advance for your help.

This is the code that I have used so far:
UPDATE tblRebateDetail
SET Model=Model
WHERE FRebateID IN (SELECT RebateID
FROM tblRebate R INNER JOIN tblStatus S
ON R.RebateID=S.FRebateID
WHERE Status='E' AND dbo.fcStatus(RebateType, FRebateID) ='RFI' AND RebateType='HWS' )

I've also used: ALTER TABLE tblRebateDetail ENABLE TRIGGER ALL to make sure that my trigger was enabled. Did not make any difference.

Then I used a fetch, same results:
DECLARE @RebateID Integer
DECLARE Reb_Cursor CURSOR FOR
SELECT RebateID
FROM tblRebate R INNER JOIN tblStatus S
ON R.RebateID=S.FRebateID
WHERE Status='E' AND dbo.REB_fc_Status(RebateType, FRebateID) ='RFI' AND RebateType='HWS'
ORDER BY RebateNo

OPEN Reb_Cursor

FETCH NEXT FROM Reb_Cursor
INTO @RebateID

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE tblRebateDetail
SET Model=Model
WHERE FRebateID=@RebateID

FETCH NEXT FROM Reb_Cursor
END

CLOSE Reb_Cursor
DEALLOCATE Reb_Cursor
GO


AnneFAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
post your trigger code.
0
AnneFAuthor Commented:
CREATE TRIGGER dbo.trRebateDetail ON dbo.tblRebateDetail
AFTER UPDATE
AS

/*
Last Updated:  21 Apr 2010
Update: Update Status table with RebAmt & Status
*/

DECLARE @RebateID Integer
DECLARE @Username VarChar(20)
DECLARE @Status VarChar(20)
DECLARE @RebAmt Integer
DECLARE @bUpdate Bit
DECLARE @intErr Int

SELECT @UserName=UserName FROM tblUser

SELECT @RebateID=FRebateID FROM INSERTED I INNER JOIN tblRebate P
ON I.FRebID=P.RebID
SELECT @RebAmt=CASE WHEN dbo.fcCalcAmt('HWS', @RebateID) IS NULL THEN 0 ELSE dbo.fcCalcAmt('HWS', @RebateID) END
SELECT @Status=dbo.fcStatus('RWT', @RebateID)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN

SELECT @bUpdate=
CASE WHEN @Status='P' THEN 0
WHEN Status=@Status AND RebAmt=@RebAmt
THEN 0 ELSE 1 END
FROM tblStatus (UPDLOCK)
WHERE FRebateID=@RebateID

IF @bUpdate=1
UPDATE tblStatus
SET Status=@Status,
RebAmt=@RebAmt,
RunFrom='Rebate Detail',
LastUpdated=GetDate(),
UpdatedBy=@UserName
WHERE FRebateID=@RebateID

SELECT @IntErr=@@ERROR
IF @IntErr<>0 GOTO PROBLEM

COMMIT TRAN

PROBLEM:
IF @IntErr<>0
BEGIN
ROLLBACK TRAN
INSERT INTO tblError
(ErrorType, ErrorNo, ErrorDesc, SectionName, ProcName, FSystemID, UserName, DateCreated)
VALUES ('Trigger Error', @IntErr, 'Update status from rebate detail', 'Rebate', 'trRebateDetail', 1, 'N/A', GetDate())
END
GO
0
Anthony PerkinsCommented:
As Tim guessed, your TRIGGER is only acting on one row, hence the problem you are having when you update more than one row.  Remember a TRIGGER fires once per INSERT/UPDATE/DELETE statement and not once per row.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

AnneFAuthor Commented:
When working on updating the group of records, I realised that it was only updating the first row, i thought fetching the records one by one and updating it would work as the update statement is updating one record at a time, but it didn't. Any suggestion on how I could do it?
0
Anthony PerkinsCommented:
>>Any suggestion on how I could do it?<<
Something like the following TRIGGER code should act upon all rows inserted, not just the last one:
CREATE TRIGGER dbo.trRebateDetail ON dbo.tblRebateDetail
    AFTER UPDATE
AS
/*
Last Updated:  21 Apr 2010
Update: Update Status table with RebAmt & Status
*/

DECLARE @intErr Int

UPDATE  s
SET     [Status] = dbo.fcStatus('RWT', s.FRebateID),
        RebAmt = ISNULL(dbo.fcCalcAmt('HWS', s.FRebateID), 0),
        RunFrom = 'Rebate Detail',
        LastUpdated = GETDATE(),
        -- The following sub-query makes the assumption that tblUser 
        -- only contains one row.  If that is not the case than the 
        -- SELECT statement in the original query is suspect:
        -- SELECT  @UserName = UserName
		-- FROM    tblUser
        UpdatedBy = (SELECT UserName FROM tblUser)
FROM	tblStatus s
		-- The following join makes the assumption that FRebateID
		-- belongs to tblRebate and not INSERTED
		INNER JOIN tblRebate P ON s.FRebateID = P.FRebateID
		INNER JOIN INSERTED I ON P.RebID = I.FRebID
WHERE   NOT (dbo.fcStatus('RWT', s.FRebateID) = 'P')
		AND NOT (s.Status = dbo.fcStatus('RWT', s.FRebateID)
				AND s.RebAmt = ISNULL(dbo.fcCalcAmt('HWS', s.FRebateID), 0))

SET  @IntErr = @@ERROR

IF @IntErr <> 0 
    BEGIN
        INSERT  tblError
                (ErrorType,
                 ErrorNo,
                 ErrorDesc,
                 SectionName,
                 ProcName,
                 FSystemID,
                 UserName,
                 DateCreated
                )
        VALUES  ('Trigger Error',
                 @IntErr,
                 'Update status from rebate detail',
                 'Rebate',
                 'trRebateDetail',
                 1,
                 'N/A',
                 GETDATE()
                )
    END
GO

Open in new window

0
AnneFAuthor Commented:
Thanks for the code.
However I have few questions if you wouldn't mind helping me:
1) Why your trigger is acting on multiple rows and mine only one.
-2)s it better? Or what are the implications? This trigger has been used for a while and is working OK (beside the bugs that I am trying to update at once), as it is only supposed to update one row at a time in a form. The form (Access adp) used has around 30 users working on it at any one time, and has being used for entering and updating data in multiple tables using triggers, there is a lot of search done through the records (more than 200,000). I had a lot of deadlock issues when I first started to develop it, and now it is working pretty well
3) Why fetching a row using a cursor, (code in my first post) does not fire the update trigger on each row? As I understood that my trigger was only updating a single row, I tried to create a function to update all the rows at once.
Thank you for your time.
0
Anthony PerkinsCommented:
1.  The following SQL statement can only retrieve one value for @RebateID:
SELECT @RebateID=FRebateID FROM INSERTED I INNER JOIN tblRebate P
ON I.FRebID=P.RebID
So if the following SQL statement contains more than one row, only the last one will be assigned:
SELECT FRebateID FROM INSERTED I INNER JOIN tblRebate P
ON I.FRebID=P.RebID

2. It is only better if it is correct and only you can tell that.  But if the result is correct, it should be more efficient as it is only one SQL statement as opposed to several.

3. I could not say for sure, but if I had to guess it would be that you are missing INTO @RebateID, as in:
OPEN Reb_Cursor
FETCH NEXT FROM Reb_Cursor
INTO @RebateID
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE tblRebateDetail
SET Model = Model
WHERE FRebateID = @RebateID
FETCH NEXT FROM Reb_Cursor
INTO @RebateID
END
CLOSE Reb_Cursor
DEALLOCATE Reb_Cursor

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AnneFAuthor Commented:
Option 3 was the solution to my problem, fetch was not working because of the missing Into in the fetch next.
I kept the trigger as it was, as it is only suppose to update one row.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.