CODEWITHSUNDEEP
c#sql-servertriggerssqlclr
user2371684
user2371684

Reputation: 1565

CLR Trigger Unresolved reference error

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?

Upvotes: 0

Views: 1194

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48874

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.

Upvotes: 2

Related Questions