[dba-SQLServer]opinions

You can't use @@Identity to return a newly inserted GUID (@@Identity only
works with Integer PKs)

You have to create the new GUID as a variable then insert it, using NEWID():

IF @contactAddrID IS NULL BEGIN
	DECLARE @ctcID AS UNIQUEIDENTIFIER
      SET @ctcID = NEWID()
      INSERT INTO tbl_Contacts (ContactID, ContactName, entryTIME,
entryUSER)
                        VALUES (@ctcID,   @CtcName, getUTCdate() , at UserGuid)
END

SELECT @CtcID




-----Original Message-----
From: dba-sqlserver-admin at databaseadvisors.com
[mailto:dba-sqlserver-admin at databaseadvisors.com]On Behalf Of Arthur
Fuller
Sent: Sunday, March 16, 2003 3:10 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]opinions


What is wrong with SELECT @@Identity?


-----Original Message-----
From: dba-sqlserver-admin at databaseadvisors.com
[mailto:dba-sqlserver-admin at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: March 16, 2003 5:12 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer]opinions


I should just go to bed before I do any real damage. Although, I did sleep
well last night in NyQuil la la land. :)

I did say insert, but that isn't what I meant.

Basically, I'm just trying to return the new identity value after saving a
new record. The publisher seems to think that an INSERT TRIGGER could be
used to grab a new record's GUID, since @@Identity or SCOPE_IDENTIY can't.
But I don't think it's programmatically sound.

I can return the values using other methods -- I was just wondering if
anyone had used INSERT TRIGGER for this purpose.

Susan H.


----- Original Message -----
From: "Arthur Fuller" <artful at rogers.com>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Sunday, March 16, 2003 1:13 PM
Subject: RE: [dba-SQLServer]opinions


> I'm not sure why you would want to do this, Susan, but assuming that
> you want to, a trigger would do it. Do something like this in your
> trigger
> (caution: written in Outlook :-).
>
> DECLARE @myGUID as GUID
> SET @myGUID = NewID()
> -- do something with the new GUID
> ...
>
> Since you could export this logic to the db rather than contain it in
> the FE, I'm not sure why you'd want to do it. This comes from a
> perspective which says, "Anything MS will do for me free is code I
> don't have to
write."
> IOW I'd create a default value for the column (=NewID()) and be done
> with it. The Front End would receive the new value just like an ANPK,
> so why bother writing code to create it?
>
> A.
>
>
> -----Original Message-----
> From: dba-sqlserver-admin at databaseadvisors.com
> [mailto:dba-sqlserver-admin at databaseadvisors.com] On Behalf Of Susan
Harkins
> Sent: March 16, 2003 12:37 PM
> To: SQLList
> Subject: [dba-SQLServer]opinions
>
>
> I'd like some opinions on using an Insert Trigger to insert a GUID
> value
to
> a new record. Good idea or bad?
>
> Susan H.