Update trigger plus update multiple rows

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

Transact-SQL (2000)

Update trigger plus update multiple rows

Author

Topic

mayhem
Starting Member

11 Posts

Posted - 2004-01-26 : 01:00:32

I have a table that contains the test results of my school's students. I created an update trigger so that when the test results field is updated, the grades field will be updated too. After I successfully tested the trigger by manually adding individual records into the table, I found that some other procedures that used to work no longer do so! Basically these are update procedures that mass update the table. The error returned is:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <=, >, >= or when the subquery is used as an expression.

My trigger code looks something like this (it's very long so i cut it):


ALTER TRIGGER dbo.MA_test_Results_doGrades
ON dbo.MA_test_Results
AFTER UPDATE
AS
IF UPDATE (Assmt_Marks)
BEGIN

IF (SELECT Assmt_Marks FROM inserted) < 45
BEGIN
UPDATE dbo.MA_test_Results SET Assmt_Grade = 'F' WHERE StudentId=(SELECT StudentId FROM inserted) AND Assmt_Id = (SELECT Assmt_Id FROM inserted)
END

END

There are more codes but they are just reptitive for other grades. An example of those procedures that no longer works after this trigger is added:

UPDATE    dbo.MA_test_Results
SET              Assmt_Marks = NULL
WHERE     (Assmt_Grade = 'VR')

I know it's the trigger causing the problem cos when I removed it the procedures work again. Thanks for any insight into this.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-01-26 : 01:09:08

This should work:

ALTER TRIGGER dbo.MA_test_Results_doGrades ON dbo.MA_test_Results AFTER UPDATE AS
IF UPDATE (Assmt_Marks)
BEGIN
UPDATE TR
SET Assmt_Grade = 'F'
FROM dbo.MA_test_Results TR INNER JOIN inserted I ON TR.studentID=I.StudentID AND TR.Assmt_ID=I.Assmt_ID
WHERE I.Assmt_Marks < 45
END

mayhem
Starting Member

11 Posts

Posted - 2004-01-26 : 04:49:56

Thanks it works great. I realised the insert table was causing the problem since a mass update will mean the insert table will now return multiple rows, but wasn't sure how to change it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources