.Net trigger, reading all the data

I have a trigger written in C# which I have added to the insert event on a table, however, when testing it generates a "System.Data.SQLClient.SqlException; Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables"

My code is attempting to read all the column names & the data contained in them as the record is created, so a solution that allows me to read all the data from each column is what I am after.

Code Extract:

public static void splTrigger()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
// string userName, realName;
SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlDataReader reader;
string data = "";

switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
command.CommandText = "SELECT * from " + "inserted";
reader = command.ExecuteReader();
//userName = (string)reader[0];
//realName = (string)reader[1];
// prepare data as name value pairs
for (int i = 0; i < reader.FieldCount; i++)
{
data = data + reader.GetName(i) + ":" + (string)reader[ i ] + " ";
}
break;
...
}}
Did you try to read it from the original table ?

command.CommandText = "SELECT * from Youtable Y INNeR JOIN inserted i on Y.idColumn 0 I.Column";

Jens K. Suessmeyer.

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

As the error suggests, after triggers do not support those datatypes in after triggers (see BOL: Using text, ntext, and image Data in INSTEAD OF Triggers )

Are you able to change the columns with the offending datatypes to the new nvarchar(max), varchar(max), varbinary(max) types?

If not, the join to the original table and selecting the columns from the base table option seems to work nicely.