CLR Trigger Unresolved reference error

Question

I am attempting to create a CLR Trigger that alters a column value to upper case. But when I try to build the file I get the following:

Severity    Code    Description Project File    Line    Suppression State
Error       SQL71501: Trigger: [dbo].[UpperTrigger] has an unresolved reference to object [dbo].[tblAirline].   TravelSight \\mac\home\documents\visual studio 2015\Projects\TravelSight\TravelSight\obj\Debug\TRAVELSIGHT.generated.sql    33

My clr code is as follows:

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 = "UpperTrigger", Target = "[dbo].[tblAirline]", Event = "AFTER INSERT, UPDATE")]
    public static void UpperTrigger ()
    {
        // Open connection for context
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = "Context Connection=true";

        // Create command object
        SqlCommand command = new SqlCommand();
        command.Connection = conn;

        // Create string that defines sql statement
        string sql = 
         "Update tblAirline " +
        "SET AirlineName = UPPER(AirlineName) " +
        "WHERE AirlineID IN(SELECT AirlineID FROM Inserted)";
        command.CommandText = sql;

        // Exec command object
        command.ExecuteNonQuery();

        // Close connection object
        conn.Close();

    }
}

What have I missed out here?

1. Why are you using a CLR trigger opposed to a tsql trigger? 2. Why does it matter if its upper (sql server isnt case sensitive)? — Wjdavis5
– Wjdavis5, Commented Apr 13, 2016 at 15:41
do you have the table with this name tblAirline if yes than try using this tblAirline instead of this [dbo].[tblAirline] — rashfmnb
– rashfmnb, Commented Apr 13, 2016 at 15:42
SQL can be case sensitive - it depends on your collation. Check here: msdn.microsoft.com/en-us/library/ms144250(v=sql.105).aspx — Andez
– Andez, Commented Apr 13, 2016 at 15:53
Based on this and your other recent SQLCLR questions it is clear that you are currently learning both .NET and SQL Server, based on mistakes with terminology, SQL Server concepts, and C# concepts. You are learning these in the wrong order: SQLCLR is using .NET within SQL Server, so you need to have a better grasp of both C# and T-SQL before moving on to the advanced topic of SQLCLR. When you have a better understanding of both, then take a look at a series of articles I am writing about SQLCLR: Stairway to SQLCLR (free registration required). — Solomon Rutzky
– Solomon Rutzky, Commented Apr 13, 2016 at 16:14
@Andez Not likely a collation issue since this error is coming from Visual Studio / SSDT. — Solomon Rutzky
– Solomon Rutzky, Commented Apr 13, 2016 at 16:16

Solomon Rutzky · Accepted Answer · 2016-04-13 16:27:30Z

The error message is coming from Visual Studio / SSDT.

The most likely cause of the error is that the table referenced in the Target property of the SqlTrigger Attribute is defined in the Project. You need to "Add new Item" to the Project, and select "Table". Add the table tblAirline exactly as it exists in the database since any differences will be propagated to the database when you deploy.

Other notes:

Please put the new SqlConnection instantiation inside of a using(){ } construct as it is a disposable object.
Please put the new SqlCommand instantiation inside of a using(){ } construct as it is a disposable object.
It would be best to stop prefixing table names with tbl. No good comes from that bad practice. Nor should you prefix View names with v or vw or anything.
Please don't use SQLCLR to do nothing more than call T-SQL. I sure hope this is a class exercise / homework and never intended to actually go to Production some day as it is slower and harder to maintain than the simple T-SQL query you are executing within this Trigger.

Collectives™ on Stack Overflow

CLR Trigger Unresolved reference error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related