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

Read BLOB under SQL 2005 Management Studio -> Open Table.

How to read the content of a BLOB field under SQL 2005 Management Studio?
under the Management Studio, how to tell the blob field contain information
or not.
Does the BLOB content store in the .mdb file or somewhere else?
Is there any way or tools available for me to look at the content in the BLOB
field without writing some code?
"John Bell" wrote:

> Hi
> I assume you are using the Open table option in SSMS, in which case if your
> column has data it will say <binary data> if you have data in a image or
> varbinary column. If you ran select query in a query windows against the
> column you would see the data itself. Blob data is stored in the database,
> with SQL 2008 and the filestream option for varbinary(max) you can store
> your blobs on the filesystem.
> John
> "Kam" <Kam@.discussions.microsoft.com> wrote in message
> news:C2B2D7F0-5C73-4EBC-9D2C-48EDAF73C29D@.microsoft.com...
>
>
|||If you are talking about some kind of general-purpose GUI tools, I'm afraid
not because what is stored in an image column is just a bit stream, and SQL
Server doesn't care about its semantics, i.e. what the content actually
represent. For a tool to properly display the content of an image column, it
would have to understand the semantics of the image values. When one value
can be a Word file, another can be an Excel file, another can be of an
arbitrary 3rd-party file format, and yet another can be a custom binary file,
no general-purpose tool can handle them all.
Linchi
"Kam" wrote:
[vbcol=seagreen]
> Is there any way or tools available for me to look at the content in the BLOB
> field without writing some code?
> "John Bell" wrote:

Read BLOB under SQL 2005 Management Studio -> Open Table.

How to read the content of a BLOB field under SQL 2005 Management Studio?
under the Management Studio, how to tell the blob field contain information
or not.
Does the BLOB content store in the .mdb file or somewhere else?Hi
I assume you are using the Open table option in SSMS, in which case if your
column has data it will say <binary data> if you have data in a image or
varbinary column. If you ran select query in a query windows against the
column you would see the data itself. Blob data is stored in the database,
with SQL 2008 and the filestream option for varbinary(max) you can store
your blobs on the filesystem.
John
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:C2B2D7F0-5C73-4EBC-9D2C-48EDAF73C29D@.microsoft.com...
> How to read the content of a BLOB field under SQL 2005 Management Studio?
> under the Management Studio, how to tell the blob field contain
> information
> or not.
> Does the BLOB content store in the .mdb file or somewhere else?
>
>|||Is there any way or tools available for me to look at the content in the BLOB
field without writing some code?
"John Bell" wrote:
> Hi
> I assume you are using the Open table option in SSMS, in which case if your
> column has data it will say <binary data> if you have data in a image or
> varbinary column. If you ran select query in a query windows against the
> column you would see the data itself. Blob data is stored in the database,
> with SQL 2008 and the filestream option for varbinary(max) you can store
> your blobs on the filesystem.
> John
> "Kam" <Kam@.discussions.microsoft.com> wrote in message
> news:C2B2D7F0-5C73-4EBC-9D2C-48EDAF73C29D@.microsoft.com...
> > How to read the content of a BLOB field under SQL 2005 Management Studio?
> >
> > under the Management Studio, how to tell the blob field contain
> > information
> > or not.
> >
> > Does the BLOB content store in the .mdb file or somewhere else?
> >
> >
> >
>
>|||If you are talking about some kind of general-purpose GUI tools, I'm afraid
not because what is stored in an image column is just a bit stream, and SQL
Server doesn't care about its semantics, i.e. what the content actually
represent. For a tool to properly display the content of an image column, it
would have to understand the semantics of the image values. When one value
can be a Word file, another can be an Excel file, another can be of an
arbitrary 3rd-party file format, and yet another can be a custom binary file,
no general-purpose tool can handle them all.
Linchi
"Kam" wrote:
> Is there any way or tools available for me to look at the content in the BLOB
> field without writing some code?
> "John Bell" wrote:
> > Hi
> >
> > I assume you are using the Open table option in SSMS, in which case if your
> > column has data it will say <binary data> if you have data in a image or
> > varbinary column. If you ran select query in a query windows against the
> > column you would see the data itself. Blob data is stored in the database,
> > with SQL 2008 and the filestream option for varbinary(max) you can store
> > your blobs on the filesystem.
> >
> > John
> >
> > "Kam" <Kam@.discussions.microsoft.com> wrote in message
> > news:C2B2D7F0-5C73-4EBC-9D2C-48EDAF73C29D@.microsoft.com...
> > > How to read the content of a BLOB field under SQL 2005 Management Studio?
> > >
> > > under the Management Studio, how to tell the blob field contain
> > > information
> > > or not.
> > >
> > > Does the BLOB content store in the .mdb file or somewhere else?
> > >
> > >
> > >
> >
> >
> >