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?