Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    41

    Unanswered: changing one field updates 3 other fields, use a join?

    i currently have a table like this..

    Code:
    user_id   username   app_id   app2_id   app3_id   app4_id
    1           john        3         4            5           6
    2           mike        4         5            6           6
    3           manager     4         5            6           6
    4           vicepres    5         6            6           6
    5           ceo         6         6             6          6
    6           board       6         6            6           6
    the basic pattern is... a user has approvers, and those approvers have approvers as well... i have 4 columns of approvers.. and if my first approver is the manager, then my second approver will be the managers approver and my third approver will be the managers approver's approver and so on..

    on my actual page, i have select fields for the app, app2, app3, app4 and i need it so that when i change the very first app, it'll automatically update app2, app3, and app4

    any ideas how i would do this? im pretty new to sql but im thinking i would use some type of join?

    thanks

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Look into triggers. You'd make an UPDATE trigger that executes automatically when an update occurs on the table. You can then check to see if the update is to app_id, and make changes to the other fields accordingly.

    There are two types of triggers, INSTEAD OF and AFTER. You need to determine if you want to replace the original UPDATE with some SQL you want the trigger to execute, or if you want the trigger to execute after the original UPDATE occurrs.
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Dec 2003
    Posts
    41
    Quote Originally Posted by Seppuku
    Look into triggers. You'd make an UPDATE trigger that executes automatically when an update occurs on the table. You can then check to see if the update is to app_id, and make changes to the other fields accordingly.

    There are two types of triggers, INSTEAD OF and AFTER. You need to determine if you want to replace the original UPDATE with some SQL you want the trigger to execute, or if you want the trigger to execute after the original UPDATE occurrs.
    im pretty new to sql, im reading up on triggers and examples of them right now.. they show some code for the triggers.. would that whole code be the entire SQL query or would i code that in perl ( what im using that to access the SQL database)?

    thanks

  4. #4
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    You actually store triggers at the table level of the DB. In Enterprise Manager, drill down into your DB, and down to your table. Then right click on the table you wish to apply a trigger to and select "All Tasks" then "Manage Triggers".
    That which does not kill me postpones the inevitable.

  5. #5
    Join Date
    Dec 2003
    Posts
    41
    Quote Originally Posted by Seppuku
    You actually store triggers at the table level of the DB. In Enterprise Manager, drill down into your DB, and down to your table. Then right click on the table you wish to apply a trigger to and select "All Tasks" then "Manage Triggers".
    ahh ok, found it, thanks

  6. #6
    Join Date
    Dec 2003
    Posts
    41
    ok i got it working.... so now, if i update an approver for a specific user, the trigger will run and go ahead and update the app2, app3, and app4 so it does what it's suppose to.

    so i still have my original table as above, but now i added this trigger to the table:
    Code:
    CREATE TRIGGER approvers ON users FOR UPDATE AS
        IF UPDATE(approver_id)
        BEGIN
            DECLARE @thisUser VARCHAR(10)
            DECLARE @newApp VARCHAR(10)
            DECLARE @newApp2 VARCHAR(10)
            DECLARE @newApp3 VARCHAR(10)
            DECLARE @newApp4 VARCHAR(10)
    
            SELECT @thisUser = (SELECT user_id FROM Inserted )
            SELECT @newApp = (SELECT approver_id FROM Inserted)
            SELECT @newApp2 = (SELECT approver_id from users WHERE user_id = @newApp)
            SELECT @newApp3 = (SELECT approver_id from users WHERE user_id = @newApp2)
            SELECT @newApp4 = (SELECT approver_id from users WHERE user_id = @newApp3)
    
            UPDATE users SET app2_id = @newApp2 WHERE user_id = @thisUser
            UPDATE users SET app3_id = @newApp3 WHERE user_id = @thisUser
            UPDATE users SET app4_id = @newApp4 WHERE user_id = @thisUser
        END
    but i just thought of a problem, say i update the approver of an approver, i.e. i update app_id of manager and change it from vice pres to the ceo, so i change app_id for the manager from 4 to 5.. then all the rows that had the manager as an app, app_2, app_3, or app_4 would need to be updated to since the managers approve is no longer the same person, the other peoples app2, wouldnt be the same and therefore their app3, and app4 wont be the same either .. i hope someone understand what im saying... how should i go about fixing this? like if i change the approver of manager, would i have to return all the rows that had manager as an app, app_2, app_3, or app_4 and then update all of those rows somehow? like looping through each of the rows returned?

    im really new to sql, i hope someone can guide me through this

    thanks

  7. #7
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Look into "Cascading Triggers". That may help you out.
    That which does not kill me postpones the inevitable.

  8. #8
    Join Date
    Dec 2003
    Posts
    41
    got it working now, thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •