Is there an "ident_current"-function for an update-statement ?

[psycho24]

For developing an update-trigger i need a function to get the recordkey (value of the primary key from the trigger table) of the updated record. I know that there are two functions to get the recordkey of an inserted record (@@identity or ident_current). I need this function for an updated record.
So i tried to find another solution for my problem. I want to find out all the column(s) consists to the primary key of a table. In the next step i would get the recordkey of the updated table by selecting all primary-key-fields from the "inserted"-table (i mean inserted-table from update trigger).
But I cant get the field(s) consists to the primary key. I hoped that information are coded in dbo.sysindexes.keys but can't read out the field of type varbinary.

Thanks for your help!

Guido Greschuchna
Düsseldorf, Germany

 

[SQLSister]

why are you not just using
select {PrimaryKeyFieldName] from inserted
to get the primary key value? If it is a primary key is it already in the inserted and deleted psuedotables inthe trigger.

 

[psycho24]

It's not a solution for me to select directly all fields from the primary key because i have written an vb-procedure that automaticly generates triggers for a given table.

This is the head of the procedure:
----------------------------------
Public Sub GenerateLogTableTrigger(tablename as string, triggertype as string)

<tablename> is the name of one table from the database
<triggertype> is &quot;insert&quot; or &quot;update&quot; or &quot;delete&quot;

The purpose of the triggers:
----------------------------
The intention is to log all data-inserts and data-modifications into one log-table for more than one table.

This is the structure of the log-table:
---------------------------------------
Columns: log_key, tablename, fieldname, recordkey, fieldvalue, log_date, username, triggertype

You see I realy need to get all columns consists to the primary key of an undefined table.


Guido Greschuchna
Düsseldorf, Germany

 

[psycho24]

I found the solution (here an example for table &quot;addresses&quot;:
------------------------------------------------------------

declare @objname nvarchar(100)
declare @objid int /* object-id of table */
declare @indid int /* index id */
declare @keys nvarchar(255) /* the index key(s) */
declare @i int
declare @thiskey nvarchar(255)

set @objname = 'addresses'
set @objid = object_id(@objname)
set @i = 1
set @keys = ''

select @indid = min(indid) from sysindexes where id = @objid and indid > 1

while @i <= 16
begin
set @keys = @keys + ' ' + isnull(index_col(@objname, @indid, @i),'')
set @i = @i + 1
end

select @keys

 

[psprague]

a cleaner solution is:

SELECT c.name AS pkColumnName
FROM syscolumns c
INNER JOIN sysindexkeys sik ON sik.id = c.id AND sik.colid = c.colid
INNER JOIN sysindexes si ON si.indid = sik.indid and si.id = c.id
WHERE (si.indid BETWEEN 1 And 254) AND (si.status & 2048) = 2048
AND c.id = OBJECT_ID('mytable')

 

[psycho24]

This is realy a better solution!
Big Thx!

Guido Greschuchna
Düsseldorf, Germany