Image data types - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Image data types

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-13 : 23:56:23

Hello everyone

just wondering i have a trigger
which is performing an update

update mytable set myvar = (select myvar from inserted)

where myvar is a image but this doesn't compare wondering the best way to compare image types i know you can compare them on lenght but just wondering how do you compare them on value?

thankyou in advance

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-14 : 08:07:31

From Books Online, "CREATE TRIGGER":

quote:
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext, or image column references in the inserted and deleted tables if the compatibility level is equal to 70. The text, ntext, and image values in the inserted and deleted tables cannot be accessed. To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table. When the compatibility level is 65 or lower, null values are returned for inserted or deleted text, ntext, or image columns that allow null values; zero-length strings are returned if the columns are not nullable.

If the compatibility level is 80 or higher, SQL Server allows the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-14 : 17:53:16

Hey all,

i am really stuck can anyone give me any idea how this can be achived

my trigger is a instead of trigger but needs to perform the normal update as well, the trigger looks something like this but there is extra conditions in there

CREATE TRIGGER mytrig ON mytable
INSTEAD OF UPDATE
AS
SET NOCOUNT ON
UPDATE mytable
SET
ENUMID = (SELECT ENUMID FROM inserted),
DESCRIPTION = (SELECT DESCRIPTION FROM inserted), ****error on line
CHANGEDDATE = GETDATE()
WHERE
DESCRIPTION = (SELECT DESCRIPTION FROM deleted) AND
ENTITYID = (SELECT ENTITYID FROM deleted) AND
CHANGEDBY = (SELECT CHANGEDBY FROM deleted)

where the DESCRIPTION field is a image data type, however i get an error in the SET part, does anyone know how i can do this with example wasn't to sure about the compatibility level (using sql sever 2000)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-14 : 20:45:04

A quick test will give you a bit of info about your problem
create table #a(i image)
update #a
set i = (select i from #a)

But even if that worked you have further problems
select * from #a a
where a.i = (select i from #a)

You can't do a join either
select * from #a where i = i

You might consider a delete and insert?

Your code wouldn't work for multi row updates anyway and should be

update tbl
set fld1 = i.fld1 ,
fld2 = fld2
from tbl t
join inserted i
on i.pk1 = t.pk1
and i.pk2 = t.pk2
join deleted d
on d.pk1 = t.pk1
and d.pk2 = t.pk2

Unless you allow updates of the PK but that's another debate.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-14 : 22:50:46

Thanks saved me some wondering

but now i got another question just a note i didn't design this database just trying to convert it to sql server as quicky as possible ( it is designed like a 3rd year old did it)

but refering to your last post what if the primary key is updatable?

is there a way to update in a trigger all the values with the new values?

this is my trigger i know this wont work but just wondeirng the way to do it, i know it is prob easy but just got to get my head around it, i am use to interbase where ya dont have to worrie so much about these inserted n deleted tables lots is handled for you

CREATE TRIGGER CHANGEENTITY ON ENTITY
INSTEAD OF UPDATE
AS
SET NOCOUNT ON
IF ((SELECT MajorBuildNo FROM inserted) = 0 AND
(SELECT MinorBuildNo FROM inserted) = 0)
BEGIN
UPDATE ENTITY SET
ENTITYID = (SELECT ENTITYID FROM inserted),
MAJORBUILDNO = 0,
MINORBUILDNO = 0,
ENID = (SELECT ENID FROM inserted),
CDATE = GETDATE()
WHERE
ENTITYID = (SELECT ENTITYID FROM deleted) AND
MAJORBUILDNO = (SELECT ENTITYID FROM deleted) AND
MINORBUILDNO = (SELECT ENTITYID FROM deleted) AND ENID = (SELECT ENID FROM deleted) AND
CDATE = (SELECT CDATE FROM deleted)

END
else
begin
/*would do normal insert*/
end

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-14 : 23:46:09

>> but refering to your last post what if the primary key is updatable?
>> is there a way to update in a trigger all the values with the new values?

Not unless you restrict it to single row updates.
You can put an identity or guid on the table to match up the rows.

Have a look at my sample code to see how to do the updates.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-15 : 00:55:19

Its all good have to maintain the tables as they are its a very restrictive project i am on only meant to conver to sql keep as similar as possible

i had just wondered if there was another way to do the update without doing the joins on primary keys in a instead of update trigger but there isn't well i dont think so i fixed up the tables
allready
:)

thanks for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources