Go Premium for a chance to win a PS4. Enter to Win

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
?
187 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 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

916 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