Re: Trigger to ensure unique objects

Christian "Raistlin" Gulich wrote:
>
> Hi,
> I have some multimedia content in my database stored using interMedia.
> Because there is no way, to declare these objects (ORDImage, ORDVideo, ...)
> as unique (or is it possible?), I wanted to write a trigger, wich ensures
> this constraints.
> The trigger fires before insert and update operations for each row.
> In this trigger a have to check, if the same object is already in the table.
> Therefore I must read the complete table and perform my checks. The problem
> is, that this tricker throws an error, that "table is modified at the moment
> an the trigger can't see this". Oracle suggestion to solve this, is not to
> read this table in the trigger - not very good for me. I suppose there is a
> lock at the table. Do you have any suggestions, how I could implement the
> trigger?
> Thanks Christian

What determines 'uniqueness' here though?

eg If you open a word document, change a letter and change it back, then save it, the binary result is not the same as the original document. Various attributes (file name, modified, size, etc) might be candidates for uniqueness but these could be done as separate scalar datatypes and thus the standard uniqueness constraints used.

hth
connor

-- 
-------------------------------
Connor McDonald
http://www.oracledba.co.uk
Co-Author: "Mastering Oracle PL/SQL - Practical Solutions"