Create Rule or Constraint

Question

Create Rule or Constraint

Shah_hs

Ten Centuries

Points: 1117
More actions
April 25, 2007 at 9:17 am

#98279

How to create rule or impose constraint to restrict the size of a column having datatype as image.??

Viewing 3 posts - 1 through 2 (of 2 total)

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

Hi,

I don't think it's possible to create constraints on Image columns. You also can't reference them in the inserted/deleted tables of triggers.

There is a workaround that involves a trigger. You can either put this trigger on to the table itself or on to a prior staging table if necessary. The only draw back is that the row is inserted into the table and then removed by the Trigger if it's too big.

An example of such is:

IF EXISTS (SELECT * FROM sysObjects WHERE name = 'tblExample' AND xtype = 'U')

DROP TABLE tblExample

GO

CREATE TABLE tblExample (

imageID INT,

myImage IMAGE,

imageSize AS DATALENGTH(myImage)

)

GO

CREATE TRIGGER trg_IU_CheckSize

ON tblExample

FOR INSERT, UPDATE

AS

DECLARE @imageID INT

DECLARE @errMsg VARCHAR(100)

-- Can't use IMAGE columns in Insered/Deleted Tables.

IF EXISTS(SELECT imageID FROM tblExample WHERE imageSize >=15)

BEGIN

DECLARE csr_imageTooBig CURSOR FOR

SELECT imageID FROM tblExample WHERE imageSize >= 15

OPEN csr_imageTooBig

FETCH NEXT FROM csr_ImageTooBig INTO @imageID

WHILE @@FETCH_STATUS = 0

BEGIN

-- Remove IMAGE from Table

DELETE FROM tblExample WHERE imageID = @imageID

SELECT @errMsg = 'ImageID ' + CAST(@imageID AS VARCHAR) + ' is too big and has been removed from the table.'

RAISERROR (@errMsg, 16, 1)

FETCH NEXT FROM csr_ImageTooBig INTO @imageID

END

CLOSE csr_imageTooBig

DEALLOCATE csr_imageTooBig

END

GO

INSERT INTO tblExample (imageID, myImage) VALUES (1, '0x3092734023')

INSERT INTO tblExample (imageID, myImage) VALUES (2, '0x34635623462345')

INSERT INTO tblExample (imageID, myImage) VALUES (2, '0x34623462345')

SELECT * FROM tblExample

GO

DROP TABLE tblExample

GO

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Omri Bahat-433962 SSC Eights! Points: 901 More actions · Answer 2

What size should the restriction be? Keep in mind that Image is simply a binary data type, so if the size you wish to use is not very large (i.e., over 8K in SQL 2000) you can use a varbinary data type instead. In other words- you can change the image data type for the column to be (var)binary.