Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Question Unanswered: Use Trigger to generate primary key

    I have create a recursive triggers in tblIncident. The PKID is the primary key in tblIncident, in this trigger, i'm trying to generate an auto increament primary key from the stored procedure GetMaxId and update to tblINcident, but I face a problem where PKID does not refresh the latest PKID, it always show the default value 0, until I requery the table. How to get the latest PKID?

    /* Trigger in tblIncident */
    CREATE TRIGGER GetPKID
    ON tblIncident FOR
    INSERT AS
    DECLARE @PKID int
    DECLARE @NEWVALUE int
    DECLARE PKID_Cursor CURSOR FOR SELECT tblIncident.PKID FROM tblIncident, inserted
    where tblIncident.PKID = Inserted.PKID
    OPEN PKID_Cursor
    BEGIN
    FETCH NEXT FROM PKID_Cursor INTO @PKID

    WHILE (@@fetch_status = 0)
    BEGIN
    SET @NEWVALUE = 0
    /*Call stored procedure - getmaxid to get the latest PKID */
    EXECUTE GetMaxId "IN", @NEWVALUE OUTPUT
    Update tblIncident SET PKID = @NEWVALUE WHERE PKID = @PKID

    FETCH NEXT FROM PKID_Cursor INTO @PKID
    END
    END

    CLOSE PKID_Cursor
    DEALLOCATE PKID_Cursor

  2. #2
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    why don't u use Identity columns or Unique Identifiers...
    If u don't know what they are, read up in Books Online...
    unless u'r trying to achive something else
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  3. #3
    Join Date
    Jan 2004
    Posts
    5
    Thanks for the reply.

    Yes, I have to read through and understand on the identity field, but Im trying to achieve something by using this method.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What are you trying to achieve?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2004
    Posts
    5
    I'm trying to take control on the primary key value.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    To set it to what? What is your primary key strategy?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •