Trigger for generating Primary Key?

[gaffonso]

I've fallen into the habit of assigning Surrogate (artificial) keys when doing my relational designs. I've got a client who needs a database with an "Intelligent" primary key on one of the tables. More specifically he would like the Primary Key to be a "Product Number" that follows their businesses internal specifications.

I was assuming I would use a trigger to generate new Product Numbers but I have read here that triggers cannot be used to generate Primary Keys. I am not finding any reference to this limition in the Books Online, can someone please confirm and point me to a reference?

In general, am I cracking this nut the right way? Are triggers the best way to go to create "intelligent" primary keys that require some business logic in their generation?

Thanks!

- Gary

 

[slimr]

If you need to generate a non integer primary key then no to triggers. Question is what does the primary key look like an how will you keep track of it
Slim

 

[tlbroadbent]

I recommend using a Stored Procedure. The application could pass data to the SP as parameters. The SP would create the Primary Key(s) or Product Number(s) according to specifications and then insert record(s) into the tables(s).

After all, a trigger is just a specialized stored procedure. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[fluteplr]

I would also have a long talk with the customer about this.

Are there ANY circumstances where the key would change for an item? If so this is probably a bad idea.

When confronted with this in the past, I have generally tried to convince the customer to use a artificial key as the real key and set up their key as a unique constraint. Most of the time I win this one and it save huge amounts of problems later when the answer to the question above turned out to be yes. One other issue, frequently these customer sugested keys are very large, which can be a performance problem if the tables are large and it is used for a lot of relations with other tables.

 

[gaffonso]

Thanks for the feedback.

Could someone please point me to the documentation describing the limitation of triggers and non integer primary keys?

Terry recommended using a stored procedure. This makes perfect sense but it would eliminate the automatic checks when directly modifying the base tables with INSERT and UPDATE statements, no? I fear that I don't have "full control" over the interaction with the database, the client is fairly sophisticated and will likely query tables directly. I'm looking for a solution that provides key-generation in such an instance. Am I out of luck?

Slim asked:
"Question is what does the primary key look like an how will you keep track of it?"

The business rules for the key are:

* The first letter is J, P, R or 0 (zero) where J = Job, P = proposal, R = R&D, and 0 is reserved for ongoing internal hours tracking.
* J, P & R coded jobs are then followed with a two digit year code, a 3 digit project identifier, and finally a two digit sub-project identifier.

* I do not yet have the details on the project and subproject identifiers but I am assuming that the project identifier is an incremented integer. Not sure yet about the sub identifier.

For example, J98071.00. They are stored without the decimal point.

I completely agree that *not* using these as primary keys is the best solution (but still keeping them in a separate UNIQUE column as suggested) but if I cannot convince the client of that I need some other options.

Thanks for your help!!

- Gary