Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
olud
Starting Member
6 Posts |
 Posted - 2004-08-27 : 05:00:38
|
| Hi All..I have 2 tables..table1 hasthe following columns - userid, name , addressTable2 - userid,password.I am looking for a way that when a record in the userid column is added, deleted or updated on table1, the system automatically does the same to the userid column on table2. can this be done?thanks all |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-27 : 05:22:10
|
| this can be done with trigger on table1. look them up in Books online (sql help)Go with the flow & have fun! Else fight the flow :) |
 |
|
|
portman
Starting Member
11 Posts |
Posted - 2004-08-27 : 10:18:18
|
This is exactly why application developers say "no inline SQL!". It is a good habit to create stored procedures for every INSERT, UPDATE, and DELETE operation on a table. That way you can embed logic like what you are talking about. So, for instance:CREATE PROCEDURE InsertUser (@UserID INT) AS INSERT INTO table1 (userid) VALUES (@UserID) INSERT INTO table2 (userid) VALUES (@UserID)GOCREATE PROCEDURE UpdateUser (@OldUserID, @NewUserID) AS UPDATE table1 SET userid = @NewUserID WHERE userid = @OldUserID UPDATE table2 SET userid = @NewUserID WHERE userid = @OldUserIDGOCREATE PROCEDURE DeleteUser (@UserID) AS DELETE table1 WHERE userid = @UserID DELETE table2 WHERE userid = @UserIDGO Sprocs have many other advantages, such as the ability to restrict permissions for various users (GRANT EXECUTE ON [sprocname] TO [sqluser]). Note that the sprocs above are just starters; you'll want to add error handling, perhaps transactions, perhaps output. |
|
|
|
|
|
|
Insert/update table from another...pls help!!!