Old SQL Code That Won't Run in SQL 2005

Question

Post reply

Old SQL Code That Won't Run in SQL 2005

Roger Abram

Hall of Fame

Points: 3683
More actions
June 3, 2010 at 8:02 am

#219670

We have a trigger that no longer runs in SQL 2005 after migrating from SQL 2000. I know it is because of the *= in the third line below but just not sure what to change it to. Not sure what *= was used for way back when.
Can anyone Shed some light?
SELECT @szTitle = COALESCE( M.TITLE,'None')
FROM Meet_Master M, inserted
WHERE inserted.COURSE_CODE *= M.MEETING
Thank you.
Roger

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Pam Brisjar SSChampion Points: 12094 More actions · Answer 1

Outer join:

http://msdn.microsoft.com/en-us/library/aa213228%28SQL.80%29.aspx

WayneS SSC Guru Points: 95405 More actions · Answer 2

Specifically, a LEFT OUTER JOIN.

Change the code to:

SELECT @szTitle = COALESCE( M.TITLE,'None')

FROM Meet_Master M

LEFT JOIN inserted i

ON i.COURSE_CODE = M.MEETING

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95405 More actions · Answer 3

Of bigger concern... you are selecting, within a trigger, into a variable. This usually means that the trigger is not set up to properly handle multiple rows of data - it looks like an assumption has been made that only 1 record will ever be affected by that trigger.

I don't know if this trigger is for update or insert (since it's referencing the inserted table, you won't be doing a delete)... but if you are doing one of these statements that affects > 1 record, I think the trigger will not be operating on all of the rows affected.

If you post the entire trigger, we can help you straighten this out.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2