CREATE TABLE table1
(
  object VARCHAR(25),
  type VARCHAR(25)
)

I have a before update trigger that verifies that at least one object is of 
'Type 1'. If a 'Type 1' row is updated so that no 'Type 1' row exist, it will 
prevent the update. Therefore, 'Type 1' row must exist in the table at all 
times.


Sample data:
'Tables', 'Type 1'
'Views', 'Type 2'

Say I want to swap the types of Tables and Views.....

I use the query:
UPDATE group_table1 SET type = 
CASE type WHEN 'Type 1' THEN 'Type 2' ELSE 'Type 1' END 
WHERE username = 'Tables' OR username = 'View';

This fails because the trigger is fired twice instead of the whole set. I'm 
guessing a merge statement will fail also.

How do I maintain the business rule that there must always be a 'Type 1' row in 
the table at all times AND allow swapping on type values?


Reply via email to