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 |
|
leodesol
Starting Member
9 Posts |
 Posted - 2006-06-01 : 10:40:57
|
| I am new to SQL and these forums, so please bear with me :) My basic question is if I can create a update trigger that will pull info from another record in the same table if data in certain fields match the existing record. An example:The user creates a new record. If said user enters data in specified fields that matches data in the same fields in another record in the same table, can a update trigger be used to fill out the rest of this record with the data from the record that matches?If you need more Info on my problem, ask and I will try to explain better. There may be a better way of doing this than using a trigger, but I am not sure. The fields that I would use to match the data would not be the primary key fields.Thanks! |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-06-01 : 11:00:56
|
| Yes please,Explain a bit more with sample data and expected resultsI think ur Trigger is do-able, but I don't find any rationale in it, as per ur explanation.u want to have 2 records of same data after ur trigger run ?Srinika |
 |
|
|
leodesol
Starting Member
9 Posts |
Posted - 2006-06-01 : 11:17:05
|
| hmm, I will try to explian.I would have:ID Field (PK, Uniqueidnetifier)Parent_Change_Num (Numeric) NullsNS_Change_Num (Numeric) NullsNS_REP (Varchar) NullsNS_Identifier (Varchar) NullsSeveral other fields (Varchar, datetime, and numeric mostly)The trigger would need to be set so that if a user enters a Parent_change_num and NS_Change_Num in a new record that match the Parent_Change_Num and NS_Change_Num of an existing record the rest of the fields, ie NS_REP, NS_Identifier, and the rest, populate from the matching record for the user.Basically, I need to be able to update a record but keep the old info. I thought about using a trigger to move the old info to a seperate history table, but I really need it in the same table. I also only want to to run if certain fields match another record rather than any time an change is made.In the end, I would have 2 records with the much of the same data, but differnt unique IDs and some differences to them. |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-06-01 : 11:34:09
|
| I doubt what you do is good !!Anyhow, here is the approach.Do everything in a test system or backup ur DB before proceding.If u already don't know, learn about triggers.-- Write a trigger to do something like Updating a record-- Improve that to do the same if the Insert record (in trigger language "the table called Inserted") has some value in some field-- Find the Unique ID of the inserted record.-- Find the data corresponding to ur matching -- Update the newly entered record (found by the Unique ID) with needed fields of th "Similar Record" (found by matching the 2 records with matching fields)Srinika |
|
|
|
|
|
|
|
|
Duplicate a record using Update Trigger question