December 21, 2010 at 11:14 am
To start, I am relatively new to SQL Server. I am using SQL Server 2008 R2 Express. For the record, I know that what I'm asking could be accomplished by designing a view, BUT I'm challenging myself to learn new aspects of MS SQL and triggers sound very interesting to me.
Here's what I'm trying to do. I have 3 tables and 1 view.
1. The table River_Segments with columns: ComID_PK, and Max_Pollutant.
2. The table River_Assessments with columns: ID_PK, and Listing_Category.
3. A middle table that keeps track of the many-to-many relationship between ComID_PK and ID_PK (this table may not be worth mentioning).
4. From these tables, I have a view called Max_Listing_Category, which is a collection of all UNIQUE ComID_PK from the River_Segments table with the maximum Listing_Category from the River_Assessments table. (Whew, this is confusing just typing.)
Here's what I'm trying to do.
I want to create a trigger that updates the column Max_Pollutant from the table River_Segments, anytime a change occurs in the column Listing_Category from my view. Can data from a view be used to update data in another table?
If this isn’t possible then I'd like to get help designing a trigger that simply updates the column Max_Pollutant (River_Segments table) from the column Listing_Category (River_Assessments table). I’ve searched the web to understand triggers, and while I understand them conceptually I do not understanding their syntax.
Thanks in advance.
December 21, 2010 at 12:23 pm
I think you may need to brush a bit on the concept of views also. You do not put a trigger on a view nor does anything update a view. A trigger belongs to a table. So it sounds like you want an update trigger on your River_Assessments table?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply