Reputation:
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.
Upvotes: 1
Views: 13779
Reputation: 754668
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
Upvotes: 2
Reputation: 37215
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.
Upvotes: 1
Reputation: 34401
The Original column violates 1NF, which is a very bad idea. You can either
Upvotes: 1
Reputation: 754668
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
Upvotes: 0