get trigger schema within CLR code

Hi.

I am trying to get the schema in which the trigger is created within the CLR code.

1)create a new schema MySchema.

2) created a table MySchema.MyTable

3) created the assembly and trigger ( create trigger MySchema.MyTrigger on MySchema.MyTable..... ) Trigger writes to another table MySchema.MyLog.

Code works fine if I hardcode Myschema.MyLog in the CLR but fails when I say just MyLog.

So how do dynamically get the trigger's schema name ?

Thanks for your help.

Nach

There is no way to get the schema of the currently executing trigger from clr. With T-SQL, you code would have to create dynamic sql based on the current trigger, or rely on the implict schema name binding that occurs.

There are alternatives
1. Use execute as user and have the specified user have a default schema of MySchema
2. Create a T-SQL trigger that calls your clr trigger converted to a proc, passing it the @.@.ProcId of the T-SQL trigger as a parameter, and you can then get object_name & schema id from the clr procedure

Get trigger information

Hi Freinds,
SQL 2000
I need to find out that if any of triggers on my database has a word
"dup_order" in it
is ther eany schema lime infromation_schema.column_name that I can use to
scan all triggers and find out which one contains the word?
Thanks in advance,
PatYou can search syscomments system table which has the source code for all
the user defined triggers in your database.
Anith|||SELECT OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsTrigger')=1
AND [text] LIKE '%dup[_]error%'
Because triggers that are > 8000 characters will span multiple rows, and
since the text "dup_order" might only appear in such a position that it
straddles rows, a better way might be to have all your triggers scripted out
using Enterprise Manager, and do a search within the output.
I am not sure exactly how SQL Server internally maps a trigger to belong to
a certain table (it may be embedded in the hideous ctext column somehow?).
You can certainly read/parse the ON section of the trigger, and that will
tell you, but doing so programmatically might not be 100% effective.
In SQL Server 2005 we will have more foolproof methods for scanning the
entire definition of an object, rather than deal with this 8000-character
chunk limitation.
"Patrick" <patriarck@.gmail.com> wrote in message
news:uU1sk%230oFHA.3304@.tk2msftngp13.phx.gbl...
> Hi Freinds,
> SQL 2000
> I need to find out that if any of triggers on my database has a word
> "dup_order" in it
> is ther eany schema lime infromation_schema.column_name that I can use to
> scan all triggers and find out which one contains the word?
> Thanks in advance,
> Pat
>|||To overcome the 4000 characters limitation, use the following procedure
to search for a string in all views, procedure, triggers and UDF-s:
CREATE PROCEDURE sp_findtext(@.Text nvarchar(256))
AS
SET @.Text=Replace(Replace(Replace(@.Text,
'[', '[[]'), '%', '[%]'), '_', '[_]')
SELECT name, xtype FROM (
SELECT id FROM syscomments WHERE text LIKE '%'+@.Text+'%'
UNION
SELECT c1.id FROM syscomments c1
INNER JOIN syscomments c2 ON c1.id=c2.id AND c1.colid=c2.colid-1
WHERE SUBSTRING(c1.text,3001,1000)+LEFT(c2.text,1000)
LIKE '%'+@.Text+'%'
) U INNER JOIN sysobjects o ON u.id=o.id
ORDER BY xtype, name

> I am not sure exactly how SQL Server internally maps
> a trigger to belong to a certain table
See the parent_obj column in the sysobjects table.
Razvan

Get the description of INFORMATION_SCHEMA.COLUMNS

I understand that sp_help and sp_columns can give a description of a table
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?
USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
David Portas
SQL Server MVP

Get the description of INFORMATION_SCHEMA.COLUMNS

I understand that sp_help and sp_columns can give a description of a table
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?
USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
David Portas
SQL Server MVP

Get the description of INFORMATION_SCHEMA.COLUMNS

I understand that sp_help and sp_columns can give a description of a table
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
--
David Portas
SQL Server MVP
--

Get the description of INFORMATION_SCHEMA.COLUMNS

I understand that sp_help and sp_columns can give a description of a table
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
David Portas
SQL Server MVP
--