Solved

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

Posted on 2016-11-07
6
45 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
  • 3
  • 3
6 Comments
 
LVL 39

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 39

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 39

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

832 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