Triggers and updates for a field

[bobpv]

I am new to triggers, but have found a need to create one. I am spinning round in circles, and may have missed the solution! Here is what I am doing:

SQL Server 2000.
I have a data base that stores job and cutomer information. I have a trigger to update a new table with specific information from the main table. What I need to do is update the ID field whenever a new record is inserted, with a particular phrase in the ID field (i am using the string 'Yes' for testing). I am able to get only those records that macth the 'Yes' to insert. NOw is where I get stuck.

I need to assign a starting value to the ID field which will be for the update trigger. This field will have an old value (say 4000) which gets incremented by 1 for every inserted new record. BUT! The id's are customer specific, they must link on the customer number field. So in the DB, I can have customer 100 using 4001,4002 4003 and so on. At the same time, customer number 200 can be using 4002,4003,4004 and so on.

I hope this all makes sense. I am just missing something here, and would appreciate any and all advice on how to accomplish my goals.

Bob
Robert Pevey and Associates

http://www.peveyweb.com

 

[donutman]

No it doesn't make a whole lot of sense. Some examples would help immensely.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]