Re: Need good idea

Vlad Olevsky wrote:
> Hi guys
>
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
>
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
>
> Obviously such approach is very ineffective, the alternative is to
> create trigger on update event and then execute stored procedure which
> will re-calculate and update the hash value. The problem with this
> approach is that end user could then change the date in the tables and
> then run this store procedure to adjust hash value.
>
> We are looking for some solution that could speed up the hash value
> updating without allowing authorized user to do it
>
> Thanks in advance,
> Leon

In DB2 for LUW you can define the column as a generated column. I presume you have some sort of UDF already that does the actually hashing. Last I heard SS 2005 will have persistent generated columns as well.
In general (x-product) you can use a combination of a check constraint and (before) triggers.

One must but wonder WHY this column is required. Are you affraid of corruption or sabotage?

Cheers
Serge

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab