Hi Guys,

I am not sure weather if i am in the right track. First of all i am using DreamweaverMX and the Application server is ASP with MS Sql2000 as the DB.
I am not a developer but i can fiddle around and done some lite programming in Php as well as in Asp with the aid of Dreamweaver.

Right now i am stuck with a situation don’t know how to proceed, wondering weather if it is the right approach.

I developing an application that has about 20 tables, some of them are so called lookup tables that you enter the information first in order to relate that to another table and so on.

The trouble is i've done very well in inserting and updating deleting etc. Now i am stuck wondering how to go about.

I have User_table, and a transaction_table; what i wish to do is when a new user is created which is done by inserting a new row in the User_table, i need at the same time pick that new row's user_id which is auto_increment that was just created and insert it in the field situated in the transaction_table (user_id as FK) and so on. I just realized that i will be needing this technique for other situation in the other tables.

I also realized that the id should be the one just inserted and not any body else's id that was inserted just after me.

Looking through different forums i found solutions like @@IDENTITY, select max (user_id) through procedure etc but unfortunately i am stuck so as to how i'll implement these solution.

I'd appreciate if some one could help me guide through the procedure or some solution or example where i could take a look or even any tutorial etc. i am opened to all proposition.

Thanks in advance.

Gecko

The best, and most reliable, way to accomplish this is through a SQL Trigger. A SQL Trigger is bound to an action on a table. So if you were to INSERT a new User into your Users table, and you had a trigger created on the INSERT action of the Users table, it would automatically execute the SQL defined in the trigger, which could replicate the user's information into your secondary "transaction" table.

The benefit of this method is that you now ensure referential integrity down at the database and not in your ASP. If you do this same thing in ASP, I could go into the database and add a user without updating the transaction table. By adding a trigger to the table, even if I go into the DB directly and add a user, that trigger will still fire and update the transaction table.

Hi thanks for that quick advice, its lot clearer for me now.
After having passed weeks over this, thinking weather I’ve got the db design correct so as to insert the new id after having just inserted a new row one. So I guess this is how it’s happening in the real world of relational DB.
Now all I need to now is to figure out how to implement this trigger through dreamweaver or by doing some hand coding.

So thanks Seppuku for the tip.
I was bit skeptic when I took this project to work on Asp thinking of where to find help when stuck, because when I was helping a friend on his site on Php I was using the Devshed [ http://www.devshed.com ]forum for all my questions and there was always some to help you,i would recomend it for Php and Mysql.
Now I guess I found a superb forum for Asp and MS Sql.
I shall post here if I have any questions or doubts.

Thanks

Gecko

No problem.. you may also want to look into Stored Procedures. Some conversations with others here have lead me to believe there are times and places to use both, so weigh the pros and cons of each.