Solved

SQLServer 2008R2 Cannot use text, ntext, or image columns in the inserted and deleted tables.

Posted on 2016-11-07
6
90 Views
Last Modified: 2016-11-07
I want to fire a trigger when someone changes an IMAGE field in a 3rd Party Vendors Database. - cant change to varbinary(max) - not my database.

ALTER TRIGGER [dbo].[CT_UPDATE_PART_BINARY_AUD] ON [dbo].[PART_BINARY] FOR UPDATE AS 
SET NOCOUNT ON 
DECLARE @U NVARCHAR(20),@TB NVARCHAR(50),@D DATETIME,@K NVARCHAR(255),@A NVARCHAR(15),@1 NVARCHAR(255),@2 NVARCHAR(255), 
@3 NVARCHAR(4000),@4 NVARCHAR(4000)

SELECT @U=SYSTEM_USER,@TB='PART_BINARY',@D=GetDate(),@A='UPDATE' 
DECLARE C1 CURSOR LOCAL FAST_FORWARD FOR  
	SELECT O.PART_ID,N.PART_ID,CAST(CAST(O.BITS AS VARBINARY(8000)) AS NVARCHAR(4000)) AS OSPEC,CAST(CAST(N.BITS AS VARBINARY(8000)) AS NVARCHAR(4000)) AS NSPEC
	FROM INSERTED N, DELETED O WHERE N.ROWID = O.ROWID 
	
OPEN C1 
FETCH C1 INTO @1,@2,@3,@4
WHILE @@fetch_status<>-1 
BEGIN  
	SELECT @K=IsNull(@1,'') 
	IF (@3 IS NULL AND @4 IS NOT NULL) OR (@3 IS NOT NULL AND @4 IS NULL) OR (@3 IS NOT NULL AND @4 IS NOT NULL AND @3!=@4) 
		INSERT INTO dbo.CT_HISTORY_DATA_BINARY VALUES(@U,@TB,'SPECS',@D,@K,@A,@3,@4) 
	FETCH C1 INTO @1,@2,@3,@4
END 

Open in new window

I get this error:
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

I researched and found that i could use an "INSTEAD OF UPDATE" trigger so i changed it to:
ALTER TRIGGER [dbo].[CT_UPDATE_PART_BINARY_AUD] ON [dbo].[PART_BINARY] INSTEAD OF UPDATE AS 

Open in new window


The "audit" trigger did fire and write the old value / new value in the table in the HISTORY_DATA_BINARY as i wanted, but the update to the main table PART_BINARY never happened - guessing that is why it's called an "INSTEAD OF UPDATE" trigger??!!??

Any help would be greatly appreciated.
0
Comment
Question by:BFanguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 41877674
Indeed - you will have to actually write the update statement to be performed by the trigger after the logging and using the same old/new  values you used to audit the change something like:

[....]
UPDATE PART_BINARY
  SET
    column = INSERTED.column,
    …
  FROM INSERTED
  WHERE INSERTED.key = PART_BINARY.key
    AND INSERTED.IsDeleted = 0
  ;
  DELETE FROM PART_BINARY
  FROM INSERTED
  WHERE INSERTED.key = PART_BINARY.key
    AND INSERTED.IsDeleted = 1
[...]

also need to consider bulk updates and just make sure is not recurring trigger so this update won't fire another audit and go in a loop.
0
 

Author Comment

by:BFanguy
ID: 41877680
lcohan, thanks for the reply,

but i am not very good with sqlserver triggers.  can you look at the trigger i have and possibly modify it to do what you are suggesting?
0
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 41877692
It is practically impossible to do that without having the table's structure to work with and test on before implementing. Essentially you will have to expand the statement below to ALL columns except the PKey and make sure you test it before adding it to any live production table.
And consider replacing the "key" below with the Primary Key column(s) name.

UPDATE PART_BINARY
  SET column1 = INSERTED.column2,
         column2 = INSERTED.column2,
          …
FROM INSERTED
  WHERE INSERTED.key = PART_BINARY.key
    AND INSERTED.IsDeleted = 0
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:BFanguy
ID: 41877705
ok, i see what you are saying, i need to update the PART_BINARY table with the updated data.

thank you.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 41877761
Right - is just to "pair" each column in that update statement however as mentioned please be sure to do this in a dev/test environment first to not cause any production issues.
0
 

Author Closing Comment

by:BFanguy
ID: 41877961
thanks
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question