Software

Ask something
Discussion Locked

Trigger output

0 helpful
3 replies

by thomas.bihn

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I'd like to use a trigger to identify the GUID that was assigned to a record just inserted. Here is the TRIGGER statement:

CREATE TRIGGER trigBoardsGUID
ON [EngineeringUser].[Boards]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON

SELECT Board_GID AS '@@GUID'
FROM inserted
END

How do I access '@@GUID' from my stored procedure that inserts into Boards?

I was using an ID field (int identity) but need to synchronize this table with another database server. I'm using Scope_Identity() to determine the last record ID added, but need to do this method to determine the last record GUID.

Thanks

3 replies

by tony hopkinson

but BOARD_GID is what is going to be put in Board_GID by your stored procedure, insert whatever.
If you want it in a variable SQL server syntax would be
Declare @LastGUID UniqueIdentifier (? whatever the datatype is in the table anyay)
Select @LastGUID = Board_GID From Inserted

or thereabouts anyway

by thomas.bihn

Thanks for the reply Tony. I'm looking to mimic how Scope_Identity determines the last identity value used in a table. By having the SELECT statement in the ON INSERT trigger, once I figure out how to read the output value, I'll be able to discern the last GUID used in a particular table. This will allow me to modify existing tables from Identity PKs to GUID PKs with less overall rewrite to stored procedures and outside inserts into the table. Because I was using the Identity, I have many cases in client-side code where I'm inserting into a table without referencing the Identity field. If I don't have the GUID automatically generated, I will have to go back to every insert I have anywhere into the table and add the field.

The reason I'm changing the table PKs to GUIDs is because the database will need to be synchronized between multiple facilities and from my understanding, I'll lose records with duplicate PKs (as would occur if both master and remote databases incremented the identity between syncs) from the remote databases. If anyone knows of a way around using a GUID and continuing the use of the "autonumber" identity, that would be even more valuable.

Thanks again for the reply, and thanks in advance for more assistance on this. I really appreciate it.

Tom

by tony hopkinson

If this is SQL Server any insert with an identity field no matter what the type will leave the inserted value in a global variable @@IDENTITY.
Be careful with this though as it will be overwritten by the next insert to a table with an identity column.
Insert into MyTable ....
Select @@Identity

inside a transaction will get it safely. ****
GUID will give you a unique ID across databases where say an integer wouldn't.

Just remeber to get it straight after the insert and
*****
don't put in a trigger to insert to another identity keyed table, or you'll get it's identity value instead.

One of the places Oracle wins hands down with sequences this, but with a care and keeping in mind you are using a global variable that can be set anywhere at anytime, the solution can be workable.

This discussion is closed to new replies.