CLR SqlTrigger and Schemas

I cannot get a Trigger to work on a schema table set that is not dbo. For
example with AdventureWorks if I used:
<Microsoft.SqlServer.Server.SqlTrigger(Name:="utrigPersonas",
Target:="Person.Address", Event:="FOR UPDATE")>
When I try to deploy this it returns the error:
Cannot find the object "Persona.Address" because it does not exist or you do
not have permissions.
Can you not create CLR triggers with tables that use schemas other than dbo?You can, but Visual Studio can't -- deploy it yourself (use CREATE ASSEMBLY,
then CREATE TRIGGER) and you should have no problems.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"developer@.stf.com" <developer@.stf.com@.discussions.microsoft.com> wrote in
message news:8F71BCCD-3F05-4CAE-9772-B8483658910A@.microsoft.com...
>I cannot get a Trigger to work on a schema table set that is not dbo. For
> example with AdventureWorks if I used:
> <Microsoft.SqlServer.Server.SqlTrigger(Name:="utrigPersonas",
> Target:="Person.Address", Event:="FOR UPDATE")>
> When I try to deploy this it returns the error:
> Cannot find the object "Persona.Address" because it does not exist or you
> do
> not have permissions.
> Can you not create CLR triggers with tables that use schemas other than
> dbo?

CLR DDL Triggers with SqlTrigger Attribute?

Does anyone have an example of using the SqlTrigger attribute for a DDL trigger? Cannot seem to locate one?What do you want to do ?

Jens Suessmeyer.

http://www.sqlserver2005.de
|||

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
// what values do you plug into these parms?
// [Microsoft.SqlServer.Server.SqlTrigger (Name="Trigger1", Target="Table1", Event="FOR UPDATE")]
public static void Trigger1()
{
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
}
}

|||

for example...

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
[Microsoft.SqlServer.Server.SqlTrigger (Name="Trigger1", Target="DATABASE", Event="CREATE_ASSEMBLY")]
public static void Trigger1()
{
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
}
}

i can build this assembly and even deploy it via VS but no trigger exists with the name of Trigger1? What gives, tell me what I am doing stupid here guys!

|||further more there are no dependencies listed on the assembly called Trigger1?|||

Target="AdventureWorks"

doesnt work either....I am about to the point of assuming you cannot do this via VS and your only option for deploying managed DDL triggers is manual...

|||This one worked for me :

[Microsoft.SqlServer.Server.SqlTrigger(Name = "ddltrigger", Target = "database",Event = "FOR CREATE_TABLE")]

public static void ddltrigger()

{

// Replace with your own code

SqlContext.Pipe.Send("Trigger FIRED");

}

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

I believe the problem was that I omitted the FOR in my event="" parm...

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
[Microsoft.SqlServer.Server.SqlTrigger(Name = "ddltrigger", Target = "database", Event = "FOR CREATE_TABLE")]
public static void ddltrigger()
{
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
}
}

that code works when I used a new project. Either way you gave me my example. Thread solved!

thx dude. join www.redmondsociety.com we'd love to have u as one of our first members.

|||

BTW...try using this code with autoDeploy...

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
[Microsoft.SqlServer.Server.SqlTrigger(Name = "ddltrigger", Target = "all server", Event = "FOR DDL_SERVER_LEVEL_EVENTS")]
public static void ddltrigger()
{
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
}
}

error at deployTime: "Server-Level AutoDeployment is not supported"..little tid-bit of knowledge resulting from this.