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 |
|
mwinter
Starting Member
2 Posts |
 Posted - 2005-11-03 : 05:54:38
|
Trying to get my head around this table:I thought it contained all data used in an insert; meaning if i enter a row in a table the inserted table should then contain all data entered?Ive created multiple tables linked via primary key Learner_ID this key is unique to any/all students and can never be duplicated hence i thought i would use it to link the tables.I have tables Fab / Main / Elec / Learner_info / WS_WR.For Fab / Main / Elec the only data i need form Learner_info is the Learner_ID field, i simply used:INSERT INTO FAB(Leaner_ID)SELECT Learner_IDFROM Insertedas a trigger to insert the ID into these tables. Everything works perfectly fine. So i assumed that i would be able to use something very similar to insert say:Learner_ID, FNAME, LNAME, ONAME and C_NUM from the learner_info table into the WR_WS table. Apparently i was wrong  I can insert the learner_ID perfectly but if i then try to add another insert trigger eg: insert fname i get an sql error:Cannot insert the value NULL into the column 'LEARNER_ID', table 'swdt_im.WS_WR'; column does not allow nulls. INSERT fails.Now from reading the error it suggests to me that because the second trigger is running my learner_id value has become a NULL. Obviously a primary key wont accept this value so the statement fails.What im struggling to understand, im sure i will solve the problem eventually but i would apreciate help, and this is most important to me rather than a solution is this:If i enter data into a row on a table does that data go into the inserted table in the same row? or does the data go into inserted as a single entry row, meaning when i tab along from learner_ID into FNAME does Learner_ID become row 1 (in inserted) and FNAME the current row? or do they go into the appropriate columns?I was under the assumption that i could enter:LEARNER_ID / FNAME / LNAME / ONAME / C_NUM into one table and have a trigger insert those values into multiple other tables. Dont suppose someone can help me out  im alright querying a SQL db but ive never had to develop anything other than a single table one myself before  Thanks in advance |
|
|
mwinter
Starting Member
2 Posts |
Posted - 2005-11-03 : 07:01:26
|
| Ok so i created a temp table to checkout how the data gets entered.So for:Learner_ID, FNAME, LNAME i entered the data using the insert from inserted into a new table. Data resembled as follows:NULL, NULL, Last Name,NULL, First Name, NULL,001, NULL, NULL,So now i understand the problem, the solution is quite easy. Insert into ws_wr(LEarner_ID, FNAME, LNAME)select Learner_ID, FNAME, LNAMEfrom insertedVoila |
 |
|
|
|
|
|
|
|
Inserted Table ?