Help need with multirow update trigger

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)

Help need with multirow update trigger

Author

Topic

1981tom0
Starting Member

4 Posts

Posted - 2010-01-21 : 09:27:32

I am having problems writing an Update trigger that deals with multiple records and would appreciate some help

I want an update on one table to update fields in another table. I want to update a table called ep_StudentInformation when a change occurs in a StudentCareer table. There are three relevant columns: StudentId, Programme and Year. Unique records are determined by StudentId and Programme.

I first need to check if either the Programme or Year has been updated:

IF UPDATE(Programme) OR UPDATE(Year)

Then I want to update records in the ep_StudentInformation table with the Inserted values for Programme and Year where ep_StudentInformation.Programme = Deleted.Programme AND ep_StudentInformation.StudentId = Inserted.StudentId

(I need to match the Deleted.Programme and Inserted.StudentId to find the unique record to update in the ep_StudentInformation table)

For example, I have a working one-record only trigger using the following code:

DECLARE @StudentId varchar(11)
SELECT @StudentId = (SELECT StudentId FROM Inserted)

DECLARE @UpdatedProgramme varchar(100)
SELECT @UpdatedProgramme = (SELECT Programme FROM Inserted)

DECLARE @OriginalProgramme varchar(100)
SELECT @OriginalProgramme = (SELECT Programme FROM Deleted)

DECLARE @UpdatedStudentYear tinyint
SELECT @UpdatedStudentYear = (SELECT Year FROM Inserted)

UPDATE [ep_StudentInformation]
SET [Programme] = @UpdatedProgramme, [Year] = @UpdatedStudentYear
WHERE [StudentId] = @StudentId AND [Programme] = @OriginalProgramme

I would really appreciate some help in turning this into a trigger that works when multiple records are updated.

Thanks
Tom

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 09:55:29

Assuming I've understood what you are doing correctly, perhaps this?


UPDATE U
SET [Programme] = I.Programme , [Year] = I.Year 
FROM [ep_StudentInformation] AS U
     JOIN inserted AS I
         ON I.StudentId = U.StudentId
     JOIN deleted AS D
         ON D.StudentId = U.StudentId
        AND D.Programme = U.Programme

If there can be two records for the same StudentID in the same Update then you need something else, which will not have changed, to join [inserted] and [deleted] - in addition to the [StudentId] I have used

1981tom0
Starting Member

4 Posts

Posted - 2010-01-21 : 11:24:34

Thanks for your help Kristen. Unfortunately it is not working as it is possible that there could be two or more studentids the same in an update if a student is enrolled on more than one programme (hence needing both the studentid and programme to determine unique rows)

e.g.

StudentCareer table (simplified):

StudentId | Programme | Year
1234567 | 00018 | 1
1234567 | 00019 | 2

StudentInformation table:

StudentId | Programme | Year | OtherColumn1 | OtherColumn2
1234567 | 00018 | 1 | xxx | xxx
1234567 | 00019 | 2 | xxx | xxx

If both the StudentCareer table programme codes changed in an update there would be two instances of studentid 1234567, although they would have different Deleted.Programme values. The only columns common to both tables are the StudentId, Programme and Year.

Thanks again for any help.
Tom

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 11:48:07

If you do have a student, enrolled in multiple programmes, and IF that student changes programme, as part of the update, then I think all bets are off

unless you can find a way to tie INSERTED and DELETED together by some other columns that a) unique identify the row and b) won't change during the update.

If all else fails you could add an IDENTITY column to the table - just to be able to match before/after rows.

1981tom0
Starting Member

4 Posts

Posted - 2010-01-21 : 11:57:05

Hmmm... I was beginning to come to the same conclusion myself! I cannot add an identity field to the StudentCareer table as it is receiving a data feed from a different database which I am not in control of - it could potentially delete then re-insert records - if i used an identity field for referencing this table it would break as soon as the data feed did this.

Thanks for your help.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-21 : 12:09:32

quote:
I cannot add an identity field to the StudentCareer table as it is receiving a data feed from a different database which I am not in control of

It is just an identity that adds a unique value to a row automatically..This shouldn't affect your existing Inserts...how are you inserting currently?

quote:
if i used an identity field for referencing this table it would break as soon as the data feed did this.

Not sure what you mean by this...can u explain?

1981tom0
Starting Member

4 Posts

Posted - 2010-01-21 : 12:24:21

"Record management" (for want of a better description) in the StudentCareers table is via an external data feed. I'm seeking clarification right now on how this actually inserts/updates/deletes the records.

Ignore my comments about the identity column as I misread your post - you are correct that an identity column could be added just for the purpose of identifying before/after rows.

Again, thanks for your continued help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources