CLR create trigger

  • Thread starter Thread starter simon
  • Start date Start date
S

simon

I have:

public class Triggers
{
[SqlTrigger (Name="TrrPerson.ContactType", Target="Person.ContactType", Event="FOR INSERT")]

public static void MyTrigger()
{
SqlTriggerContext oTriggerContext = SqlContext.GetTriggerContext();
SqlPipe sPipe = SqlContext.GetPipe();
SqlCommand sqlCmd = SqlContext.GetCommand();
if (oTriggerContext.TriggerAction == TriggerAction.Insert)
sqlCmd.CommandText = "select * FROM inserted";
sPipe.Execute(sqlCmd);
}
}

If I click: Build - Deploy MyCLRDLL i get message:

Deploy failed.

If I comment the line:
//[SqlTrigger (Name="TrrPerson.ContactType", Target="Person.ContactType", Event="FOR INSERT")]

and deploy again it works.

If I execute the statement:
CREATE TRIGGER MyTrigger ON Person.ContactType
FOR INSERT
AS

EXTERNAL NAME MyCLRDLL.Triggers.MyTrigger

than it works, the trigger is added. Why?

Than if I insert something:

insert INTO Person.ContactType
VALUES(21,'Big boss','20040503')

the insert executes but I don't get a contents of the inserted row in my mesage window of SQL server management studio.
I think I should.

Thank you,

Simon
 
Please post SQL2005 Beta1 questions to the appropriate whidbey NG microsofoft.private.whidbey.sqlclr

Willy.

I have:

public class Triggers
{
[SqlTrigger (Name="TrrPerson.ContactType", Target="Person.ContactType", Event="FOR INSERT")]

public static void MyTrigger()
{
SqlTriggerContext oTriggerContext = SqlContext.GetTriggerContext();
SqlPipe sPipe = SqlContext.GetPipe();
SqlCommand sqlCmd = SqlContext.GetCommand();
if (oTriggerContext.TriggerAction == TriggerAction.Insert)
sqlCmd.CommandText = "select * FROM inserted";
sPipe.Execute(sqlCmd);
}
}

If I click: Build - Deploy MyCLRDLL i get message:

Deploy failed.

If I comment the line:
//[SqlTrigger (Name="TrrPerson.ContactType", Target="Person.ContactType", Event="FOR INSERT")]

and deploy again it works.

If I execute the statement:
CREATE TRIGGER MyTrigger ON Person.ContactType
FOR INSERT
AS

EXTERNAL NAME MyCLRDLL.Triggers.MyTrigger

than it works, the trigger is added. Why?

Than if I insert something:

insert INTO Person.ContactType
VALUES(21,'Big boss','20040503')

the insert executes but I don't get a contents of the inserted row in my mesage window of SQL server management studio.
I think I should.

Thank you,

Simon
 
Simon,

Does it give any more information than just "Deply failed"?

You are using the beta of SQL Server 2005, right? This won't work on
earlier versions of SQL server.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have:

public class Triggers
{
[SqlTrigger (Name="TrrPerson.ContactType",
Target="Person.ContactType", Event="FOR INSERT")]

public static void MyTrigger()
{
SqlTriggerContext oTriggerContext =
SqlContext.GetTriggerContext();
SqlPipe sPipe = SqlContext.GetPipe();
SqlCommand sqlCmd = SqlContext.GetCommand();
if (oTriggerContext.TriggerAction == TriggerAction.Insert)
sqlCmd.CommandText = "select * FROM inserted";
sPipe.Execute(sqlCmd);
}
}

If I click: Build - Deploy MyCLRDLL i get message:

Deploy failed.

If I comment the line:
//[SqlTrigger (Name="TrrPerson.ContactType", Target="Person.ContactType",
Event="FOR INSERT")]

and deploy again it works.

If I execute the statement:
CREATE TRIGGER MyTrigger ON Person.ContactType
FOR INSERT
AS
EXTERNAL NAME MyCLRDLL.Triggers.MyTrigger
than it works, the trigger is added. Why?
Than if I insert something:
insert INTO Person.ContactType
VALUES(21,'Big boss','20040503')
the insert executes but I don't get a contents of the inserted row in my
mesage window of SQL server management studio.
I think I should.
Thank you,
Simon
 
Back
Top