how to get tablename in CLR-Trigger?

Hi,
i want to write an 'universal' audit trigger in C# or VB. For that i need
the tablename. But i found nothing in SqlContext.TriggerContext.
How can i get this tablename?
thanks,
HelmutHelmut
Table name of what? of trigger create on this table?
CREATE TABLE TT
(
COL INT
)
CREATE TRIGGER MY_TR ON TT
FOR INSERT
AS
DECLARE @.ObjID int
SET @.ObjID = (SELECT parent_obj FROM sysobjects WHERE id = @.@.PROCID)
SELECT OBJECT_NAME(@.ObjID) AS 'Parent Table'
INSERT INTO TT VALUES (1)
SELECT * FROM TT
DROP TABLE TT
"Helmut Woess" <user22@.inode.at> wrote in message
news:9serddu999is$.evazv4rhvdim.dlg@.40tude.net...
> Hi,
> i want to write an 'universal' audit trigger in C# or VB. For that i need
> the tablename. But i found nothing in SqlContext.TriggerContext.
> How can i get this tablename?
> thanks,
> Helmut|||Uri,
i need this in an assembly, not in TSQL. So @.@.PROCID is not available.
thanks, Helmut|||On Fri, 26 May 2006 11:06:36 +0200, Helmut Woess wrote:

>Hi,
>i want to write an 'universal' audit trigger in C# or VB. For that i need
>the tablename. But i found nothing in SqlContext.TriggerContext.
>How can i get this tablename?
Hi Helmut,
As far as I know, you have to specify a single tablename in the Target
property of the SqlTrigger attribute - so that rules out your idea of a
universal trigger. You'll have to use one per table.
Hugo Kornelis, SQL Server MVP|||Am Sat, 27 May 2006 01:47:20 +0200 schrieb Hugo Kornelis:

> As far as I know, you have to specify a single tablename in the Target
> property of the SqlTrigger attribute - so that rules out your idea of a
> universal trigger. You'll have to use one per table.
Yes, you are right, but in the moment it isn't necessary to set this
properties and i can use one assembly for many triggers. I hope this will
be possible in future versions of SqlServer too, i don't see any advantage
in the target properties. Can you call me one?
Helmut|||On Sat, 27 May 2006 11:33:45 +0200, Helmut Woess wrote:
(snip)
> i don't see any advantage
>in the target properties. Can you call me one?
Hi Helmut,
Sorry for the delayed reply - I haven't had much chance to play with CLR
triggers before now.
Earlier today, I stumbled over a web page stating that the target
properties for a CLR trigger are only used to aid Visual Studio when you
use the Deploy Project menu option. If you deploy manually, they have no
effect. Unfortunately, I didn't save the URL.
Hugo Kornelis, SQL Server MVP

How to get Primary Key (Columns) of a Table?

I want to get the Primary Key Columns in Arrays by sending a table
name. I am using SQL Server 2000 and I want to make a find utility in VB.net which
will work for all the forms; I have tables with one Primary key and some tables with composite Primary keys.

I used to do this in VB 6 by making a function which fills the Primary Keys in
List Box (I require to fill in list box), now I need to get in array.

Can some one tell me the migration of the following VB 6 Code?

This was written for the MS Access, I need same for SQL Server, I can
not find Table Def and Index Object in VB.net 2003.

Public Sub GetFieldsFromDatabase (ldbDatabase As Database, lsTableName As
String)

Dim lttabDef As TableDef
Dim liCounter As Integer
Dim liLoop As Integer
Dim idxLoop As Index
Dim fldLoop As Field

With ldbDatabase
For Each lttabDef In .TableDefs
If lttabDef.Name = lsTableName Then
liCounter = lttabDef.Fields.Count
For liLoop = 0 To liCounter - 1
cboFieldLists.List(liLoop) = lttabDef.Fields(liLoop).Name
Next liLoop
For Each idxLoop In lttabDef.Indexes
With idxLoop
lblIndexName = .Name
If .Primary Then
liCounter = 0
For Each fldLoop In .Fields
cboPrimaryKeys.List(liCounter) = fldLoop.Name
liCounter = liCounter + 1
Next fldLoop
End If
End With
Next
cboFieldLists.ListIndex = 0
If cboPrimaryKeys.ListCount > 0 Then
cboPrimaryKeys.ListIndex = 0
End If
Exit For
End If
Next
End With
End Sub

SQL Server has a built-in system stored procedure calledsp_primarykeys whichwill return you the primary key information for the table you specify.

USE master
EXEC sp_primarykeys @.table_server = N'LONDON1',
@.table_name = N'Customers',
@.table_catalog = N'Northwind',
@.table_schema = N'dbo'

|||Thanks Tmorton