readinf blob data in sql trigger

hi,
i am using sql sever 2005 , i want to read binary data and data of
computed coloumn using trigger.
my table is below:
table column
--
1) document data -- type (image)
2) userId -- type(varchar computed column)
i have to write delete trigger which can get image data and userid in
to some variable or cursor.
please can anyone help me in this regard.
sathya narayanan.v
narayanan@.gsdindia.comThe text, ntext, and image values in the inserted and deleted tables cannot
be accessed within a trigger. This is by design.
Anith|||Hi
If you are using SQL Server 2005, replace the image datatype with
varbinary(max) as the image datatype will be removed in future releases of
SQL Server.
Post SQL Server 2005 CTP/Beta questions to:
http://communities.microsoft.com/ne...p=sqlserver2005
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"sathya" wrote:

> hi,
> i am using sql sever 2005 , i want to read binary data and data of
> computed coloumn using trigger.
> my table is below:
> table column
> --
> 1) document data -- type (image)
> 2) userId -- type(varchar computed column)
>
> i have to write delete trigger which can get image data and userid in
> to some variable or cursor.
> please can anyone help me in this regard.
>
> sathya narayanan.v
> narayanan@.gsdindia.com
>|||Do you want to manipulate this data in the trigger or would you like the
trigger to return it? Since the latter is impossible, please elaborate on
what you actually want to do.
The sole purpose of the image datataype is to enable storage of large binary
data in SQL. Manipulating it is IMHO a matter for the application layer,
rather than the data/storage layer. But please, prove me wrong.
ML|||Well, not quite. You can access them in an INSTEAD OF trigger with the
compatibility level set to 80 (or higher - someday).
ML|||hi,
thank you for reply..
actually i want to create trigger on delete event which gets image and
computed coloumn data from one table to another table. Is it possible
to get image data (blob ) and computed data to read from sql in delete
trigger event.
i have posted my databse coloumns below:
Id Uniqueidentifier
SiteId Uniqueidentifier
OldDirName varchar(256)
OldLeafName varchar(128)
WebId Uniqueidentifier
DeletedListId Uniqueidentifier
DeletedDocLibRowId int
Type tinyint
Size int
MetaInfoSize int
Version int
UIVersion int
Dirty bit
CacheParseId Uniqueidentifier
DocFlags tinyint
ThicketFlag bit
CharSet int
TimeCreated datetime
TimeLastModified datetime
NextToLastTimeModified datetime
MetaInfoTimeLastModified datetime
TimeLastWritten datetime
SetupPath nvarchar(255)
CheckoutUserId int
CheckoutDate datetime
CheckoutExpires datetime
CheckoutSize int
VersionCreatedSinceSTCheckout bit
LTCheckoutUserId int
VirusVendorID int
VirusStatus int
VirusInfo nvarchar(255)
MetaInfo binary(8000)
Content binary(8000)
CheckoutContent binary(8000)
Extension varchar(128)
I have to read all coloumn values when in trigger for deleted row and
keep data in another coloumn.
I faced problem while reading image and computed column data.
please help me.
sathya narayanan v
narayanan@.gsdindia.com|||hi,
please tell me how you can do this...
sathya narayanan|||>> i have to write delete trigger which can get image data and userid in to
You will have to elaborate on what you are trying to do. As I said, you
cannot use image data within a trigger. You cannot declare a variable of
image type either.
If you are stuck with this table, you might have to use a different
approach, perhaps using a stored procedure or temp table with column of
image type etc.
Anith|||You should be using something like this in your trigger:
insert destination_table
(
..
)
select ...
from inserted
for the insert/update trigger
and
insert destination_table
(
..
)
select ...
from deleted
for the delete trigger.
You cannot store image values in triggers, since text, ntext and image
datatypes cannot be used for variables.
And if you had used them, such a trigger wouldn't have supported multiple
inserts/updates/deletes.
MLsql