SQL Server triggers "For Each Row" equivalent

Question

I need to update multiple rows in a Parts table when a field in another table changes and I want to use a trigger. The reason for the trigger is many existing application use and modify the data and I don't have access to all of them. I know some databases support a For Each Row in the trigger statement but I don't think Microsoft does.

Specificly I have two tables Parts and Categories.

Parts has Part#, Category_ID, Part_Name and Original and lots of other stuff

Category has Category_ID and Category_name.

Original is a concatenation of Category_Name and Part_Name separated by a ':'

For example Bracelets:BB129090

If someone changes the Category_Name (for excample from Bracelets to Bracelets), the Original field must be updated in every row of the Parts table. While this is an infrequent event it happens enough to cause trouble.

No Web and desktop applications uses Original

All Accounting application use only Original

It is my task to keep Accounting and the other application in sync.

I did not design the database and the company that wrote the accounting program will not change it.

Answer 1

I guess in your case there is no need for a row-level trigger.

You can do something like

IF UPDATE(Category_Name)
    UPDATE Parts
    SET Original = inserted.Category_Name + ':' + Part_Name
    FROM Parts
    INNER JOIN inserted ON Parts.Category_ID = inserted.Category_ID

as an UPDATE trigger on the Category table.

If you really need per-row processing (say, of a stored procedure), you need a CURSOR or a WHILE loop over inserted.

Answer 2

If you can alter the table schemas, on option that you would have that would ensure that the Original column is always up to date, no matter what, is to make Original a computed column - a column that's computed from the Category_Name plus the Part_Name as needed.

For this, you need to create a stored function that will do that computation for you - something like this:

CREATE FUNCTION dbo.CreateOriginal(@Category_ID INT, @Part_Name VARCHAR(50))
RETURNS VARCHAR(50)
WITH SCHEMABINDING
AS BEGIN
  DECLARE @Category_Name VARCHAR(50)

  SELECT @Category_Name = Category_Name FROM dbo.Category 
         WHERE Category_ID = @Category_ID

  RETURN @Category_Name + ': ' + @Part_Name
END

and then you need to add a column to your Parts table which will show the result of this function for each row in the table:

ALTER TABLE Parts
  ADD Original AS dbo.CreateOriginal(Category_ID, Part_Name)

The main drawback is the fact that to display the column value, the function has to be called each time, for each row.

On the other hand, your data is always up to date and always guaranteed to be correct, no matter what. No triggers needed, either.

See if that works for you - depending on your needs and the amount of data you have, it might well perform just fine for you.

Marc

Answer 3

The Original column violates 1NF, which is a very bad idea. You can either

1. Skip the column completely and concatenate it in each query (probably not the best solution, but I argue that it's probably better than the trigger).
2. Create a view over the table and have the Original column in the view (probably what I would do), or
3. Make Original a computed column, which is the best way if you want to create an index on it.

Answer 4

Or another option: why don't you just create a view over those two tables, for your Accounting department, which contains this concatenated column:

CREATE VIEW dbo.AccountingView
AS
  SELECT
    p.PartNo, p.Part_Name, p.Category_ID,
    c.Category_Name + ':' + p.PartName as 'Original'
  FROM
    Parts p
  INNER JOIN
    Category c ON p.Category_ID = c.Category_ID

Now your Accounting people can use this view for their reporting, it's always fresh, always up to date, and you don't have to worry about update and insert triggers and all those tricky things.....

Marc