Solved

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

Posted on 2016-11-07
6
37 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

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, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now