Hi,

I am trying to maintain logical data integrity by using triggers.

In my case I have "Item" table and "SaleTransaction" table  for which
item_id is a foreign key. Item record can have 0 or 1 "saletransaction"
records. Now let's assume that there is  Item.salestatus field and one of
the values of the status requires saletransaction record to exist, but
others prohibit that.

Now my task is to enforce this type of integrity when user modifies
item.salestatus field and creates saltetransaction record.

If  item_update_trigger  checks first for existence of SaleTransaction
record  it would not update, since it is not present (yet).
If saleTransaction_insert_trigger checks for item.salestatus field it would
not insert because status is not updated (yet).
So I am ending up with circular reference and each of my triggers never
permits an update.

I guess the problem is that I need somehow to validate result of the whole
transaction, not the atomic update. How can it be done on database level ?

What is the common wisdom here ?

Any ideas are appreciated,

Thanks,
VT

How about not using the trigger on the Item table, and instead making sure in
all procedures that the status column rule is not violated?

Or even better - how about maintaining the status column functionality
through a view?

select <item key>
         ,Status = case when <saletransaction key> is null then 0 else 1 end
         from Item
                left join SaleTransaction
                           on SaleTransaction.ItemId = Item.ItemId


ML

---
http://milambda.blogspot.com/

random select a row based a weight column.
Timeout expire
Optimize this query
How can I insert a new field between existing field?
store results over network