A
anu b
Hi
I need to use Clr trigger for insert command
My code is as below
I am using SQL server 2005 and VS 2008.... but after running this code
i didnt get the result as i expexted it shows the result as no row is
effected ...Please help me guys
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.IO;
//using System.Transactions;
public partial class Triggers
{
// Enter existing table or view for the target and uncomment the
attribute line
[Microsoft.SqlServer.Server.SqlTrigger(Name = @"firstTrigger",
Target = "dbo.CrossSell", Event = "FOR Insert")]
public static void firstTrigger()
{
Guid CrossSellId;
int int_id;
Guid ProductId;
Guid CrossSellingId;
SqlCommand command;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
//string st = triggContext.EventData.Value;
// XmlDocument xmlDoc = new XmlDocument();
//xmlDoc.LoadXml(st);
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
// DataTable dt = new DataTable();
//SqlDataAdapter da = new SqlDataAdapter();
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM
INSERTED;",
connection);
SqlDataAdapter da = new
SqlDataAdapter("select*from inserted ",connection);
DataTable dt = new DataTable();
da.Fill(dt);
StringWriter writer = new StringWriter();
dt.WriteXml(writer,XmlWriteMode.WriteSchema,false);
string xmlFromDataTable = writer.ToString();
reader = command.ExecuteReader();
reader.Read();
CrossSellId = (Guid)reader[0];
int_id = (int)reader[1];
ProductId = (Guid)reader[2];
CrossSellingId = (Guid)reader[3];
reader.Close();
////// command = new SqlCommand(
//////"INSERT into CrossSell (CrossSellId,
int_id,ProductId,CrossSellingId) " +
//////"VALUES (@CrossSellId,
@int_id,@ProductId,@CrossSellingId)", connection);
command = new SqlCommand(
@"INSERT [dbo].[CrossSell] VALUES ("
+ CrossSellId + @", " + int_id + @"," + ProductId +
@"," + CrossSellingId + @");",
connection);
pipe.Send(command.CommandText);
command.ExecuteNonQuery();
pipe.Send(xmlFromDataTable);
//pipe.Send("CrossSell inserted!");
//connection.Open();
//da.Fill(dt);
//
connection.Close();
}
break;
}
After this i need to update my ProductBase table
DECLARE @ProductBase TABLE (ID int IDENTITY(1,1), CrossSell xml)
INSERT INTO @ProductBase
DEFAULT VALUES
SELECT * FROM @ProductBase
Update @ProductBase
set CrossSell = ' '
where CrossSell IS NULL
SELECT * FROM @ProductBase
then it shows the updated result...
This what i did ....
but after debugging the clr trigger it shows no rows are effected i
dont kknw what is the problem with it..i am new to this...thanks in
advance for your help
I need to use Clr trigger for insert command
My code is as below
I am using SQL server 2005 and VS 2008.... but after running this code
i didnt get the result as i expexted it shows the result as no row is
effected ...Please help me guys
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.IO;
//using System.Transactions;
public partial class Triggers
{
// Enter existing table or view for the target and uncomment the
attribute line
[Microsoft.SqlServer.Server.SqlTrigger(Name = @"firstTrigger",
Target = "dbo.CrossSell", Event = "FOR Insert")]
public static void firstTrigger()
{
Guid CrossSellId;
int int_id;
Guid ProductId;
Guid CrossSellingId;
SqlCommand command;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
//string st = triggContext.EventData.Value;
// XmlDocument xmlDoc = new XmlDocument();
//xmlDoc.LoadXml(st);
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
// DataTable dt = new DataTable();
//SqlDataAdapter da = new SqlDataAdapter();
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM
INSERTED;",
connection);
SqlDataAdapter da = new
SqlDataAdapter("select*from inserted ",connection);
DataTable dt = new DataTable();
da.Fill(dt);
StringWriter writer = new StringWriter();
dt.WriteXml(writer,XmlWriteMode.WriteSchema,false);
string xmlFromDataTable = writer.ToString();
reader = command.ExecuteReader();
reader.Read();
CrossSellId = (Guid)reader[0];
int_id = (int)reader[1];
ProductId = (Guid)reader[2];
CrossSellingId = (Guid)reader[3];
reader.Close();
////// command = new SqlCommand(
//////"INSERT into CrossSell (CrossSellId,
int_id,ProductId,CrossSellingId) " +
//////"VALUES (@CrossSellId,
@int_id,@ProductId,@CrossSellingId)", connection);
command = new SqlCommand(
@"INSERT [dbo].[CrossSell] VALUES ("
+ CrossSellId + @", " + int_id + @"," + ProductId +
@"," + CrossSellingId + @");",
connection);
pipe.Send(command.CommandText);
command.ExecuteNonQuery();
pipe.Send(xmlFromDataTable);
//pipe.Send("CrossSell inserted!");
//connection.Open();
//da.Fill(dt);
//
connection.Close();
}
break;
}
After this i need to update my ProductBase table
DECLARE @ProductBase TABLE (ID int IDENTITY(1,1), CrossSell xml)
INSERT INTO @ProductBase
DEFAULT VALUES
SELECT * FROM @ProductBase
Update @ProductBase
set CrossSell = ' '
where CrossSell IS NULL
SELECT * FROM @ProductBase
then it shows the updated result...
This what i did ....
but after debugging the clr trigger it shows no rows are effected i
dont kknw what is the problem with it..i am new to this...thanks in
advance for your help