Cannot Declare Image Type in Trigger

Question

Cannot Declare Image Type in Trigger

esebasti

SSC Eights!

Points: 854
More actions
February 6, 2003 at 8:19 am

#58824

I have a base and history table with a column of data type image. I use an update trigger to insert into the history table when the base table is updated. In the trigger I have to declare a variable for each column, the problem comes in when I attempt to declare a variable of data type Image. I get the following error.
"The text, ntext, and image data types are invalid for local variables."
has anyone found a work around for this?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Sorry no. However, if you have a unique id or pk value you can link the two tables together you can use set without variables.

UPDATE tbl

SET cols = cols

FROM

querytojoinhistoryandinserted

esebasti SSC Eights! Points: 854 More actions · Answer 2

Do you know if I would be able to Cast or Convert to a different data type to store the image to a variable and then back to an image for the insert to the history table. The reason being is that our enterprise architecture is all standardized to use the same trigger structure, although this might be a good reason to make an exception to the standards.

E...

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Sorry, again no. You cannot CAST TEXT, NTEXT or IMAGE to another datatype.

Ben Collins SSC Enthusiast Points: 134 More actions · Answer 4

Hello,

I'm fairly new to T-SQL so hopefully I'm not making any really trivial mistakes here.

I'm up against this very problem myself and have been going mad trying to get around it working.

I understand that in an INSTEAD OF Trigger you are allowed data of type image and for instance in an insert trigger the image data is present in the inserted table see msdn : <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_6f3n.asp> so if you can't declare a local variable of type image then what use is having the data available? is there another way of using data in the inserted table without first declaring a local variable to recieve it.

if someone could explain the error of my ways I'd be most grateful.

Many thanks

Ben.

noeld SSC Guru Points: 96590 More actions · Answer 5

Ben Why do you need a VARIABLE?

If you are just saving that column on an audit table or some other table for that matter you simply:

insert into DESTINATION_TABLE ( img)

select col_img from inserted -- or deleted depending on the trigger type

if you need more insight just post your code and what are you trying to accomplish and I may help you out!

Cheers

* Noel

Ben Collins SSC Enthusiast Points: 134 More actions · Answer 6

Hi Noel,

Thanks for replying, I'm not sure of the syntax to do what you propose. Basically I'm trying to insert the image data into two tables, I use a stored procedure to insert from my code into the first table and then I'm trying to use a trigger to insert into the second.

Obviously having to use an INSTEAD OF trigger means that I will have to explicitly insert the data into the first table as well - which I'm not sure how to do as it will then cause the instead of trigger to fire again getting into an endless loop (any thoughts on this?)

I guess another alternative would be to have a separate image table (with two columns of type guid and image)insert into that and just store the guid in both tables but it's not ideal.

Many thanks for any assistance my code so far follows:

Regards,

Ben.

CREATE TRIGGER tr_DistributeTransactions ON [dbo].[Transaction]

--FOR INSERT

Instead of Insert

--After Insert

AS

-- set up the variables

DECLARE @DeviceID VARCHAR(50)

DECLARE @Sent DateTime

DECLARE @TransCode VARCHAR(50)

DECLARE @JobNumber VARCHAR(50)

DECLARE @DropPostCode VARCHAR(50)

DECLARE @SignatureName VARCHAR(50)

--DECLARE @Signature image

-- fill the variables

SELECT @DeviceID = (SELECT DeviceID FROM Inserted)

SELECT @Sent = (SELECT Sent FROM Inserted)

SELECT @TransCode = (SELECT TransCode FROM Inserted)

SELECT @JobNumber = (SELECT JobNo FROM Inserted)

SELECT @DropPostCode = (SELECT DropPostCode FROM Inserted)

SELECT @SignatureName = (SELECT SignatureName FROM Inserted)

--SELECT @Signature = (SELECT Signature FROM Inserted)

IF @TransCode like '20' -- signature Still need to find out how to move the image column!!!

BEGIN

Insert into Signature

([TimeStamp], JobNo, DropPostCode, SignatureName)--, Signature)

VALUES

(@Sent,@JobNumber, @DropPostCode, @SignatureName)--, @Signature )

END