Why can't I use a trigger to generate a non-int PK? 1

[gaffonso]

I've seen several places now that a trigger cannot be used to generate a non integer Primary Key. I'm good with that but would like to read up on the details behind *why*. A cursory check of Books Online doesn't turn up this limitation. Where is it documented? Is there a discussion I can read about this?

Thanks!

- Gary

 

[tlbroadbent]

I haven't found a reference that says you can't create a non-identity PK in a trigger. I have found plenty of newgroup commentary indicating it isn't possible. So I began to wonder if a primary key could be created in a trigger. The answer is, "Yes!"

Here is a solution that I created this evening after thinking about your question. I was led to this solution after reading an article by Umachandar Jayachandran at SQLMag.com. Read his article at the following link.

http://www.sqlmag.com/Forums/Thread...304458&CFApp=57&Thread_ID=75121#Message196598

His article discusses creation of a non-identity primary key in an INSTEAD OF trigger in SQL 2000. I tested his solution and found that it worked very well but decided to create the same functionality in an AFTER INSERT trigger. The following script resulted. It also works well, though it obviously has had little testing and your requirement is more complex. However, you should be able to use it as a starting point.

BTW: Thanks for the question. I enjoyed the challenge as well as the opportunity to learn something new.

----------------------------------------

--Create table with non-integer PK
--Create default on PK column
CREATE TABLE Products (
ProdID Char(4) PRIMARY KEY DEFAULT 'ABC0',
ProdDesc varchar(30) NULL)
GO

-- Create Insert Trigger to update PK
CREATE TRIGGER trgProdInsert ON Products
FOR INSERT
AS

DECLARE @pk char(4)

--Simple function to create the primary key
SELECT @pk=Left(ProdDesc,3) + Right(prodDesc,1) From inserted

UPDATE Products SET ProdID=@pk
FROM Products p Inner Join inserted i
ON p.ProdID=i.ProdId AND p.ProdDesc=i.ProdDesc
GO

--Insert some records into the table with null PK
INSERT INTO Products ( ProdDesc ) VALUES ( 'AAA Product 1' )
INSERT INTO Products ( ProdDesc ) VALUES ( 'BBB Product 1' )
INSERT INTO Products ( ProdDesc ) VALUES ( 'CCC Product 1' )
INSERT INTO Products ( ProdDesc ) VALUES ( 'BBB Product 2' )
INSERT INTO Products ( ProdDesc ) VALUES ( 'AAA Product 2' )
GO

--Show the results
SELECT * FROM Products
GO

--Cleanup
Drop table Products
GO Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[gaffonso]

A big, big thanks to you!

- Gary