SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,159
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)

    Trigger After Insert with a GUID Primary Key

    Okay, so I'm a bit stumped.

    I am trying to create a trigger on an already defined table that uses a GUID (uniqueidentifier) for a primary key.

    I need to take the generated GUID and associated it to an INTEGER (which is an identity seed column).

    I now have the following, I just want to make sure it is appropriate and that there isn't a better way. I can't redesign the original table (trust me, I tried!), so I have to work with what is there. The below seems to work.

    Code:
    USE GainWeb;
    GO
    IF OBJECT_ID('MyReferenceTrigger', 'TR') IS NOT NULL
    	DROP TRIGGER MyReferenceTrigger
    GO
    
    CREATE TRIGGER MyReferenceTrigger ON MyTable
    AFTER INSERT
    AS 
    	INSERT INTO MyReferenceTable SELECT UniqueIdentifierColumn FROM INSERTED
    GO
    Here is the definition of MyReferenceTable
    Code:
    CREATE TABLE MyReferenceTable
    	(
    	NewAutoSeed int NOT NULL IDENTITY (1, 1),
    	OriginalUniqueIdentifier uniqueidentifier NOT NULL
    	)  ON [PRIMARY]
    GO
    ALTER TABLE MyReferenceTable ADD CONSTRAINT
    	PK_MyReferenceTable PRIMARY KEY CLUSTERED 
    	(
    	NewAutoSeed 
    	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    
    GO

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    So I don't have that much experience with triggers, though I'm a little curious why you are not placing this logic into your front end app? This looks like MSSQL, but I know that MySQL can have issues with triggers when it comes to replication.

  3. #3
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,159
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    It is MSSQL, but the reason being, I didn't want to have to alter the code and find all code paths that insert into this table. A trigger seemed to be the best bet that would resolve this particular scenario regardless of how the data gets inserted.

    And I was given a SQL Database, and told, here is where the data is stored that you need to use with this existing service, but we won't tell you how its populated/used. So since I have a black box of mystery, I figured, play it safe, use a trigger.

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Well then I would agree with you there lol.


Bookmarks

Posting Permissions

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