Get the uniqueidentifier back that was inserted into a table from a trigger

I noticed something strange in a trigger the other day…it had a reference to $rowguid; the reason it was strange to me was because I 1) know to never, ever use a uniqueidentifier on any of my tables,  2) know to never, ever put a trigger on a table, so therefore 3) never heard of the keyword. Well, maybe a trigger on occasion, but very rarely. Triggers are seldom needed, and if you’re on a team building a custom app, the business objects folks should be doing any triggering action in the middle layer, and not in the database in my opinion. Now, I’m not saying here that all stored procedures and logic need to be removed from the data layer. No sir. But that’s for another blog and discussion somewhere else down the line. What I am saying here is that I’ve been the data dude on custom app-dev projects over the years, and I tend to stay away from triggers if at all possible. Just my opinion of course, you use them as you see fit.

In order to get the return GUID or uniqueidentifier that was just inserted into a table from a trigger, run the following example:

USE tempdb

DROP TABLE triggerTest

GO

CREATE TABLE triggerTest

    (ID INT

    ,UniqueIdent uniqueidentifier ROWGUIDCOL DEFAULT NEWID()

    )

GO

CREATE TABLE logTable

    (id INT IDENTITY

    ,TableName VARCHAR (100)

    ,UniqueIdent UNIQUEIDENTIFIER

    ,Date datetime DEFAULT GETDATE()

    )

GO

Notice the ROWGUIDCOL – that is a necessary attribute to get the return uniqueidentifier back from the trigger. Check out Books Online for further reading on this.

Here’s the trigger example for this table:

CREATE TRIGGER [dbo].triggerTestInsert 

ON dbo.triggerTest

WITH EXECUTE AS 

CALLER AFTER INSERT 

AS

SET NOCOUNT ON

BEGIN

    INSERT dbo.logTable SELECT  'triggerTest', $rowguid, GETDATE() FROM inserted    

END

GO

Insert a record and you will get the guid back from it.  Again, check the insert and notice now that I am inserting a $rowguid value into the table; this represents the GUID that was inserted by the NEWID() function.

INSERT triggerTest (ID) VALUES (1)

GO

SELECT *

FROM logTable

GO

Looks pretty good. So in order to get a return value for the uniqueidentifier that was just inserted, as you would with SCOPE_IDENTITY() for IDENTITY, for instance, use the ROWGUIDCOL along with $rowguid to fetch the guid.

Thanks for reading,
Lee

----------------------