Refer to ROWGUID col in update trigger

When writing an Update trigger on a table that has a UniqueIdentifier as the
primary key, how to I refer to the current row being updated.
I am used to using @.@.identity when working with primary keys that are of
type int but a UniqueIdentifier cannot be an identity column. I suspect it
has something to do with the "Is ROWGUID" property but I can't find the
function for getting the GUID of the row being updated.
Thanks,
Andrew.There isn't one. If you need to know this then you should generate the Guid
beforehand and use it in the insert statement.
--
Andrew J. Kelly
SQL Server MVP
"Andrew" <sql@.ses.ca> wrote in message
news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> When writing an Update trigger on a table that has a UniqueIdentifier as
the
> primary key, how to I refer to the current row being updated.
> I am used to using @.@.identity when working with primary keys that are of
> type int but a UniqueIdentifier cannot be an identity column. I suspect
it
> has something to do with the "Is ROWGUID" property but I can't find the
> function for getting the GUID of the row being updated.
> Thanks,
> Andrew.
>|||I want to access the row that is being updated, not inserted. The GUID is
already there.
Does this mean it is not possible to use a trigger to update a
"DateOfLastUpdate" field if the primary key is a GUID? I realize date could
be updated if the update was performed using a stored procedure but I feel
it would be safer to have this functionality on the table itself.
Thanks,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%237WJLGiRDHA.1552@.TK2MSFTNGP10.phx.gbl...
> There isn't one. If you need to know this then you should generate the
Guid
> beforehand and use it in the insert statement.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Andrew" <sql@.ses.ca> wrote in message
> news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> > When writing an Update trigger on a table that has a UniqueIdentifier as
> the
> > primary key, how to I refer to the current row being updated.
> >
> > I am used to using @.@.identity when working with primary keys that are of
> > type int but a UniqueIdentifier cannot be an identity column. I suspect
> it
> > has something to do with the "Is ROWGUID" property but I can't find the
> > function for getting the GUID of the row being updated.
> >
> > Thanks,
> > Andrew.
> >
> >
>|||> You threw me off when you mentioned @.@.IDENTITY. @.@.IDENTITY has nothing to
> do with an UPDATE, it is only useful for Inserts. Any time you want to
> reference a row that is being updated in a trigger you can use the
Inserted
> table. To update a datetime column you would simply do this:
> UPDATE YourTable SET ModDate = GETDATE()
> WHERE PK IN (SELECT i.PK FROM Inserted as i)
> This way it works when more than 1 rows is updated and there is no need to
> know what the actual value of the PK is.
I tend to use a wrapper like:
IF NOT UPDATE(ModDate)
Just to prevent recursion. :-)|||Thanks for your help. I had just "discovered" the inserted table but the
method I came up with is not as clean as yours.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uiAkt8iRDHA.3236@.TK2MSFTNGP10.phx.gbl...
> You threw me off when you mentioned @.@.IDENTITY. @.@.IDENTITY has nothing to
> do with an UPDATE, it is only useful for Inserts. Any time you want to
> reference a row that is being updated in a trigger you can use the
Inserted
> table. To update a datetime column you would simply do this:
> UPDATE YourTable SET ModDate = GETDATE()
> WHERE PK IN (SELECT i.PK FROM Inserted as i)
> This way it works when more than 1 rows is updated and there is no need to
> know what the actual value of the PK is.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Andrew" <sql@.ses.ca> wrote in message
> news:u53MXdiRDHA.2424@.tk2msftngp13.phx.gbl...
> > I want to access the row that is being updated, not inserted. The GUID
is
> > already there.
> >
> > Does this mean it is not possible to use a trigger to update a
> > "DateOfLastUpdate" field if the primary key is a GUID? I realize date
> could
> > be updated if the update was performed using a stored procedure but I
feel
> > it would be safer to have this functionality on the table itself.
> >
> > Thanks,
> > Andrew
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:%237WJLGiRDHA.1552@.TK2MSFTNGP10.phx.gbl...
> > > There isn't one. If you need to know this then you should generate
the
> > Guid
> > > beforehand and use it in the insert statement.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Andrew" <sql@.ses.ca> wrote in message
> > > news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> > > > When writing an Update trigger on a table that has a
UniqueIdentifier
> as
> > > the
> > > > primary key, how to I refer to the current row being updated.
> > > >
> > > > I am used to using @.@.identity when working with primary keys that
are
> of
> > > > type int but a UniqueIdentifier cannot be an identity column. I
> suspect
> > > it
> > > > has something to do with the "Is ROWGUID" property but I can't find
> the
> > > > function for getting the GUID of the row being updated.
> > > >
> > > > Thanks,
> > > > Andrew.
> > > >
> > > >
> > >
> > >
> >
> >
>