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