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.
Here is the definition of MyReferenceTable
IF OBJECT_ID('MyReferenceTrigger', 'TR') IS NOT NULL
DROP TRIGGER MyReferenceTrigger
CREATE TRIGGER MyReferenceTrigger ON MyTable
INSERT INTO MyReferenceTable SELECT UniqueIdentifierColumn FROM INSERTED
CREATE TABLE MyReferenceTable
NewAutoSeed int NOT NULL IDENTITY (1, 1),
OriginalUniqueIdentifier uniqueidentifier NOT NULL
) ON [PRIMARY]
ALTER TABLE MyReferenceTable ADD CONSTRAINT
PK_MyReferenceTable PRIMARY KEY CLUSTERED
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]