quote:

---

Originally posted by Lamprey

Do you ahve some sample data and expected results? Is there a one to one relationship between the two tables or are there many Rivver_assessments and you want to take the MAX listing_category for a given ID and use that max to update the River_segments table? Depending on the relationship between the tables there might be other options besides a trigger.

This link might help preparing data:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

---

-- Setup
CREATE TABLE dbo.River_Assessments (ID INT, Listing_Category INT)
CREATE TABLE dbo.River_Segments (ComID INT, Max_Pollutant INT)

INSERT dbo.River_Assessments 
    (ID , Listing_Category)
VALUES
    (1, 1),    
    (2, 1),
    (3, 1),
    (4, 1),
    (5, 1) 
    
INSERT dbo.River_Segments 
    (ComID , Max_Pollutant)
VALUES
    (1, 1),    
    (2, 1),
    (3, 1),
    (4, 1),
    (5, 1) 
GO

-- Create Trigger    
CREATE TRIGGER dbo.River_Assessments_Update
    ON dbo.River_Assessments
    AFTER UPDATE
AS
BEGIN
    MERGE
        dbo.River_Segments AS Target
    USING
        Inserted AS Source
        ON Source.ID = Target.ComID        
    WHEN MATCHED THEN
        UPDATE SET Target.Max_Pollutant = Source.Listing_Category;

END   

-- Check starting point
SELECT * FROM dbo.River_Assessments
SELECT * FROM dbo.River_Segments

--Change data
UPDATE dbo.River_Assessments
SET Listing_Category = 5
WHERE ID IN (2, 4)

-- Verify results
SELECT * FROM dbo.River_Assessments
SELECT * FROM dbo.River_Segments

-- Cleanup
DROP TRIGGER dbo.River_Assessments_Update
DROP TABLE dbo.River_Assessments
DROP TABLE dbo.River_Segments