Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-11-07
6
Medium Priority
?
131 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 2000 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

730 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