CLR trigger

Question

CLR trigger

anupamabr

SSC Eights!

Points: 930
More actions
November 10, 2008 at 2:07 pm

#195688

I am using Visual Studio 2008 for Clr trigger insert....
I created a CLR trigger for insert command by above code...
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);
SqlContext.Pipe.ExecuteAndSend(command);
//SQLContext.Pipe.ExecuteAndSend(command);
//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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

I don't see any reason to use CLR for this. There is no complex logic occuring in your code, so a standard TSQL FOR INSERT trigger will work best for this process.

anupamabr SSC Eights! Points: 930 More actions · Answer 2

anupamabr

SSC Eights!

Points: 930

November 10, 2008 at 4:25 pm

#896641

I dont have any idea about TSQL ...Please help me

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Start by reading the article identified below in my signature block. Also, try explaining what the purpose of the trigger is to accomplish.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

If you don't know TSQL, and you have other objects in SQL CLR, then I would recommend that you start over on your design. In my opinion, 95% of all tasks in SQL Server that most people need to do only require standard TSQL. You will get more mileage and better performance out of your application if you properly use CLR and properly use TSQL. If the following is how you do data access in CLR inside or outside of SQL Server, then you have a very dangerous application on your hands:

anupamabr (11/10/2008)
command = new SqlCommand( @"INSERT [dbo].[CrossSell] VALUES (" + CrossSellId + @", " + int_id + @"," + ProductId + @"," + CrossSellingId + @");", connection);

This code is dangerous because you are concatenating unchecked values INTO dynamic SQL. This will allow for SQL Injection which can be used to do vast amounts of damage to your database and or take over your SQL Server if you are doing things on the No-No list like running your app under a sysadmin user because it is easier than learning the permission model for SQL Server and creating appropriate permission rights to the database.

This code is also problemattic because it is adhoc and therefore not cacheable in the plan cache since every execution will run a different set of code. You should instead be using a parameterized command:

command = new SqlCommand(

@"INSERT [dbo].[CrossSell] VALUES (@CrossSellId, @int_id, @ProductId, @CrossSellingId);",

connection);

Then use SqlParameters to bind values to these parameters.

command.Parameters.Add(new SqlParameter("@CrossSellId", CrossSellId));

command.Parameters.Add(new SqlParameter("@int_id", int_id));

command.Parameters.Add(new SqlParameter("@ProductId", ProductId));

command.Parameters.Add(new SqlParameter("@CrossSellingId", CrossSellingId));

This will prevent injection attacks as well as ensure that your code is cacheable in the plan cache for reuse.

As for how to write this in TSQL, you will need to provide additional information regarding Table DDL as requested in the link in the previous posters signature. Without this, the best we can do is offer you the link to the BOL reference materials:

http://msdn.microsoft.com/en-us/library/ms189799.aspx

I'd recommend that you pick up one of the following books:

Pro SQL Server 2005 Database Design and Optimization (Pro)

http://www.amazon.com/Server-2005-Database-Design-Optimization/dp/1590595297/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1226362594&sr=8-1

Pro T-SQL 2005 Programmer's Guide (Expert's Voice)

http://www.amazon.com/T-SQL-Programmers-Guide-Experts-Voice/dp/159059794X/ref=pd_bbs_sr_4?ie=UTF8&s=books&qid=1226362677&sr=8-4

Inside Microsoft SQL Server 2005: T-SQL Querying (Solid Quality Learning)

http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735623139/ref=pd_bbs_sr_5?ie=UTF8&s=books&qid=1226362677&sr=8-5

All three of them are excellent. The second book is by Michael Coles who also frequents this site. Louis Davidson and Itzig Ben Gan are both excellent resources on TSQL as well, but since I see Michael on here at times, I will shamelessly plug his book. I have all three of the above books.

If you provide the information needed, we can help you create a solid TSQL solution to your problem.

anupamabr SSC Eights! Points: 930 More actions · Answer 5

anupamabr

SSC Eights!

Points: 930

November 11, 2008 at 11:35 am

#897095

Thanks alot!!!

anupamabr SSC Eights! Points: 930 More actions · Answer 6

