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.
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
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