sirpelidor Posted September 12, 2005 Share Posted September 12, 2005 Howdy, i hope i came to the right forum since I don't know where to seek help I made a table within sql server, there's a field call "linkID", and basically what i want it to do is automatically filled with some kind of value everytime when that row is inserted.for example, whenever a row is inserted, i want to take the birthdate field from the same table- e.g. 01/01/2001 mix with last 4 digit from ssn field at the same table - e.g. 1234. and concatenates them together into 1 string -e.g: 010120011234i dunno much about sql server, but after done some reading, i think they call that "trigger". But i don't know what language and how is used to program dbms (part of the major problem i don't know how to solve is to deal with leading zero from the date field).I know I can accomplish the smiliar task from the software programming level (e.g. if writing program in java, concatenate the string first, and insert that row alone with sql statment), but the problem with that is, i have to write that code everytime when i create a application that is talking to the same table. So it'll be nice if I can handle that at a dbms level.thank you for your time Link to comment Share on other sites More sharing options...
dman Posted September 12, 2005 Share Posted September 12, 2005 That is correct... trigger.The language of SQL Server is called "Transact-SQL" or "T-SQL"syntax is something likeCREATE TRIGGER <trigger_name>ON <table_name>FOR INSERTAS <tsql code here> but if you are just trying to make a link for the table you should use a "dumb" primary key, meaning it is only unique identifier, not related to row data in any way. Then you reference this as foreign key in child table. I will elaborate if you are interested. Link to comment Share on other sites More sharing options...
sirpelidor Posted September 13, 2005 Author Share Posted September 13, 2005 Hi dman, Thank you for ur reply. I understand mixing ssn+bday as for primary key field is bad practice. In fact i do have auto signed primary key field in each row.However, I'm unable to go with your suggestion (using dumb key field) due to the polices and guildlines which i have to follow.Would you mind show me how you would done those task in T-sql, or would you please show me where can I find some on-line books/ references regard to how to program sql server (I think knowing how to program dbms is a good skill to obtain)?Thank you Link to comment Share on other sites More sharing options...
dman Posted September 13, 2005 Share Posted September 13, 2005 (edited) You are right, good skill to have. Everything is in a DB these days and all RDBMS work pretty much the same, so anything you learn is fairly generic knowledge.CREATE TRIGGER <trigger_name>ON <table_name>FOR INSERTAS DECLARE @pk int DECLARE @link varchar(20) SELECT @pk = (SELECT pk_id FROM Inserted) SELECT @link = (select substring(birthdate,1,2) + substring(birthdate,4,2) + substring(birthdate,7,2) + right(ssn,4) FROM Inserted) UPDATE <table_name> SET linkid = @link WHERE pk_id = @pk ok, I am rusty at SQL Server, I have been using mySQL for a while, so this may not be quite right, but should give you the idea. Basically the "As" part will execute whenever a row is inserted. the "Inserted" field is special temp table sql server makes with fields from last insert.replace "pk_id" with your primary key fieldhttp://www.informit.com/guides/content.asp...&seqNum=70&rl=1what polices and guildlines do you have to follow that prohibit correct DB design? Edited September 13, 2005 by dman Link to comment Share on other sites More sharing options...
sirpelidor Posted September 13, 2005 Author Share Posted September 13, 2005 thanks dman,with a little modification, ur code works very well.case close.p.s: i perfer not to get into detail, but when then people above you make certain decision (regardness good db practice or not) its kindda hard to go against it heheheh Link to comment Share on other sites More sharing options...
dman Posted September 13, 2005 Share Posted September 13, 2005 (edited) thanks dman,with a little modification, ur code works very well.case close.p.s: i perfer not to get into detail, but when then people above you make certain decision (regardness good db practice or not) its kindda hard to go against it heheheh<{POST_SNAPBACK}>Cool.I know what you mean about project managers. I once had one that would not allow me to use SQL syntax with Visual Foxpro because XBase didn't have it, so I didn't need it. (read, "he didn't know it") Edited September 14, 2005 by dman Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now