Post reply

Using TRIGGERs to update data when data is changed.

  • 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

  • January 7, 2007 at 1:31 pm

    #681680

    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.

  • January 7, 2007 at 11:56 pm

    #681711

    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]

  • January 10, 2007 at 8:48 pm

    #682602

    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

  • January 10, 2007 at 8:59 pm

    #682603

    Yup, that should cover it. 

    Thanx for keeping everyone posted.

  • January 10, 2007 at 10:58 pm

    #682611

    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

  • January 11, 2007 at 12:38 am

    #682618

    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

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

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