[sirpelidor]

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: 010120011234

i 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

[dman]

That is correct... trigger.
The language of SQL Server is called "Transact-SQL" or "T-SQL"

syntax is something like

CREATE TRIGGER <trigger_name>

ON <table_name>

FOR INSERT

AS
      <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.

[sirpelidor]

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

[dman]

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 INSERT

AS

   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 field
http://www.informit.com/guides/content.asp...&seqNum=70&rl=1

what polices and guildlines do you have to follow that prohibit correct DB design?

This post has been edited by dman: 13 September 2005 - 01:50 PM

[sirpelidor]

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

[dman]

sirpelidor, on Sep 13 2005, 06:31 PM, said:

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")

This post has been edited by dman: 13 September 2005 - 07:39 PM