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.