SQL Server table trigger not inserting value

Question

I am creating an after insert table trigger that inserts into another table. All columns work as expected, but when it comes to one particular column (T_W_A.WorkAreaBuffer) that has geographic data in it, it comes back null and I don't understand why. When I take the select statement out of the trigger, all columns work as expected. Here is a copy of the information stored in T_W_A.WorkAreaBuffer:

POLYGON((-80.0705872065121 33.0028430262796,-80.0706042845348 33.0027090648411,-80.0706520501637 33.0025804848199,-80.0707286676788 33.0024622274388,-80.0708311926384 33.0023588372123,-80.0709556850437 33.0022742873109,-80.0710973607543 33.0022118268834,-80.0712507753344 33.0021738562025,-80.0714100332675 33.0021618344308,-80.0715690144994 33.0021762235502,-80.071721609607 33.0022164706098,-80.0718619545597 33.002281028973,-80.071984656053 33.0023674177484,-80.0720849987596 33.0024723171218,-80.0721591265326 33.0025916959256,-80.072204190601 33.0027209665457,-80.072218459058 33.0028551612142,-80.0722013834361 33.0029891229132,-80.0721536198053 33.0031177035549,-80.0720770035815 33.0032359618221,-80.0719744790095 33.0033393530653,-80.0718499860293 33.0034239039594,-80.0717083088688 33.0034863652042,-80.0715548921845 33.0035243364028,-80.0713956318132 33.0035363583138,-80.0712366481804 33.0035219689339,-80.0710840510747 33.0034817212538,-80.0709437048309 33.0034171620046,-80.0708210029499 33.0033307722124,-80.0707206608182 33.0032258718464,-80.0706465344951 33.0031064922252,-80.0706014725309 33.0029772210874,-80.0705872065121 33.0028430262796))

Here is the table structure I am inserting into:

CREATE TABLE [dbo].[training_grades]
(
    [Id] [BIGINT],
    [Number] [VARCHAR](24) NULL,
    [FirstTimeScore] INT,
    [prodarea] NVARCHAR(MAX) NULL
)

Here is the Ticket structure:

CREATE TABLE [dbo].[Ticket]
    (
        [TicketId] [BIGINT] primary key,
        [Number] [VARCHAR](24) NULL
    )

Here is the T_W_A structure:

CREATE TABLE [dbo].[T_W_A]
        (
            [TicketId] [BIGINT] primary key foreign key,
            [WorkAreaBuffer] [TEXT] NULL
        )

Here is the trigger:

CREATE TRIGGER TRG_InsertSyncEmp 
ON Ticket
AFTER INSERT 
AS
BEGIN
    INSERT INTO [dbo].[training_grades]
        SELECT 
            i.TicketId, 
            Number,
            CASE WHEN ia.FirstTime = 1 THEN 1 ELSE 0 END AS FirstTimeScore,
            tw.workareabuffer AS prodarea
        FROM 
            INSERTED i 
        LEFT JOIN 
            itemAtribute ia ON i.TicketId = ia.ItemId
        JOIN 
            T_W_A tw ON i.TicketId = tw.TicketId
END

tw.workareabuffer is a text data type and from what I've read, you cannot insert text fields using a trigger.

My next step was to create a view of the T_W_A and cast the text field to a nvarchar(max) type, but the results were the same.

This leads to me believe that there is something wrong with the join itself, but like I said, when I take the select statement out of the trigger, it works as expected.

Any suggestions to isolate the problem?

Possible duplicate of SQL Server 2008 R2: Trigger on `TEXT` column — Rahul Neekhra
– Rahul Neekhra, Commented Nov 15, 2018 at 18:48
I posted this same comment previously - apparently to a thread that you deleted. I can't imagine that a table named ItemAttribute has a column named ItemId that would match TicketId in Ticket. We could verify that if you post ALL the DDL. Posting the Ticker table twice (with different names and columns) means you just aren't paying sufficient attention. — SMor
– SMor, Commented Nov 15, 2018 at 19:57
Alternatively, ignore the trigger for now and write a query that does what you want with a specific, existing row in Ticket. Get that working and you can easily convert that into a trigger. — SMor
– SMor, Commented Nov 15, 2018 at 19:58
@SMor I updated the content in the previous version and no one responded when I updated so I revised, clarified, and re-posted. I assure you that itemid = ticketid. I know it's messed up but that's how it is. As I stated in the question, when I take the select statement out of the trigger, it works as expected. — Matt
– Matt, Commented Nov 15, 2018 at 20:51
My second comment stills stands - and should be trivial to write and demonstrate. Joins are joins and these seem to be as basic as they come. If you can't write that query, at least post complete DDL. That includes the view (presumably) you refer to as T_W_A and the primary and foreign key constraints. Actual DDL - not your pseudo versions. You can't declare a foreign key with just the text "foreign key". — SMor
– SMor, Commented Nov 15, 2018 at 21:35

DanB · Accepted Answer · 2018-11-15 18:39:56Z

3

First, change TEXT to NVARCHAR(MAX). TEXT is deprecated.

Second, when you add (from the trigger), specify columns name. This will help you to avoid issues in the future.

Now, you may got NULL because you are inserting into TICKET table BEFORE inserting into T_W_A table? When the trigger fire, no data is present in T_W_A table (my guess).

answered Nov 15, 2018 at 18:39

DanB

2,1121 gold badge14 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Matt Over a year ago

I think you may be onto something. Unfortunately I cannot change the data type of the workareabuffer. I created the trigger instead to fire on insert of the T_W_A table instead of the Ticket table but when I convert workareabuffer to nvarchar(max) I still get "Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables"

DanB Over a year ago

You have 2 TEXT column. You changed only 1? The issue can be there. What is your trigger on T_W_A, and what is the order of insert on ticket and t_w_A order?

Matt Over a year ago

No I changed the prodarea to nvarchar(max) type (I updated the question to reflect). Trigger on T_W_A is the same as the ticket trigger above, I just swapped the order and joined the ticket to the T_W_A and on insert convert(nvarchar(max),i.WorkAreaBuffer) as prodarea

DanB Over a year ago

I've just created tables (I let TEXT as TEXT) and your trigger. I tested it, and prodarea came with values. Can you give us insert statement into t_w_a and ticket tables, and right order? And which version of SQL Server are you using?

Matt Over a year ago

Hi Dan, yes, but it didn't directly relate to your answer. What I ended up having to do is save the ticketworkarea in a variable as varchar(max).

|

Collectives™ on Stack Overflow

SQL Server table trigger not inserting value

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related