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

i currently have a table like this..

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?

thanksLook 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.|||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|||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".|||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|||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:

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|||Look into "Cascading Triggers". That may help you out.|||got it working now, thanks :)