Using TRIGGERs to update data when data is changed.

Question

Using TRIGGERs to update data when data is changed.

Simon Doubt

Mr or Mrs. 500

Points: 565
More actions
January 6, 2007 at 11:39 pm

#171738

Hello all:
I am new to using triggers, but understand their function in auditing tables.
I haved reviewed the BOOKS ONLINE topic for CREATE TRIGGER, as well as the UPDATE() function. What I am interested in, however, is understanding how a trigger can be used to change data in one table based on a CHANGE (not just an update) to the data in the trigger table.
For example, let's say that I have two tables: FOOD, and FOODTYPE.
When a new row is inserted or updated in the FOOD table, I would like to know how to insert or update a row in the FOODTYPE table.
Let's say a row in the FOOD table looks like this:
FOODID(PK)     FOODNAME
========    ========
1                     Apple
And a row in the FOODTYPE looks like this:
FOODTPYEFKID(FK)     FOODTYPENAME
============      ===========
1                                Fruit
Now, let's say I change 'Apple' to 'Potato'.
I would like to know how to update the FOODTYPE table (the FOODTYPENAME column) from 'Fruit' to 'Vegetable'.
I realize that this is a fairly stupid example, but I wanted to keep things simple.
Any input would be greatly suggested.
Thank you for any help.
-Simon

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I think we'll need the actual problem to help you.

In the you presented you just don't need the trigger. When changing the name of the food, you should also change the foodtypeid in the food table. The foodtypename from the foodstypes table should not be changed at all in this case.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 2

You wouldn't use a trigger in this example. Food type would be a static list of food types and would not reference the Food table at all. The food table would have a FK food type ID in it. If you change apple to potato, you would change the food type id in the Foods table.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Simon Doubt Mr or Mrs. 500 Points: 565 More actions · Answer 3

Ninja & Robert:

Thanks for the replies.

I realize that my example didn't make sense, I was just trying to keep things simple. I guess my question wasn't clear.

Anyway, I found the answer that I was looking for, which is that I should join (via the PK) and then compare the value in the virtual DELETED table to the value in the virtual INSERTED table to see if anything has changed, and then update as required.

-Simon

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Yup, that should cover it.

Thanx for keeping everyone posted.

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 5

Simon,

SQL server also supports a function to see if a column has been updated. The syntax is UPDATE(column_name) and will return true if the column has been updated. You can also use COLUMNS_Updated to test multiple columns at the same time.

SQL guy and Houston Magician

Simon Doubt Mr or Mrs. 500 Points: 565 More actions · Answer 6

Robert:

Thanks for the reply. I was aware of both of those functions, and their utility in serving as auditing triggers, but was not sure how to use them to verify whether data had actually been changed, since that was what I was looking for. Anyway, problem solved.

Thanks,

Simon