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.

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

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.

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.

Well then I would agree with you there lol.