helloo

Hi

I did the changes as u mentioned for clr trigger ...

my code is as below

- Hide quoted text -

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);

command.Parameters.Add(

new SqlParameter("@CrossSellId", CrossSellId));

command.Parameters.Add(

new SqlParameter("@int_id", int_id));

command.Parameters.Add(

new SqlParameter("@ProductId", ProductId));

command.Parameters.Add(

new SqlParameter("@CrossSellingId", CrossSellingId));

pipe.Send(command.CommandText);

SqlContext.Pipe.ExecuteAndSend(command);

//SQLContext.Pipe.ExecuteAndSend(command);

//command.ExecuteNonQuery();

pipe.Send(xmlFromDataTable);

//pipe.Send("CrossSell inserted!");

//connection.Open();

//da.Fill(dt);

// connection.Close();

}

break;

}

now i need to manually deploy it befor e debugging for that i created an assembly in my database thundercommerce

Use

ThunderCommerce

create

assembly firstTrigger

from

'C:\Users\Anil\Documents\Visual Studio 2008\Projects\Voodoo.CLR\bin\Debug\Voodoo.CLR.dll'

.....

after that i created a trigger....

create

trigger firstTrigger

on

dbo.CrossSell

for

insert

as

external name firstTrigger.Triggers.firstTrigger

.....

then i debugged it then same result..no rows are effected....

i don knw why the triggr is not firing...what is wrong with it....

My database is thundercommerce and my table dbo.crossSell....which contains 4 datafields...crosssellingid,intid,productid all are unique identifiers and the primary key of table is crosssellid....

After clr trigger insert i also need to update the Productbase table which contains crosssellId as xml datatype using the inserted result there.....

I dont knw how can i use tsql trigger for xml data ie y i used clr trigger ..Please let me knw if u have any idea now....

if u need any furthe details abt the same please let me know...

It is urgent!!!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

You seem to be doing a lot of work to write a CLR trigger that uses embedded sql to accomplish what could be done in straight T-SQL code.

What is the business process that you sre working with. If you provide us with the table DDL, sample data, and expected results (read the article in my signature block, it tells you how to create all of this), and post that back. I am confident we can show you a straight T-SQL way of accomplishing what you are trying to do in a CLR trigger.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

I am in complete agreement with Lynn on this. You are beating a screw with a hammer because all you have is a hammer. You might be able to build a house like this, but it will not be as solid as if you just went out and bought a screw gun.

As I said in my last post, you need to abandon the use of CLR to solve this problem and focus on using the appropriate TSQL method which we can help you construct if you provide the appropriate information as listed in the article on Lynn's signature.

To make it clear, I am not going to look at providing a CLR based solution to your problem because it violates best practices, and I am not an advocate of providing answers just to solve the problem on the path chosen. In this case CLR is very wrong for the problem at hand, and TSQL is much simpler, cleaner, and more performant. The example I provided in my post above was more of a in general, you have a problem that I see and can offer to you how to fix it. It wasn't intended to keep you trying to solve this in CLR.

anupamabr SSC Eights! Points: 930 More actions · Answer 9

Hi I think that my trigger is working...but there is some mistakea in my code...

I am not able to use TSQL trigger since i have xml datatype is there...

So pls help me...

My problem is when i am trying to insert values into crosssell table...I am getting the following error

INSERT [dbo].[CrossSell] VALUES (4a321938-c189-453c-9e8d-850a2885453d, 5,4a321938-c189-453c-9e8d-850a2885453c,4a321938-c189-453c-9e8d-850a2885453a);

Msg 6549, Level 16, State 1, Procedure firstTrigger, Line 1

A .NET Framework error occurred during execution of user defined routine or aggregate 'firstTrigger':

System.Data.SqlClient.SqlException: Incorrect syntax near 'a321938'.

System.Data.SqlClient.SqlException:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages)

at Microsoft.SqlServer.Server.SmiEventSink_Default.DispatchMessages(Boolean ignoreNonFatalMessages)

at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at Triggers.firstTrigger()

. User transaction, if any, will be rolled back.

The statement has been terminated.